Microsoft Excel Heat Maps: How to Create and Read Them

A Microsoft Excel heat map uses cell colors to show the relative size or pattern of values in a range. In Excel for Microsoft 365 on Windows, select the numeric cells and choose Home > Conditional Formatting > Color Scales, then pick a palette; Excel applies a color scale that updates when the values change.

What an Excel heat map shows

A heat map is a visual layer over a table, not a separate chart type. Each cell’s fill color represents its value according to a scale, making clusters, highs, lows, and gradual changes easier to spot than they may be in a grid of numbers. The underlying values remain in the worksheet, and conditional formatting determines their appearance.

For example, a sales table might have products in rows, months in columns, and revenue in the cells. A color scale can make high-revenue months stand out while quieter periods appear in a different color. A class-grade table, service response-time matrix, or production log can use the same approach, provided the numbers are meaningful to compare.

Excel’s built-in Color Scales conditional-formatting rules are often what people mean by an Excel heat map. They are different from a geographic map chart, which plots locations on a map, and from a chart that displays data as a separate visual. The heat map remains inside the worksheet’s cells.

Create a heat map with a color scale

For a basic heat map, use a range of numeric cells without including headings or totals. On Windows, follow these steps in Excel for Microsoft 365:

Select the cells containing the values to compare, such as B2:G13.
Choose Home > Conditional Formatting > Color Scales.
Point to a two-color or three-color palette and choose one that makes low and high values easy to distinguish.
Review the result and change the rule if the colors obscure the numbers or do not communicate the pattern clearly.

The format is conditional: if a value changes, Excel can update that cell’s color based on the rule. If the table grows, check whether the new rows or columns are included in the rule’s range. A rule applied only to B2:G13 does not necessarily cover data entered later in row 14.

To inspect or adjust a rule, select a formatted cell and choose Home > Conditional Formatting > Manage Rules. In the rules manager, check which cells the rule applies to, then edit or remove the rule as needed. If the sheet contains several rules, set the scope carefully so that a rule for one table does not accidentally format another.

On Excel for Mac, conditional formatting is also available from the Home tab, including Color Scales; the exact layout can vary by release. Excel for the web offers conditional formatting and color scales as well, though some detailed rule-editing controls may differ from the Windows desktop app. If you cannot find a particular setting in the web interface, open the workbook in desktop Excel.

Choose the right scale and comparison

A two-color scale emphasizes a transition from a low end to a high end. It is useful when the main question is simply which values are smaller or larger. A three-color scale adds a midpoint color, which can make a central value or middle band visually distinct. For instance, a red-to-yellow-to-green scale may communicate low, middle, and high results, but those color meanings should fit the subject and audience.

Excel can base a color scale on different kinds of endpoints and, for a three-color scale, a midpoint. Depending on the rule, options can include lowest or highest value, a number, a percentage, a percentile, or a formula. These choices change what the colors mean:

Lowest and highest values: The endpoints follow the smallest and largest values in the selected range. This is convenient for a quick view of relative extremes.
Fixed numbers: A defined threshold keeps the same meaning as the data changes. This is useful when a value has a real target, limit, or service standard.
Percentiles: A percentile-based endpoint reflects a position in the distribution rather than a fixed measurement. It can help when a few extreme values would otherwise dominate the scale.
A midpoint: A three-color scale can make a meaningful center, such as zero or a target, visually distinct from values on either side.

Consider a table of monthly response times measured in minutes. If the colors always stretch from the quickest to slowest month, a month may look unusually good or bad even when all months are close together. If the organization has a fixed target, using that target as a scale boundary or midpoint may tell a more useful story. The appropriate setup depends on whether the reader needs relative ranking or comparison against a stable standard.

Prepare the data and layout

Heat maps work best when the range has a clear structure: one value per cell, consistent units, and labels that explain rows and columns. Put headings outside the formatted numeric range. Including a grand total or average in the same range as individual observations can distort the color scale because the summary value may be much larger or smaller than the entries around it.

Before formatting, check that values are genuinely numeric. A number stored as text may not behave like a numeric value in a color-scale rule. Also look for mixed units, missing values, and formulas that return unexpected results. For example, comparing dollars with percentages in one scale may technically produce colors but does not make the cells meaningfully comparable.

For a matrix such as products by months, one scale across the entire table answers, “Which product-month combinations are high or low compared with the whole table?” It does not answer, “Which month is highest for each product?” Those are different comparisons. If each row needs its own low-to-high range, apply a separate color-scale rule to each row or use another design that makes the intended comparison clear. Remember that a row-specific scale makes colors comparable within a row, not necessarily from one row to another.

Sorting can help reveal patterns, but preserve the relationship between labels and values when you sort. If a heat map is part of a report, freeze or repeat headings where appropriate and add a short note explaining the scale’s direction and units.

