Excel Heatmap Guide: Create, Customize, and Read One

To make a heatmap in Excel for Microsoft 365, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a palette. Excel colors cells according to their values; the formatting does not change the underlying data.

What an Excel heatmap shows

A heatmap uses color to make patterns in a grid easier to spot. In Excel, the quickest way to create one is with conditional formatting: a rule compares each cell’s value with a scale and applies a fill color. A row of monthly sales, a staff-by-day schedule, or a table of survey scores can become easier to scan when higher and lower values stand out visually.

Excel’s Color Scales are not a separate chart type. They color the cells themselves, so the numbers remain visible and can still be sorted, filtered, copied, or used in calculations. A three-color scale commonly uses one color for lower values, another for middle values, and a third for higher values. A two-color scale shows a transition between low and high values.

The colors are relative to the rule’s range unless you configure fixed thresholds. That distinction matters: a cell colored green in one heatmap is not necessarily higher than a green cell in another. Each chart may use a different range or scale, so colors should be interpreted in the context of the data and rule.

Prepare the data before adding colors

Start with a rectangular range that contains comparable numeric values. Put clear labels in a separate header row or column, keep units consistent, and avoid mixing totals or explanatory text into the values you want to color. For example, a table of regional sales by month is a useful heatmap if every value is measured in the same currency and period.

Check that numbers are actually stored as numbers. Imported values can look numeric while being stored as text; those entries may not behave as expected in a color scale. If a value is left-aligned unexpectedly, or sorting puts it in an odd position, check its cell format and source data. Also decide what blanks and errors mean. A blank usually represents missing information, not zero, and treating it as zero can create a false impression of poor performance.

Use consistent units, such as percentages throughout a range rather than a mix of percentages and whole-number counts.
Keep headers outside the selected cells unless you specifically want them included in the formatting.
Separate totals from ordinary observations when totals would distort the scale.
Decide whether unusual values are valid data, data-entry errors, or a different category that should be analyzed separately.

Create a heatmap with Color Scales

In Excel for Microsoft 365 on Windows, select the cells to format, then use Home > Conditional Formatting > Color Scales. Choose a two-color or three-color preset from the gallery. The colors appear immediately, and the values in the cells are unchanged. If you select a range with headers, Excel may color those cells too, so undo the action or adjust the rule if the labels should remain plain.

For example, select a block of monthly order counts for several products. A color scale can show which product-month combinations are relatively busy or quiet. It does not identify why a value is high, nor does it establish that a high count is desirable; interpretation depends on what the numbers represent.

To alter the rule later, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the rule, its range, and its settings. The Show formatting rules for menu controls which worksheet or selection’s rules are displayed. Select the rule and choose Edit Rule to change the scale, or use the available controls to remove a rule that is no longer needed.

Excel for Mac has conditional formatting tools on the Home tab as well; the menu wording and dialog layout can vary slightly by release. In Excel for the web, use the Home tab’s Conditional Formatting controls. If a command or dialog is arranged differently, look for Color Scales within Conditional Formatting and verify the rule in the browser version’s available settings.

Choose a scale that fits the question

A two-color scale is often suitable when the main question is simply which values are relatively low and which are relatively high. A three-color scale adds a midpoint, which can help distinguish lower, middle, and upper parts of a distribution. Use a midpoint with a clear meaning when possible, such as a target or neutral value, rather than choosing a dramatic palette just because it looks attractive.

Color direction should match the interpretation. For response time, lower may be better; for completion rate, higher may be better. A red-to-green palette can imply bad-to-good even when the data does not have that meaning. Consider a color-blind-friendly palette and avoid relying on color alone: retain the values, use clear headings, and explain what the colors signify.

For comparisons against a standard, a fixed midpoint or threshold can be more informative than a range-based scale. Suppose a performance table has a target of 100. If each selection is independently colored from its minimum to maximum, 95 might appear as the top color in one group and 105 as the bottom color in another. A rule anchored to the target makes the comparison more consistent. In Edit Formatting Rule, review the minimum, midpoint, and maximum types and values; choose settings that reflect the question rather than accepting defaults automatically.

