Excel Data Heat Map: Create, Read, and Troubleshoot One

How-to basics · Updated November 19, 2026

An Excel data heat map uses cell colors to show how values compare across a selected range. In Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a palette; Excel applies colors automatically, with no chart or formula required.

What an Excel heat map shows

A heat map is a visual summary: color makes relative highs, lows, and patterns easier to spot in a table. For example, a monthly sales grid with products in rows and months in columns can make a strong quarter or a slow product stand out at a glance. The cells still contain their original values; conditional formatting changes their appearance rather than replacing them.

In Excel, the simplest heat map is a conditional-formatting color scale. A two-color scale blends between a low-value color and a high-value color. A three-color scale adds a midpoint color, which can make a gradual range easier to interpret. The familiar red-yellow-green palette is only one option; other palettes may be clearer or more suitable for the data.

Color scales show values in relation to other values in the range to which the rule applies. A cell colored as a relative high does not necessarily meet a business target, and the same value can receive a different color when the range changes. Heat maps are useful for exploration and comparison, but they do not explain why a value is high or low.

Create a heat map with a color scale

For a straightforward heat map in Excel for Microsoft 365 on Windows:

  1. Arrange the data in a rectangular range. Include numerical values you want to compare, and keep headings outside the range when possible.
  2. Select the cells to format. For example, select the monthly sales values but not the product names or month labels.
  3. On the ribbon, choose Home > Conditional Formatting > Color Scales.
  4. Choose a two-color or three-color scale. Excel applies the formatting to the selected cells.

To adjust or inspect the rule, choose Home > Conditional Formatting > Manage Rules. Set Show formatting rules for to the relevant selection or worksheet if needed, select the color-scale rule, and choose Edit Rule. You can change the colors, the minimum, midpoint, or maximum types and values, and the range the rule applies to. The precise dialog options depend on the rule type selected.

Excel for Mac also provides color scales under Home > Conditional Formatting > Color Scales, though ribbon arrangement and dialog presentation can vary by version. In Excel for the web, select the cells and use Home > Conditional Formatting to find color-scale options; some advanced rule-editing controls may differ from the desktop app. If the menu layout differs, look for the Conditional Formatting command on the Home tab.

Choose the right range and scale

The range is part of the meaning of a heat map. If Excel applies one scale to a whole table, each value is judged against all values in that table. If you apply separate rules to individual rows, each row gets its own comparison. The first approach helps compare values across the whole dataset; the second emphasizes variation within each row.

Imagine a grid of weekly orders for several stores. One store may have far more orders than the others, so a whole-table scale could make smaller differences among the remaining stores look nearly identical. Applying a scale separately to each store’s row can reveal its busiest weeks, but the strongest color in one row no longer means the same numerical value as the strongest color in another. Choose the scope based on the question readers need to answer.

  • Use a whole-range scale when comparing magnitudes across all selected values is important.
  • Use row-by-row scales when the pattern within each category matters more than comparing categories with different sizes.
  • Use column-by-column scales when comparing periods or measures within each column is the main goal.
  • Use a fixed midpoint when there is a meaningful boundary, such as zero for positive and negative differences.

For a rule with fixed thresholds, edit the scale and set the relevant threshold type to Number, then enter the value. A scale centered on zero can distinguish negative from positive results more clearly than one whose midpoint simply follows the distribution. Be careful with a midpoint set to Percent: it describes a position in the range, not a business target such as a 50% sales margin. A percentile threshold is also a position in the distribution, not a literal value.

Understand how Excel assigns colors

A color scale maps values between its configured low, midpoint, and high points to colors. With a minimum and maximum set to the lowest and highest values, those endpoints adapt when the data changes. In a three-color scale, the midpoint can be configured by a value, percentage, percentile, or another offered type. Values between the points receive blended colors; values beyond fixed points are represented at the corresponding end of the scale.

This makes color scales convenient for datasets that change, but it also means the colors are not fixed labels. A dark green cell in one month’s report might represent a different number from a dark green cell in another month if the underlying minimum and maximum differ. If readers need to compare separate reports, use consistent numeric thresholds and a consistent color scheme where appropriate, or show the values and scale context clearly.

Conditional formatting is dynamic: when an underlying value changes, Excel recalculates the formatting. Copying or extending data can also affect the ranges covered by a rule. Use Manage Rules to confirm the Applies to range, especially after inserting rows, copying formatted cells, or building a report from a changing dataset.

Prepare data and handle special values

Color scales work best when the selected cells contain comparable numeric values. Text labels, blank cells, and numbers stored as text can make a range harder to interpret. A value that looks numeric may be text if it was imported with an unexpected separator or apostrophe. Check the cell format and whether Excel recognizes the entry as a number before relying on the colors.

