Build a Heat Map in Excel: Steps, Scales, and Fixes

To build a heat map in Excel, select the numeric cells you want to compare, then choose Home > Conditional Formatting > Color Scales and pick a palette. Excel colors each cell according to its value, making highs, lows, and patterns easier to spot without changing the underlying data.

What an Excel heat map shows

An Excel heat map is usually a range of cells formatted with a color scale. A color scale assigns colors based on the values in the selected range: for example, lower numbers may appear green, middle values yellow, and higher numbers red. The colors update when the values change, provided the conditional formatting rule still applies to those cells.

This is different from a geographic heat map, which places data on a map, and from a chart that plots values on axes. In a spreadsheet, the simplest heat map is a visual layer over a table. It works well for comparing many related figures at once, such as monthly sales by region, attendance by class, or response times by service team.

Color is a quick cue, not a replacement for the numbers. Readers should still be able to inspect the cell values, and the colors should make a meaningful comparison rather than merely decorate the worksheet.

Prepare the data before applying colors

Arrange the values in a clear rectangular range, with useful row and column headings outside the numeric cells. For example, a sales table might put regions in rows, months in columns, and sales figures in the body of the table. Select only the values if the headings are text; including labels can make the result less predictable or distract from the comparison.

Before formatting, check that values are genuinely numeric. Numbers stored as text may not behave as expected in comparisons or other analysis. Also decide whether blanks mean no data, zero, or something else. A blank and a numeric zero are not the same, and treating a missing result as zero can create a misleading impression.

Keep units consistent across the selected range, such as dollars throughout or percentages throughout.
Remove totals from the color-scale range if they are much larger than ordinary rows and would distort the scale.
Consider separating unlike measures. A column of percentages and a column of dollar amounts should not usually share one color scale.
Use descriptive headings and retain the underlying numbers so the colors can be checked.

Create a basic heat map

Select the cells containing the values to visualize. In a table with headings in row 1 and column A, select the numeric body rather than the headings.
On the ribbon, choose Home > Conditional Formatting > Color Scales.
Choose a preset two-color or three-color scale. Excel applies the rule immediately to the selected cells.
Review the result against the values and adjust the scale or range if important differences are difficult to see.

The exact shades available can vary with the Excel theme and version, but the Color Scales gallery contains ready-to-use options. You do not need to add a separate chart or use a formula for this basic method. To remove the effect from a selected range, choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

In Excel for Microsoft 365 on Windows, these commands are on the Home tab. Excel for Mac has conditional formatting on the Home tab as well, although the dialog layout can differ. Excel for the web also supports conditional formatting, but some advanced rule-editing options may be more limited than in the desktop app.

Choose a color scale that fits the data

A two-color scale is suitable when the main goal is to distinguish low from high values. A three-color scale adds a midpoint, which can help show low, typical, and high results. A common red-yellow-green palette is familiar, but it is not automatically the best choice: red and green can be difficult for some readers to distinguish, and colors can carry unintended meanings.

For a more controlled scale, select the range and choose Home > Conditional Formatting > Color Scales > More Rules. In the New Formatting Rule dialog, choose Format all cells based on their values, then select a two-color or three-color scale. You can set the minimum, midpoint, and maximum types and values, and choose colors for those points. Available options depend on the scale type you select.

Excel normally bases a color scale on the values in the rule’s applied range. That means the same number can receive different colors in two separate heat maps if their ranges have different minimums and maximums. If comparisons across periods or teams must use the same standard, use matching fixed limits in the rules rather than relying on each range’s own extremes.

Understand relative and fixed thresholds

Automatic minimum and maximum settings are convenient for exploring one dataset. Excel uses the distribution in the selected cells to assign colors, so the strongest and weakest values stand out even if the figures are not close to a particular business target.

Fixed thresholds are more useful when a color has a defined interpretation. For example, if a service-level table should show results below 80% as low and results at or above 95% as high, set the scale thresholds to match those standards. This makes the colors more comparable from one reporting period to another. A fixed scale can also reveal when every value is poor or every value is strong, a pattern that an automatically stretched scale may visually soften.

For data centered on a meaningful point, such as profit and loss around zero or performance against a target, use a three-color scale and make the midpoint the meaningful reference value. Otherwise, the midpoint may simply represent the middle of the observed range, which is not necessarily the same as zero, average, or target.

Example: monthly sales by region

