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

How-to basics · Updated November 21, 2026

In Microsoft Excel for Microsoft 365 on Windows, create a heat map by selecting a numeric range and choosing Home > Conditional Formatting > Color Scales. Excel then colors cells according to their values, making high and low results easier to spot; you can adjust the scale, comparison range, and colors to suit your data.

What an Excel heat map shows

An Excel heat map is a visual treatment of worksheet cells: a color represents each cell’s value, or its position relative to other values in a selected range. A common example uses red for low values, yellow for middle values, and green for high values. The resulting pattern can reveal clusters, outliers, seasonal changes, or differences between categories faster than a table of numbers alone.

Excel does not have a separate worksheet feature named Heat Map. The usual method is Conditional Formatting, specifically a Color Scale rule. The rule changes a cell’s appearance based on its value without replacing the value itself. You can still select the cell and read its underlying number in the formula bar, use it in calculations, or sort and filter the data.

A heat map is useful for data arranged in a grid, such as monthly sales by product, attendance by weekday and class, or response times by support team. It is most informative when the cells can be compared meaningfully. A colored grid is not automatically a good visualization: the scale and the way the data is organized affect what readers infer.

Create a basic heat map

For a quick result, select only the cells whose values you want to color. On the Windows desktop version of Microsoft 365, choose Home > Conditional Formatting > Color Scales, then select a preset. Excel applies the rule to the selected range. A three-color scale commonly shows low, middle, and high values with different colors; two-color options show a transition between two ends.

  1. Arrange the values in a rectangular range, with clear row and column labels outside the cells to be shaded.
  2. Select the numeric cells, not the labels or totals unless those should be part of the comparison.
  3. Choose Home > Conditional Formatting > Color Scales.
  4. Choose a preset whose light and dark colors are easy to distinguish.
  5. Check several cells against their actual numbers and confirm that the visual pattern makes sense.

For example, suppose a worksheet has four product rows and twelve month columns. Selecting just the 48 monthly sales cells and applying a color scale lets a reader see strong months and weak months across products. Including a row of yearly totals in that selection could distort the scale, because those totals are much larger than monthly values. Keep totals separate unless comparing them is an intentional part of the analysis.

In Excel for Mac, Conditional Formatting and Color Scales are also available from the Home tab, although the ribbon layout can differ by release and window size. Excel for the web also supports conditional formatting and color scales; some rule-editing details and controls can differ from the desktop app. If a particular formatting option is not visible in the web interface, open the workbook in the desktop version.

Prepare the data before coloring it

Good preparation prevents a heat map from showing accidental patterns. Keep one kind of measurement in the heat-map range. For example, do not mix dollar sales, percentages, and unit counts in one color scale: their magnitudes are not directly comparable. Use consistent units and make the time periods or categories clear in nearby headings.

Check for missing, invalid, or unusual entries. A blank cell is not the same as a measured zero, and text such as N/A is not a numeric measurement. Decide whether a zero means no activity, a true zero result, or missing information, and label the distinction where readers can see it. For meaningful gaps, leaving the cell blank may be clearer than entering zero, which can make the lowest color appear.

  • Use a consistent number format, such as currency, percentage, or whole number, within the range.
  • Keep labels, subtotals, and grand totals outside the color-scaled cells when they are not part of the comparison.
  • Review data for outliers, duplicate entries, and inconsistent category names.
  • Make sure the rows and columns have a clear order, such as chronological order for months.

Outliers deserve particular attention. If most values are between 10 and 20 but one is 500, a scale based on the full range may make the ordinary values look nearly identical. The outlier may be a genuine exceptional result or a data-entry mistake. Investigate it before changing the scale to make the remaining differences more visible.

Choose a useful color scale

Excel’s preset scales are convenient, but a color sequence should match the meaning of the data. A sequential scale, where color becomes progressively darker or more intense, usually suits measurements that run from low to high. A diverging scale, with a middle color and distinct colors on either side, suits data with a meaningful center such as zero, a target, or an average.

For example, a diverging scale can help show positive and negative differences from a budget target. A sequential scale is often more suitable for monthly revenue when every value is nonnegative. Do not assume that green always means good and red always means bad: in a delay or defect table, a high value may be undesirable. Add a note or legend that explains the color meaning if readers might interpret it differently.

Consider accessibility as well as appearance. Some readers have difficulty distinguishing red from green, and colors can look different on different displays or in print. Use contrasting lightness, avoid relying on color alone, and retain the numbers in the cells. A short explanation, clear labels, and readable values allow readers to understand the table even when they cannot distinguish every shade.

Customize thresholds and comparison ranges

