Excel Heat Map: How to Create and Customize One

How-to basics · Updated November 19, 2026

Excel does not have a worksheet function named HEATMAP; the usual way to make a heat map is to select numeric cells and choose Home > Conditional Formatting > Color Scales. Excel then colors cells according to their values, and the colors update when the underlying numbers change.

What an Excel heat map is

A heat map is a visual way to compare many values at once. In Excel, it commonly means a table whose cells receive different fill colors based on their numbers. A low-to-high scale might run from pale green through yellow to dark red, making patterns such as strong sales months or unusually high response times easier to spot.

The feature behind this display is conditional formatting, not a special chart or worksheet formula. A color-scale rule compares values in a selected range and assigns colors along a gradient. The numbers remain in the cells; the formatting adds a visual layer rather than replacing the data.

Because the colors are conditional, the display can respond to edits and recalculation. If a value changes, Excel reevaluates the rule and may show a different color. This makes a heat map useful for dashboards and recurring reports, provided the formatted range includes the data that will be added later.

Create a basic heat map in Excel

In Excel for Microsoft 365 on Windows, use these steps to format a simple table:

  1. Select the numeric cells you want to compare. Usually, leave out column headings, row labels, and grand totals.
  2. Go to Home > Conditional Formatting > Color Scales.
  3. Choose a two-color or three-color preset. Excel applies the scale to the selected cells immediately.
  4. Review the result against the numbers. If the colors exaggerate small differences or obscure important thresholds, choose a different scale or customize its breakpoints.
  5. To edit or remove the rule, select a cell in the range and use Home > Conditional Formatting. Choose Manage Rules to inspect or edit rules, or Clear Rules to remove formatting.

For a custom scale, select the cells, choose Home > Conditional Formatting > New Rule, and choose the option to format all cells based on their values. Choose a two-color or three-color scale, then set the color and value type for the minimum, midpoint if used, and maximum. Depending on the selected type, a point can be based on the lowest or highest value, a number, a percentage, a percentile, or a formula. Choose OK to apply the rule.

Excel for Mac also provides color scales through Home > Conditional Formatting > Color Scales. The rule editor’s appearance and available controls can vary with the Excel build. Excel for the web also supports conditional-formatting color scales, although editing options may differ from the desktop application.

How color scales interpret values

A color scale maps positions in a range of values to colors. In a two-color scale, the minimum and maximum receive the endpoint colors, with intermediate values shown as blends. A three-color scale adds a midpoint color, which is useful when a meaningful middle value separates low and high results.

With a default scale, Excel typically uses the selected range’s own minimum and maximum as endpoints. That means the same number can look different in two separate heat maps. For example, a score of 75 could appear near the high end in a range from 50 to 80, but closer to the middle in a range from 0 to 100. A heat map communicates relative position within its rule’s range; it does not automatically give a number a fixed interpretation.

Choose a fixed numeric midpoint when a value has a real business meaning. If 100 represents a target, for example, a three-color scale can use 100 as its midpoint so viewers can distinguish values below and above target. Percentiles can help when a few extreme values compress the colors for most of the data, but they describe rank within the selected range rather than a fixed threshold.

Choose a scale that fits the question

The color scheme should make the pattern clear without suggesting more certainty than the data supports. For values where higher is better, use a progression that reads naturally from low to high. For measures where lower is better, reverse the color meaning so that the favorable end is obvious. If values can be positive or negative, a three-color scale centered on zero can distinguish negative results, values near zero, and positive results.

  • Use a two-color scale when the main goal is to rank values from low to high.
  • Use a three-color scale when a meaningful midpoint matters, such as zero, a target, or a neutral rating.
  • Use fixed numbers when comparisons must be consistent across reports or time periods.
  • Use relative endpoints when the goal is to reveal patterns within one dataset, even as its range changes.

For readability, keep the underlying numbers visible and choose colors with enough contrast. A gradient based only on red and green can be difficult for some readers to distinguish. Consider a color-blind-friendly palette, and do not rely on color alone to communicate a critical status; labels, values, or a separate indicator can reinforce the message.

Example: a monthly performance matrix

Suppose a worksheet has departments down rows and months across columns. Each interior cell contains a numeric performance score, with departments in the first column and months in the header row. Select only the score cells before applying the scale. If the headers or a total row are included, Excel will treat those cells as part of the formatting range where possible, and the resulting minimum and maximum may no longer describe just the individual monthly scores.