Think carefully about totals, subtotals, and outliers. Including a grand total in the same scale as individual monthly figures can compress the colors for the ordinary values. A single unusually large result can have a similar effect. Exclude totals from the range when they are not comparable, or use thresholds that better reflect the intended comparison. Do not remove a genuine outlier merely to make the colors look more dramatic; investigate it and explain the scale if readers could misunderstand it.

Dates are stored as numbers in Excel, so they can receive color-scale formatting too. That is useful for comparing dates, but it may not communicate the intended meaning as clearly as a rule based on age, deadline status, or days overdue. For a status such as overdue versus on time, a separate rule with explicit conditions may be more understandable than a continuous color blend.

Make the heat map readable and accessible

Color should reinforce the numbers, not conceal them. Keep the underlying values visible where possible, use number formats that make units clear, and avoid choosing a palette where neighboring colors are difficult to distinguish. A color scale alone can be difficult to interpret for readers with color-vision deficiencies or when the sheet is printed in grayscale.

  • Use a clear heading that says what the values measure and what period they cover.
  • Prefer a palette with perceptible differences across its range; do not rely on red and green alone to communicate an important distinction.
  • Keep labels, units, and meaningful thresholds visible, and explain what the colors represent.
  • Check that text remains legible against the cell fills, especially if you apply custom colors.
  • Use a legend or a brief note when the scale has fixed thresholds or when the colors could be misread.

Color scales are not the same as data bars or icon sets. Data bars add an in-cell bar whose length represents magnitude; icon sets add symbols according to thresholds. Those options can be found under Home > Conditional Formatting, but they answer somewhat different visual questions. A color scale is often better for a dense matrix, while bars can make individual magnitudes easier to compare against a shared baseline.

Common heat map problems

Everything looks nearly the same. The range may contain an extreme value that stretches the scale, or the chosen palette may have subtle differences. Inspect the values and consider fixed thresholds if there is a defensible comparison boundary. Avoid changing the scale just to make small differences appear larger without telling readers.

The colors change after adding data. The new cells may have entered the Applies to range, or the rule may now use different endpoints. Check Home > Conditional Formatting > Manage Rules and confirm both the range and threshold settings.

Headings or totals have colors too. The selected range may include labels or non-comparable summary cells. Edit the rule’s Applies to range or select a more suitable range when creating it.

The heat map does not update as expected. Confirm that edited entries are numeric and that the cells are covered by the conditional-formatting rule. Also check for another conditional-formatting rule that affects the same cells; rule order and overlapping formatting can change what is displayed.

Copied cells show unexpected formatting. Copying and pasting can bring conditional-formatting rules along with cell formatting. Review Manage Rules for duplicate or overlapping rules and correct the Applies to ranges. Clearing the formatting from a selection can remove more than the heat map, so check what else is applied before using a clearing command.

When a heat map is not the best choice

A heat map is strongest when readers need to scan a table for relative patterns. It is less suitable when exact comparisons between two values are the main task, when the dataset has only a few values, or when the colors could imply a ranking that is not meaningful. In those cases, a sorted table, a standard chart, or clearly labeled threshold categories may communicate more directly.

For a timeline, line chart can show changes over time more clearly than a colored grid. For a small number of categories, a bar chart makes magnitude comparisons explicit. If you want to classify values into named groups such as low, medium, and high, use conditional-formatting rules with clear cutoffs rather than relying on a scale that adjusts to the selected range. Excel’s color-scale heat map is a formatting technique, not a separate chart type.

Frequently Asked Questions

Does Excel have a built-in heat map feature?
Yes. The common built-in method is Conditional Formatting > Color Scales. Select the cells, then choose Home > Conditional Formatting > Color Scales. Excel colors the cells based on their values without changing the stored data.

Can I make an Excel heat map with fixed values?
Yes. Open Home > Conditional Formatting > Manage Rules, edit the color-scale rule, and set the minimum, midpoint, or maximum type to Number where appropriate. Enter meaningful thresholds so colors retain a consistent interpretation when the data range changes.

Can I make each row use its own heat map scale?
Yes. Apply a separate color-scale rule to each row, or configure rules for the relevant row ranges. This highlights within-row patterns, but a particular color may represent different values in different rows. Use a shared scale if direct comparisons between rows matter.

Will a heat map update when the numbers change?
Yes. Conditional formatting recalculates when values change. The appearance can also change when the scale’s range or thresholds change, so check the rule’s Applies to range and settings after editing the worksheet structure.

Can I use text or categories in a color-scale heat map?
A color scale is intended to map numeric values, so text categories are not a good fit. For named categories or status labels, use conditional-formatting rules that match specific text or conditions, or assign colors manually with a clearly explained legend.

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.