Read the colors without overinterpreting them

A color scale communicates position on a range; it does not explain why a value is high or low, whether the difference is important, or whether a result is good. A dark cell might simply be the maximum in a small group. If all values are close together, a gradient can make small differences look dramatic. Keep the actual numbers visible when those distinctions matter.

Look at the scale endpoints and the underlying values before drawing conclusions. If the minimum is 48 and the maximum is 52, a full gradient across those numbers can make a difference of four units appear vivid. Conversely, a single extreme value can compress the colors for the rest of the data. Choosing fixed boundaries, percentiles, or a different visualization can make the pattern more informative, as long as the choice is disclosed and appropriate.

Color should not be the only way to convey a critical status. People may print the sheet in grayscale, have color-vision differences, or view it on a display with poor contrast. Use clear labels, retain numeric values, and consider adding text such as “Below target” or “Above target” when a decision depends on a threshold. Avoid relying on red and green alone to communicate pass and fail.

Customize the rule for a specific purpose

To make the scale more precise, select a cell in the range and open Home > Conditional Formatting > Manage Rules, then edit the relevant rule. You can change the scale type, the values used for its limits, and the colors. The dialog’s options depend on whether the rule is a two-color or three-color scale. A fixed boundary is often easier to interpret over time than an automatically recalculated minimum or maximum.

For categories rather than a continuous gradient, use a rule that highlights cells meeting a condition instead. For example, a discrete format can mark values below a limit in one color and values above it in another. That can be more direct when a threshold is the only decision that matters. A gradient is generally better for exploring gradual variation across many numeric values.

Conditional formatting can also be combined with other worksheet features, but overlapping rules may produce unexpected results. If a cell has more than one applicable rule, inspect the rules manager and their order and settings. Keep the number of competing visual treatments small; otherwise the sheet can become harder to read than the unformatted table.

Common problems and how to fix them

The colors seem reversed: Edit the rule and verify which color is assigned to the low and high endpoints. Do not assume red always means low or bad.
Everything looks nearly the same: Check for an outlier stretching the scale, or for values that are tightly grouped. Consider appropriate fixed limits or percentile endpoints, and keep the actual numbers visible.
Totals dominate the colors: Remove totals and summary rows from the scale’s applied range, or format summaries separately.
New data is not formatted: Use Manage Rules to inspect the “Applies to” range and extend it to cover the new cells. Avoid including unrelated cells.
Blank or text cells look different: Confirm that the intended cells contain numeric values and that blanks are not being mistaken for zeros. Check formulas and data types before changing the colors.
Colors do not match a colleague’s screen: Display settings, themes, printing, and app versions can affect appearance. Share the workbook with its values and labels, not just a screenshot of its colors.

When to use another visualization

A heat map is particularly useful for a dense two-dimensional table where the position of a value matters, such as weeks by departments or products by months. It is less effective when readers need exact comparisons among a few values, when the table has no natural row-and-column structure, or when colors cannot be interpreted without repeated reference to a legend.

A sorted bar chart is often easier for comparing a short list of categories because the lengths can be compared along a common baseline. A line chart is usually better for showing a time trend, especially when the order of dates matters. A pivot table can summarize a large dataset before a heat map is applied; a heat map then adds visual emphasis to the resulting grid. For geographic patterns, use a map visualization rather than treating a cell-color table as a location map.

Excel’s built-in color scales are included as part of its conditional-formatting tools, so a basic heat map does not require a separate add-in or a dedicated chart. More elaborate dashboards may use other Excel features or separate reporting software, but the simple cell-based method is often sufficient for a worksheet analysis.

Frequently Asked Questions

Does Excel have a built-in heat map feature?
Yes. Excel’s Color Scales under Home > Conditional Formatting apply a heat-map-like gradient to selected cells. It is conditional formatting on worksheet cells, not a separate chart object.

How do I make a heat map in Excel without a formula?
Select the numeric range and choose Home > Conditional Formatting > Color Scales, then select a palette. Excel creates the color-scale rule for you; a formula is not required for the standard method.

Can I make each row use its own heat-map scale?
Yes. Apply a separate color-scale rule to each row when the goal is to compare values within that row. Because each row then has its own scale, matching colors in different rows may represent different numeric values.

Can I use a heat map with negative numbers?
Yes. A color scale can format negative and positive numeric values. If zero is an important dividing point, edit the rule to use a meaningful midpoint so the colors distinguish values below and above zero clearly.

Why did my heat map change after I added data?
The selected range may have expanded, or the rule may now include a new minimum or maximum that changes the scale. Check Home > Conditional Formatting > Manage Rules to confirm the applied range and the endpoint settings.

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.