A three-color scale can make low-scoring department-month combinations stand out, while stronger results show in a different color. If 80 is the target, setting the midpoint to 80 is more informative than letting Excel choose the middle based on the data distribution. Then a score of 78 remains just below target even if most other results are much lower.

For comparisons between months, apply one scale to the entire score matrix. For comparisons within each department, a separate scale for each row can instead show which months are relatively strong for that department. These views answer different questions: a shared scale helps compare absolute scores across the whole table, while row-specific scales emphasize within-row changes. Label the choice clearly so readers do not mistake relative colors for directly comparable values.

Heat maps with custom rules

Color scales work best when values lie on a meaningful numerical continuum. If you need fixed categories rather than a gradient, create separate conditional-formatting rules. For instance, you might use one rule for values below a target and another for values at or above it. Choose Home > Conditional Formatting > Highlight Cells Rules for common comparisons, or use New Rule when you need more control.

A formula-based conditional-formatting rule can test each cell and apply a fill when a condition is true. In the New Rule dialog, choose the option to use a formula to determine which cells to format. This is not a heat-map function: it is a way to apply formatting based on a logical test. When the rule applies to a range, the formula’s cell references need to be set so Excel evaluates the appropriate cell in each position. Check the rule on several cells after creating it, especially if the range includes mixed references or an unusual layout.

Use a color scale for gradual differences, and use discrete rules when categories have defined meanings such as “below target,” “on target,” and “above target.” Combining many overlapping rules can make the result hard to interpret, so keep the legend or explanation simple and review rule order in Manage Rules when more than one rule applies to the same cells.

Keep the heat map reliable as data changes

Conditional formatting applies to a defined range. If new rows are entered below that range, they may not receive the heat map automatically. You can extend the rule’s range in Manage Rules, or format a larger planned area if that suits the worksheet. When the data is an Excel table, adding rows often carries formatting into the new rows, but check that the conditional-formatting rule’s range and behavior match what you need.

Keep summary rows and subtotals separate from detail values when they have a different meaning or scale. Including a very large total can stretch the color range and make ordinary values appear nearly identical. Likewise, a single outlier can dominate a relative scale. A fixed maximum or percentile may help, but state the choice clearly because it changes how colors should be interpreted.

Blank cells are not the same as zero. A zero is a numeric value and can receive a color; a blank generally does not behave like a numeric endpoint. Text entries are not treated as ordinary numeric values by a color scale. If a cell that looks numeric is ignored, check whether it is stored as text, perhaps because of an apostrophe, imported data, or inconsistent formatting. Converting the data to actual numbers may resolve the issue.

Common problems and alternatives

If nearly every cell has the same color, check whether the values vary enough to show a visible gradient, whether an extreme outlier is stretching the range, and whether the rule applies to the intended cells. Also check for overlapping conditional-formatting rules that may affect the display. To start over, select the range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells, then apply a fresh rule.

If the cells show colors but the display is difficult to read, adjust the scale’s breakpoints or use a simpler palette. The fill does not hide the cell value, but strong fills can reduce text contrast. You can change the font or choose lighter fills, while keeping enough contrast for the numbers to remain legible.

A cell-based heat map is often the clearest option for a compact comparison table. For a geographic pattern, use a map visualization suited to location data; for a trend over time, a line chart may communicate direction more clearly. A PivotTable can also use conditional formatting, but review how its rule applies when the PivotTable is refreshed or rearranged. The best choice depends on whether readers need to compare individual values, see a trend, or understand a spatial distribution.

Frequently Asked Questions

Is there a HEATMAP function in Excel?
No. Excel’s standard heat-map effect comes from conditional formatting, especially Color Scales. You can use formulas in custom conditional-formatting rules, but there is no built-in worksheet function called HEATMAP that turns a range into a heat map.

How do I make a heat map in Excel?
Select the numeric range, then choose Home > Conditional Formatting > Color Scales and select a preset. Use New Rule if you need fixed thresholds, a chosen midpoint, or specific colors.

Can I keep the numbers visible in a heat map?
Yes. A color scale changes the cell formatting, not the cell contents. The values remain visible unless you separately change the font, number format, or other cell settings.

Why does a zero have a color while a blank does not?
Zero is a numeric value that Excel can place on the scale; a blank is not the same value. If a number is unexpectedly ignored, check whether it is stored as text rather than as a numeric value.

Can each row have its own heat-map scale?
Yes. Apply separate color-scale rules to individual rows when the goal is to compare each row with itself. Use one shared rule across all rows when colors should represent comparable absolute values throughout the whole table.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.