Excel Heat Maps: How to Create, Customize, and Read Them

To create a heat map in Excel for Microsoft 365 on Windows, select the numeric cells you want to compare, then choose Home > Conditional Formatting > Color Scales and select a color scale. Excel colors cells according to their values, making high and low figures easier to spot; you can customize the colors and thresholds through the conditional-formatting rules.

What an Excel heat map shows

A spreadsheet heat map uses cell background colors to represent the size of the values in a range. A common setup colors lower numbers one way, higher numbers another way, and values in between with a gradual blend. The result is a visual pattern across rows and columns, not a separate chart. It can help reveal concentrations, gaps, unusually high or low results, and changes across a grid of related measures.

For example, a sales manager might arrange monthly sales by product, with products down the rows and months across the columns. A color scale can quickly show which products had stronger months and whether a particular month was weak across the board. A heat map is useful for exploring a table, but it does not explain why a value is high or low. Keep labels and, where appropriate, the underlying figures visible so readers can interpret the colors correctly.

Excel’s Color Scales feature is a form of conditional formatting: the cell’s appearance changes according to its value. It does not alter the value itself. This makes it different from manually filling cells with colors, which can become misleading as the data changes.

Prepare the data before applying colors

Start with a rectangular range in which each row and column has a clear meaning. Put descriptive headings outside the numeric range, and avoid including totals if they are not comparable with the individual values. For instance, a grand total may be much larger than each monthly figure; including it can compress the rest of the colors into a narrow band.

Use consistent units. Do not compare percentages, dollar amounts, and counts in the same color scale unless that comparison is intentional.
Check for values stored as text. Text that looks like a number may not be treated as a numeric value by the scale.
Decide how to handle blanks, errors, and missing observations. A blank is not the same as zero, so do not enter zero merely to make the table look complete.
Consider whether all rows should share one scale. If each row is colored independently, patterns within rows may stand out, but colors will no longer represent the same absolute values across the whole table.

For a small example, imagine a grid with products in rows and quarters in columns. If the values are all quarterly sales in the same currency, applying one scale to the complete grid makes a dark cell mean a relatively high sales figure within that selected range. If some products are measured in units and others in revenue, one shared scale would be difficult to interpret.

Create a basic heat map

Select the cells containing the values to visualize. Leave out headings and totals unless you deliberately want them included. You can drag across a range or click the first cell and use Shift while selecting the last cell.
On the Home tab, choose Conditional Formatting > Color Scales.
Choose a two-color or three-color preset. Excel applies the scale to the selected cells immediately.
Inspect the result against the figures. If the colors obscure the text or exaggerate small differences, choose another scale or set explicit thresholds.

Excel’s presets commonly use a progression from one color to another, or a three-color progression with a midpoint. The exact palette options can vary by Excel release and theme. On Excel for Mac, the corresponding command is also on the Home tab under Conditional Formatting; ribbon details can vary slightly by version. Excel for the web also offers conditional formatting, though its rule-editing interface may differ from the Windows desktop steps.

A useful first pass is to apply a scale to a representative range, then decide whether it communicates the pattern you want. The default scale is based on the selected values, so the strongest and weakest values in the range determine the ends of the gradient. That is convenient for exploration, but it means colors can shift if the range or its data changes.

Customize colors and thresholds

To define a more deliberate scale, select the cells and go to Home > Conditional Formatting > Manage Rules. Choose the relevant rule, select Edit Rule, and use the color-scale options. If no rule exists yet, choose New Rule and select Format all cells based on their values, then choose a two-color or three-color scale. In the rule dialog, set the color and type for the minimum, midpoint if present, and maximum.

Threshold types serve different purposes. Lowest Value and Highest Value adapt to the smallest and largest numbers currently in the range. Number uses an explicit value, which is helpful when a meaningful boundary is known. Percentile uses the distribution’s rank rather than a fixed numeric cutoff; it is not the same as setting a threshold to a percentage of the maximum. Choosing thresholds intentionally can make comparisons more stable and meaningful.

A three-color scale is often useful when the data has a meaningful center, such as results below, near, or above a target. For a performance grid where zero means no change, set the midpoint type to Number and its value to 0, then select a neutral midpoint color. This can distinguish negative from positive results even when the overall range is uneven. If all numbers are positive and there is no meaningful center, a two-color gradient may be clearer.

Use restrained colors with enough contrast to distinguish the ends of the scale. Very saturated colors can make a worksheet tiring to read, while similar colors may be hard to tell apart. Consider readers with color-vision differences: do not rely on color alone to communicate a critical status. Keep the numbers available, use clear labels, and consider adding text indicators or a separate explanation of the scale.

Choose a scale that answers the question

The right scale depends on what the reader should notice. If the question is which cells are comparatively high or low in this one dataset, an automatically scaled gradient is a reasonable starting point. If the question is whether each value meets a known target, use a fixed threshold or consider a separate rule that marks values above and below the target. A scale that changes its endpoints every time new data arrives may be unsuitable for month-to-month comparisons.

Use one shared scale when readers need to compare absolute values across the whole grid.
Use separate scales by row or column only when within-row or within-column patterns matter more than comparing exact values between groups. Applying separate rules takes more setup and can make identical colors represent different numbers.
Use a midpoint when a central value has a real interpretation, such as zero change or a target score.
Use fixed numeric limits when the same colors must mean the same thing in recurring reports or comparisons between worksheets.

