Heat Map in Excel: How to Create One

How-to basics · Updated November 21, 2026

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 pick a palette. Excel colors each cell according to its value, making higher, lower, or middle values easier to spot. For a more useful result, exclude labels and totals, check the scale’s minimum and maximum, and choose colors that fit the story your data tells.

What an Excel heat map shows

A heat map is a way to represent numbers with color. In Excel, the simplest version is a conditional-formatting color scale applied to a range. The cells remain numeric: the fill color is a visual layer that updates when the underlying values change. A color scale can help reveal clusters, unusually high or low values, and patterns across rows and columns that are hard to notice in a table of figures.

For example, a sales table could have months in columns and product categories in rows. A scale that uses a light shade for lower sales and a dark shade for higher sales can make seasonal peaks visible. The same approach can show attendance by day, response times by team, or scores by question. A heat map does not explain why a value is high or low; it helps you decide where to look more closely.

Excel’s built-in color scales are not the same as a geographic map. They color cells in a worksheet range, not regions on a map. They are also different from a chart: the values stay arranged in the original cells, and color—not bars or plotted points—communicates magnitude.

Prepare the data before adding colors

Good source data matters more than a sophisticated palette. Put comparable numeric measurements in a clean rectangular range, with descriptive row and column headings outside the cells you plan to format. A layout such as months across the top and product names down the side is easy to scan. Select only the measurement cells, not the headings, grand totals, or explanatory notes.

Check that values Excel should compare are stored as numbers rather than text. Imported values can look numeric but be stored as text, in which case Excel may not treat them as expected in a numeric scale. Also decide how to handle missing data. A blank usually should mean “no observation,” not zero; entering zero for missing information can mislead readers by making it appear to be a real low value.

  • Keep units consistent. Do not mix dollars and percentages in one scale.
  • Remove or separately handle subtotal and total rows if they are much larger than individual observations.
  • Review errors and outliers before applying a scale. A single extreme value can affect the colors of every other cell.
  • Include a clear title or note that explains the measure and time period.

Create a basic heat map step by step

In Excel for Microsoft 365 on Windows, follow these steps:

  1. Select the cells containing the values to visualize. For instance, if the monthly figures occupy C2:N12, select that range and leave the row and column labels out.
  2. On the ribbon, open Home > Conditional Formatting > Color Scales.
  3. Choose a two-color or three-color preset. Excel applies the rule immediately, so you can compare the result with the data.
  4. Check the whole range, including its highest and lowest values, to see whether the color pattern is understandable and whether any totals or blanks were included accidentally.

A two-color scale represents a transition from one end of the range to the other. A three-color scale adds a middle color, which can make a central value or midpoint easier to distinguish. The preset names and swatches show the color direction; do not assume that a particular color always means “good.” Decide what the colors mean for your specific measure and communicate that meaning with a label or note if it is not obvious.

On Excel for Mac, the Conditional Formatting and Color Scales commands are also available from the Home tab, although the ribbon and rule dialogs can look slightly different from Windows. Excel for the web offers conditional formatting as well, but its menus and editing controls may differ from the desktop dialogs. If a workbook will be used across platforms, reopen it in the intended version and confirm that the rule and colors display as expected.

Choose a scale that fits the comparison

The default scale uses the values in the selected range to determine the color mapping. That is useful for exploring a dataset, but it does not always make the most meaningful comparison. If one value is far above the others, the remaining cells may look nearly alike. If the numbers are all close together, a full-range scale can make small differences seem dramatic.

Choose the scale based on the question:

  • Low-to-high ranking: Use a single-hue progression when the main point is that some values are smaller and others larger.
  • Below, near, or above a meaningful target: Use a three-color scale with a midpoint representing the target or neutral value, rather than relying automatically on the statistical middle of the range.
  • Positive and negative change: A diverging scale can distinguish negative values from positive ones, with a neutral color around zero when zero is the meaningful boundary.

Color is not a universal sign of performance. Red may mean a poor result in one report and an important high value in another. Use a short legend or a note such as “darker = more cases” when readers might interpret the palette differently. For accessibility and printing, avoid depending only on red-versus-green differences; use lightness contrast and, where possible, labels or other context too.

Customize the minimum, midpoint, and maximum

To change how a scale maps values to colors, select a cell in the formatted range and open Home > Conditional Formatting > Manage Rules. In the rules manager, choose the relevant rule and select Edit Rule. The color-scale rule can define its minimum, midpoint, and maximum using options such as a lowest or highest value, a number, a percentile, or a formula. The available controls depend on the selected scale type.