Understand ranges, outliers, and comparisons

By default, a scale derives its colors from the values covered by that rule. Consequently, one extreme value can stretch the range and make most other cells look similar. For example, if nearly all daily sales are between 20 and 80 but one cell contains 900, the remaining values may occupy a narrow portion of the color transition. First check whether the extreme is an error. If it is valid, consider setting meaningful fixed limits, using a separate scale for a distinct group, or showing the outlier in context rather than hiding it.

Think carefully about the range the rule applies to. One rule across an entire table makes cells comparable within that table. Separate rules for each row can make within-row patterns easier to see, but a dark cell in one row may represent a smaller number than a pale cell in another. Choose based on the question: comparing products against one another usually calls for a shared scale, while examining each product’s changing pattern over time may call for row-by-row scales.

Do not compare colors across separate worksheets or reports unless the scale settings and thresholds match. If a manager sees the same shade in two reports, that shade only means the same thing when both reports use equivalent ranges and rule settings. State units and thresholds near the display, especially when the heatmap will be printed or shared without its source workbook.

Common problems and how to fix them

Nothing changes: Check that the selected cells contain numeric values, that the rule applies to the intended range, and that another conditional-formatting rule is not taking precedence.
Headers are colored: Edit the rule’s Applies to range in Manage Rules so it covers only the data cells.
Colors look nearly identical: Check for an outlier, a narrow spread in the data, or a scale whose endpoints are not useful for the comparison.
New rows are not colored: Inspect the rule’s Applies to range after adding data. If the range did not expand, extend it or recreate the rule for the larger range.
Sorting or filtering seems to change the impression: The visible cells may represent only part of the range, while the rule still uses its full applied range. Confirm the intended comparison scope before drawing conclusions.

Multiple conditional-formatting rules can also make results confusing. Open Manage Rules and review their order, scope, and any Stop If True settings where shown. When a workbook has accumulated rules from copied cells, removing obsolete rules and applying one clearly scoped heatmap rule is often easier to maintain than adding more exceptions.

Heatmaps versus other ways to show data

A cell-based heatmap works well when readers need to compare many values in a compact grid and retain the exact numbers. It is less effective when the audience needs to follow a trend over time, compare a small number of categories precisely, or read values without color. A line chart can make a time trend clearer; a bar chart is often better for ranking categories; a simple table may be best when exact values matter more than patterns.

Excel’s other conditional-formatting rules can answer narrower questions. Data Bars show approximate magnitude inside each cell, while Icon Sets place symbols beside values. Highlight Cells Rules can mark values above a threshold, duplicates, or other defined conditions. These are not identical to a continuous heatmap, but can be more direct when the task is to find exceptions rather than explore a whole distribution.

A color-coded table can also be built with manually assigned fills, but manual colors do not update reliably when the data changes. Conditional formatting is generally preferable for a live report because Excel recalculates the appearance when values change. For a polished dashboard, a heatmap can be one view among several rather than the only evidence offered to the reader.

Frequently Asked Questions

Can Excel make a heatmap automatically?
Yes. Select the numeric range and use Home > Conditional Formatting > Color Scales. Excel applies a preset scale immediately, but you should check the rule’s range and thresholds before relying on the colors.

Can I make an Excel heatmap with fixed values?
Yes. Edit the Color Scale rule and set its minimum, midpoint, or maximum to a chosen type and value. Fixed thresholds are useful when comparing groups against the same target, because each group then uses a consistent reference.

Why do two cells with the same number have different colors?
They may belong to different conditional-formatting rules or ranges with different limits. Review each rule in Manage Rules and confirm that both cells are evaluated against the same scale if you expect the colors to match.

Can I create a heatmap from a PivotTable?
Conditional formatting can be applied to PivotTable values, but the rule’s scope matters as the PivotTable changes. When creating or editing the rule, check whether it applies to selected cells or to the relevant PivotTable value field, and verify the result after refreshing the PivotTable.

Will the heatmap update when I change a value?
Yes, conditional formatting recalculates as cell values change. However, new cells may fall outside the rule’s Applies to range, so check the rule after extending the data or adding rows.

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.