To adjust a scale, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, choose the relevant rule and edit it. You can also use Home > Conditional Formatting > New Rule to create one. Choose Format all cells based on their values, then select a two-color or three-color scale and set the color and value type for each point.

Depending on the scale, points can be based on values, percentages, percentiles, or a formula. These choices are not interchangeable. A percentile-based midpoint describes a position in the distribution; it is not necessarily the arithmetic average. A numeric midpoint sets a specific cutoff. If zero is the meaningful dividing line, set the midpoint to a number and enter zero rather than relying on an automatic midpoint that may fall elsewhere.

The selected range defines the comparison context. If you apply one rule to the entire table, Excel colors cells relative to the values in that broader range. If each product should be compared only with its own monthly results, separate rules or carefully defined ranges may be more appropriate. Conversely, if the goal is to compare all products and months against one shared scale, applying a consistent rule to the full data area helps make the colors comparable.

Think carefully before applying different scales to different rows. Row-by-row scaling can reveal each product’s own high and low months, but the same shade may represent very different sales amounts in different rows. A shared scale supports comparisons of absolute values; individual scales emphasize patterns within each row. State which interpretation the heat map is designed to support.

Common problems and how to fix them

If the colors seem backward, edit the rule and check which color is assigned to the minimum and maximum. If the middle color appears in an unexpected place, inspect its type and value. Excel’s automatic scale may choose endpoints or a midpoint based on the selected values, so it may not match a business target or a threshold that matters to readers.

If adding or editing values does not produce the expected result, check which cells the rule applies to. Choose Home > Conditional Formatting > Manage Rules and inspect the rule’s Applies to range. A rule may cover only part of the table, or several overlapping rules may affect the same cells. Correct the range or remove rules that are no longer needed. Be cautious when copying and pasting formatted cells, because copied conditional formatting can introduce extra rules.

Other common causes of confusing results include numbers stored as text, totals included with detail data, and blank cells that have been filled with zero. Verify that the values are numeric where appropriate and that the rule excludes headings. If you sort or add rows, check that the rule still covers the intended data. A visually odd cell can also be correct if it is an outlier, so compare its displayed color with its actual value before changing the rule.

Heat maps, color scales, and map charts

In everyday Excel use, heat map often means a color-scaled worksheet range. This is different from a geographic map chart, which plots locations or regions and uses geographic information. Excel’s Filled Map chart is intended for geographic data such as countries, states, or postal regions; it is not the same as shading a grid of sales values with Conditional Formatting.

Color scales are also different from data bars and icon sets, which are other Conditional Formatting options. Data bars add a bar inside each cell to show relative magnitude, while icon sets display symbols such as arrows or traffic-light icons. A color scale is compact and works well across a dense matrix, but it does not show exact differences as directly as numbers or bars. Choose the visual treatment that fits the comparison, and avoid stacking several competing signals on the same cells.

Limits, sharing, and printing

A heat map summarizes values visually but does not explain why a pattern exists. A dark cell may indicate a high value, not a good result, and a pale cell may reflect the scale rather than a meaningful absence. Readers should be able to see the underlying values, the units, the time period, and what the colors represent. For a presentation or report, add a concise title and a legend or note describing the scale.

Conditional formatting is part of the workbook, so recipients who open the file in a compatible version of Excel can view and edit the rules. When sharing a workbook in another spreadsheet program, check the appearance after conversion; formatting behavior can vary. Before printing, use Print Preview to confirm that the grid, labels, and values remain legible. Very pale fills may disappear on paper, and a color scale that looks distinct on screen may be difficult to interpret in grayscale.

Frequently Asked Questions

Does Excel have a built-in heat map feature?

Excel’s usual heat-map method is Conditional Formatting > Color Scales, rather than a separate feature named Heat Map. The rule colors worksheet cells according to their values, and you can customize the colors and thresholds.

Can I make a heat map with text labels?

A Color Scale is designed to evaluate cell values, especially numbers. Keep labels in adjacent rows or columns. If the cells contain categories or text rather than measurements, use a rule suited to those values or another visualization instead of expecting a numeric color scale to communicate them.

How do I make zero the center of a heat map?

Edit or create a three-color scale and set the midpoint type to Number with a value of zero. Assign the center color to that point. This is useful when negative and positive values have different meanings, such as results below or above a target.

Why do identical values have different colors?

They may belong to different conditional-formatting ranges, each with its own minimum, midpoint, and maximum. Inspect the rules and their Applies to ranges. A shared scale across the intended comparison area is needed if equal numbers should receive the same color.

Can I use an Excel heat map in a printed report?

Yes, but preview the printed page and check that both the colors and the cell values remain readable. Use sufficient contrast, include a note explaining the scale, and do not make color the only way to identify an important result.

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.