For example, if a performance measure has a meaningful target, set the midpoint type to Number and enter that target as the midpoint value. This makes the target correspond to the middle color even if it is not the statistical midpoint of the data. If the scale should have fixed bounds—for instance, a percentage range that is always 0 to 100—set the minimum and maximum to those numbers. Fixed bounds make separate periods easier to compare, because the same value keeps the same color from one period to the next.

By contrast, an automatically calculated scale is useful when the goal is to see relative variation within the current selection. Its colors can change when the selected data changes, so the same number might receive a different shade in a later period. Neither approach is inherently better: use automatic bounds for within-range exploration and fixed, justified bounds when consistent comparisons matter. Avoid choosing bounds simply to make the colors look more dramatic.

Read the pattern without overstating it

A heat map is a visual summary, not a statistical test. A dark cell identifies a value’s position on the chosen scale; it does not prove that the difference is important or explain its cause. Look at the actual number, units, sample size, and surrounding context before drawing conclusions. If the values are rates, check that the denominators are comparable. Ten cases out of twenty and ten cases out of ten thousand have the same count but very different rates.

Be cautious when comparing separate heat maps. If each range has its own automatic minimum and maximum, identical colors can represent different numbers. For example, a dark shade might mean 40 in one period and 90 in another. Fixed scale limits and a visible legend help readers make valid comparisons across worksheets or reporting periods.

Another common issue is an outlier compressing the rest of the color range. You can inspect the distribution, use a justified percentile-based bound, or present the outlier separately. Do not silently hide a meaningful observation just because it makes the chart less attractive. If the outlier is an error, correct the source; if it is real, explain how it affects the scale.

Keep the heat map current and readable

Conditional formatting responds when values in its applied range change. If the data grows, however, a rule may not automatically cover every new row or column. Open Home > Conditional Formatting > Manage Rules and inspect the rule’s Applies to range. Extend it when needed, then verify the added cells are included. Excel Tables can make expanding datasets easier to manage, but it is still sensible to check the rule after changing a table’s layout or adding data.

Formatting can also become hard to manage if several rules overlap. In the rules manager, review which rules apply to the selected cells and remove obsolete or duplicate rules. A separate rule might be coloring an entire row or marking exceptions, so deleting rules without checking their purpose can change the worksheet unexpectedly.

Keep labels visible, use a readable font, and avoid overly saturated fills that make numbers difficult to read. If the range is large, freeze headings or use filters to help readers orient themselves. For a report that will be printed, preview it in grayscale or export a sample to PDF: subtle color differences may disappear on paper or on a low-quality display.

Heat maps versus other Excel options

Color scales are a quick way to show relative magnitude in a worksheet matrix. They are often the best choice when readers need to locate patterns while retaining the original table. They are less suitable when the audience needs precise comparisons, a strong time trend, or a single summary statistic.

  • Data bars: Conditional-formatting data bars show approximate magnitude within each cell and can be easier to compare when values are positive, but long bars can compete with the cell’s number.
  • Icon sets: These use symbols to classify values into groups, which may suit status categories better than a continuous gradient.
  • Charts: A line or column chart is usually clearer for a time trend or for comparing a small number of series.
  • PivotTables: A PivotTable can summarize a larger dataset into categories before you apply a color scale to the resulting values.

These options answer different questions. A color scale emphasizes the position of each cell within a range; a chart emphasizes a relationship or trend. You can use both, but make sure they do not imply conflicting comparisons.

Frequently Asked Questions

Can Excel make a heat map automatically?
Yes. Select the numeric range and choose Home > Conditional Formatting > Color Scales. Excel applies a default color scale based on the selected values. You can edit its bounds and colors through Manage Rules.

How do I make a heat map with a fixed scale?
Open Home > Conditional Formatting > Manage Rules, edit the color-scale rule, and set the minimum and maximum to appropriate numbers. Use a fixed scale when values need to be compared consistently across periods or groups.

Why are all the cells nearly the same color?
The values may be tightly clustered, or a very large or small outlier may be stretching the scale. Check the data and the rule’s bounds. You can choose justified numeric limits or a percentile-based threshold, but keep the actual values available so the color does not conceal important detail.

Can I make a heat map from text or categories?
Color Scales are intended to map numeric values to colors. For categories such as “High,” “Medium,” and “Low,” use categorical formatting rules or icon sets instead of treating the labels as a continuous numeric scale.

Will the heat map update when I change a value?
Yes, conditional formatting recalculates its colors when values within the applied range change. If you add rows or columns outside that range, check Manage Rules and update the rule’s Applies to range so the new cells are formatted too.

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.