What a heat map does and when to use one

A heat map in Excel is a visual format that colors cells based on their values — high numbers turn one color, low numbers turn another, and everything in between gets a shade in between. You read it the same way you read a weather map: red means hot (high values), blue means cold (low values), and you can spot patterns across a whole table at a glance instead of reading numbers one by one.

Use a heat map when you have a table of numbers and you want to see which cells stand out. Common examples: sales by region and month (which months were strongest?), test scores across classrooms (which classes need help?), website traffic by day and hour (when do visitors arrive?), or inventory levels across locations (where are we running low?). A heat map turns a spreadsheet of 200 numbers into a picture you can understand in three seconds.

Key Takeaways

  • Heat maps use conditional formatting, a built-in Excel feature that colors cells based on their numbers — you do not need to install anything or learn code.
  • The fastest route is to select your data, go to Home > Conditional Formatting > Color Scales, and pick a color scheme (red-yellow-green is common, but Excel offers others).
  • You can customize which colors represent high and low values, and you can set the exact number thresholds instead of letting Excel guess.
  • Heat maps work on any rectangular block of numbers, but they are clearest when your data has a natural order — like months across columns and regions down rows.

The fastest way: using Color Scales

Open your spreadsheet and select the range of cells you want to color. Click the Home tab at the top. In the Styles group, click Conditional Formatting. A menu appears. Click Color Scales.

Excel shows you preset color schemes. The most common is a three-color scale: green for low values, yellow for medium, red for high. Click it. Excel when ready colors every cell in your selection based on its value. The lowest number in your range gets the darkest green, the highest gets the darkest red, and everything else gets a shade in between. You are done.

If you want a different color scheme — say, blue to white to red, or a two-color scale from light to dark — click a different preset. Each one works the same way: Excel looks at all the numbers in your selection, finds the minimum and maximum, and assigns colors proportionally.

Customizing the colors and thresholds

The presets work fast, but sometimes you need control. Maybe you want to use your company colors, or you want to set a specific number as the "middle" instead of letting Excel calculate it.

Select your data again. Go to Home > Conditional Formatting > Color Scales > More Rules. A dialog box opens. You see three sections: Minimum, Midpoint, and Maximum (or just Minimum and Maximum if you chose a two-color scale).

For each section, you can change the color by clicking the color box. You can also change what number triggers that color. By default, Excel uses "Automatic" — it picks the actual lowest and highest values in your data. If you want to set a specific number instead, click the dropdown next to Automatic and choose Number, Percentage, or Percentile. Then type your number. For example, if you are tracking sales and you want to flag anything below $10,000 as red, set Minimum to Number and type 10000. Click OK.

Removing or changing a heat map you already created

To turn off the colors, select the same range again. Go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. The colors disappear but your numbers stay.

To change the colors without starting over, select the range, go to Home > Conditional Formatting > Manage Rules. Find your rule in the list, click it, and click Edit Rule. The same dialog opens — change the colors or thresholds, then click OK.

Heat maps with text or mixed data

Heat maps work best on pure numbers. If your cells contain text, formulas that return text, or a mix of numbers and text, Excel cannot color them — it skips those cells. Make sure your data range contains only numbers (or cells that calculate to numbers).

If you have headers — like month names across the top or region names down the left side — do not include them in your selection. Select only the number cells. The headers stay plain text while the numbers get colored.

Common mistakes and how to fix them

The most common mistake is selecting too much. If you select a range that includes blank cells or text, Excel still applies the formatting, but blank cells stay blank and text cells stay uncolored. The heat map looks broken. Solution: select only the cells with numbers.

Another mistake is forgetting that Excel uses the minimum and maximum in your selection to set the color scale. If you have one outlier — one cell with a number much higher or lower than the rest — it stretches the scale and makes all your other colors look similar. If that happens, use the More Rules dialog to set a specific threshold instead of Automatic.

A third mistake is using a heat map on data that does not have a natural order. If your rows are random product names and your columns are random metrics, the colors do not tell a story — they just look like a random pattern. Heat maps work best when your data has structure: time across one axis, categories down the other.

When to use other conditional formatting instead

Color Scales is the right tool for spotting high and low values across a range. But Excel has other conditional formatting options for different jobs. Data Bars puts a colored bar inside each cell proportional to its value — useful when you want to compare numbers but still read the exact value. Icon Sets puts a small symbol in each cell (arrows, traffic lights, stars) based on the value — useful when you want to flag just the top and bottom performers, not every cell.

For most heat map work, Color Scales is what you want. The others are worth knowing about if you find yourself wishing the colors worked differently.

Frequently Asked Questions

Can I make a heat map from data in two different sheets?

No. Conditional formatting works on a single range in a single sheet. If your data is split across sheets, copy it into one sheet first, then explore the heat map. You can delete the copy afterward if you want — the formatting stays on the original data.

Does the heat map update automatically if I change the numbers?

Yes. If you edit a cell in your heat map range, Excel recalculates the colors when ready. The minimum and maximum values in your selection might change, which shifts all the colors — this is usually what you want, but if you set custom thresholds in More Rules, those stay fixed.

Can I print a heat map?

Yes. The colors print the same way they appear on screen. If you are printing to black and white, the colors convert to grayscale and may become hard to read — test it first by printing to PDF.

What if I want different colors for positive and negative numbers?

Use the More Rules dialog and set Minimum to a negative number (like -100), Midpoint to 0, and Maximum to a positive number (like 100). Assign one color to Minimum, a neutral color like white or gray to Midpoint, and a different color to Maximum. This makes negative numbers one color and positive numbers another.