For example, suppose a report shows customer satisfaction scores from 1 to 5. A scale that uses the observed minimum and maximum may make a table of scores from 3.8 to 4.4 look as varied as a table spanning 1 to 5. If the goal is to compare performance against the full rating system, fixed limits based on the scale’s actual boundaries are more informative. The choice should match the decision the visualization supports.

Read the pattern without overinterpreting it

Color helps the eye find patterns, but a heat map can also make ordinary variation look dramatic. Always check the values and the scale’s endpoints before drawing conclusions. A single unusually large number may set the maximum and make most other cells look nearly alike. Conversely, a narrow range can still produce vivid differences when Excel maps its lowest value to one end of the gradient and its highest to the other.

Look for patterns that are meaningful in the table’s context: repeated high values in a product row, a low-performing month across several categories, or a gradual change across time. Then verify those impressions by reading the figures. A heat map can guide attention; it cannot establish a cause, prove that a difference is important, or replace an appropriate statistical comparison.

For presentations or printed reports, check the worksheet at the size people will actually view it. Small cells, long labels, and dark fills can reduce readability. You may want to widen columns, adjust number formats, or use a lighter palette. If the audience needs exact values, retain them in the cells rather than displaying color alone.

Common problems and fixes

If the colors do not appear, first confirm that the selected cells contain numeric values and that the conditional-formatting rule applies to the intended range. Open Home > Conditional Formatting > Manage Rules and inspect the Applies to range. A rule may cover only part of the table, or a different rule may be affecting the same cells.

Headers are colored: Edit the Applies to range so it covers only the data cells, not the heading row or column.
Nearly every cell looks the same: Check for an outlier, a total included in the range, or values on very different scales. Consider fixed thresholds or a carefully chosen midpoint.
Colors change after adding data: A scale based on Lowest Value and Highest Value recalculates from the values in its applied range. Use explicit limits for a stable comparison, and confirm the new cells are included in Applies to.
Numbers that appear numeric are ignored: Check whether they are stored as text, perhaps because of imported data or inconsistent formatting. Convert them to real numbers before relying on the scale.
Rules behave unexpectedly: Review overlapping conditional-formatting rules in Manage Rules. Editing or deleting an unrelated rule can change what appears in the range.

When data is in an Excel table, new rows may be included as the table expands, but do not assume every rule has the intended scope. Check the Applies to range after adding rows, especially in a workbook with copied formatting or several conditional-formatting rules. The range shown in Manage Rules is the practical way to confirm which cells are controlled.

Update, manage, or remove a heat map

Conditional formatting responds when values change, so you usually do not need to reapply a color scale after editing a number. If you paste new data, however, check both the rule’s range and whether pasted formatting brought in additional rules. The Conditional Formatting Rules Manager lets you choose which rules to inspect, edit their settings, or delete a selected rule.

To remove a scale, select the affected cells and choose Home > Conditional Formatting > Clear Rules. Select Clear Rules from Selected Cells if you want to keep other conditional formatting elsewhere in the worksheet. Be careful with Clear Rules from Entire Sheet: it removes conditional-formatting rules across that sheet, not just the heat map you were viewing. Clearing a rule does not delete the underlying cell values.

For a recurring report, consider documenting what the colors mean and keeping the same numeric limits each time. If the scale is intentionally recalculated for each reporting period, label that fact so a reader does not assume that the same shade represents the same number in every report.

Heat maps compared with other Excel visuals

A cell heat map is usually best when the data is already a two-dimensional table and readers need to scan many values at once. A bar chart is often better for comparing a small number of categories because bar lengths make differences easier to judge. A line chart is generally more suitable for showing a trend over time. These formats answer different questions, so the heat map is not automatically the best choice just because the data is in a grid.

Excel’s Color Scales feature also does not create a geographic heat map. It colors worksheet cells; it does not calculate geographic density or draw a shaded map of locations. Excel has a Filled Map chart for supported geographic data, which shades areas according to values, but that is a regional map visualization rather than a density heat map. Use the visualization that matches the location data and the question being asked.

Frequently Asked Questions

Can Excel make a heat map automatically?
Yes. Select numeric cells and choose Home > Conditional Formatting > Color Scales. Excel applies a preset based on the values in the selected range, and you can edit the rule afterward.

Can I create a heat map with different colors?
Yes. Open the rule through Home > Conditional Formatting > Manage Rules, select Edit Rule, and change the colors and threshold types. You can also create a new two-color or three-color scale.

How do I make zero the center of an Excel heat map?
Edit a three-color scale and set its midpoint type to Number with a value of 0. Choose colors that distinguish negative values, the midpoint, and positive values, then verify that the data includes values on both sides of zero if that is the comparison you intend.

Can Excel create a heat map from a PivotTable?
Conditional formatting can be applied to PivotTable values, but PivotTables can change size when refreshed. Check the rule’s scope and its options for applying formatting to PivotTable value cells so the intended cells remain formatted after refreshes.

Is an Excel color scale the same as a geographic heat map?
No. A color scale shades worksheet cells based on their values. For geographic regions, consider Excel’s Filled Map chart if the location data and geography are supported; it represents values by area rather than cell-by-cell density.

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.