Suppose a worksheet contains five regions in rows and twelve months in columns, with sales amounts in the body. Selecting all sixty monthly figures and applying a three-color scale can show seasonal patterns and regions that consistently lead or lag. If each row is colored using a separate rule, the colors would instead compare each region’s months with one another. Those two choices answer different questions.

For an overall comparison, apply one rule to the full sixty-cell range. A dark shade then means a value is high relative to all selected sales figures. For a within-region comparison, apply a scale separately to each row, or create separate rules for each row. In that view, the darkest month in every region may represent that region’s peak, even if the peak amounts differ substantially.

Decide which comparison matters before formatting. A heat map can make a pattern appear obvious while concealing the fact that the scale is relative to a particular group or date range. Include a note in the worksheet if the thresholds or comparison basis are not self-evident.

Common problems and how to fix them

Nearly every cell has the same color: The values may be tightly clustered, or the scale limits may be too broad. Review the minimum and maximum and consider fixed thresholds that highlight useful differences.
One extreme value washes out the rest: A very high or low outlier can stretch an automatic scale. Check whether the outlier is valid, exclude it only if that is analytically justified, or set thresholds that better suit the question.
Totals dominate the colors: Remove grand totals or subtotals from the rule’s range when they are not comparable with individual values.
New rows are not colored: The conditional formatting rule may not include the added cells. Open Home > Conditional Formatting > Manage Rules and inspect the range shown in Applies to. Expand it as needed, or create the formatting after converting a structured dataset to an Excel table.
Blank cells look like low values: Confirm that blanks are truly blank and decide how missing data should appear. Do not replace missing figures with zero unless zero is the correct value for the analysis.
Several rules conflict: In Manage Rules, check the rules applied to the range and their order. Remove obsolete rules or adjust the applied ranges so the intended color scale is clear.

If colors look different after copying cells, inspect the conditional formatting rules rather than assuming the numbers changed. Copying and pasting can bring formatting rules along or create overlapping rules. Manage Rules lets you review and edit rules for the current selection or worksheet.

Make the heat map readable and accessible

Choose colors with enough contrast and avoid relying on red versus green alone to communicate a critical status. A single-hue scale, such as pale to dark blue, can be easier to interpret for general low-to-high comparisons. Where the distinction matters, add labels, symbols, or a written threshold so color is not the only signal.

Keep the numbers visible unless the worksheet has a clear reason to hide them. Use a number format that fits the measure, such as percentages with an appropriate number of decimal places or currency with a consistent unit. Avoid excessive decimal precision: it can make a compact heat map harder to scan without improving the comparison.

Consider the printed or exported version too. Pale fills can disappear on a low-quality printout, and colors may shift when a workbook is viewed on another screen. A short legend or note describing what the light and dark ends mean can make the display more dependable for other readers.

Heat maps versus other Excel formatting

Color scales are best for showing gradual differences across many values. If the question is whether a cell passes a specific threshold, a rule such as Highlight Cells Rules may communicate that more directly. For example, highlighting all values below a target gives a clear pass-or-review signal, while a continuous scale shows relative degrees across the whole range.

Data bars are another conditional formatting option. They place a bar inside each cell and can make magnitude comparisons intuitive, especially when precise values are also visible. Icon sets group values into categories using symbols. These approaches are not interchangeable: choose a color scale for a continuous pattern, a threshold rule for a defined cutoff, and a chart when readers need a separate visual with axes, labels, or a trend over time.

Frequently Asked Questions

Can Excel create a heat map without a formula?
Yes. Select the numeric range and use Home > Conditional Formatting > Color Scales. Excel creates the visual formatting rule without requiring a worksheet formula.

Can I make a heat map with negative numbers?
Yes. A color scale can format negative and positive values. For data centered on zero, use a three-color scale and set zero as the midpoint so negative and positive values fall on different sides of the reference point.

How do I keep the same colors across multiple heat maps?
Set consistent minimum, midpoint, and maximum thresholds in each rule instead of letting each range choose its own limits. Check that the ranges use the same units and that the chosen thresholds make sense for every group.

Can an Excel heat map update when I change values?
Yes. Conditional formatting recalculates the colors as cell values change. If you add cells outside the rule’s applied range, however, you may need to extend the range in Manage Rules.

Can I create a geographic heat map in Excel?
A cell color scale is not a geographic map. Excel has map chart features for certain geographic data, but their availability and behavior differ from conditional formatting; for a table-based comparison, Color Scales is the direct heat map method.

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.