Excel Heat Maps: Create, Format, and Read Color Scales

In Excel, a heat map is usually a range of cells colored by value using Conditional Formatting. Select the numbers, then choose Home > Conditional Formatting > Color Scales and a preset; Excel applies a gradient so high, low, and middle values are easier to compare at a glance.

What an Excel heat map shows

A heat map uses color to encode the values in a table. In a typical Excel example, darker or warmer colors indicate larger numbers and lighter or cooler colors indicate smaller ones. The colors do not alter the underlying data: they are formatting that updates as values change. You can still select a colored cell and read its actual value in the formula bar or worksheet.

Excel does not have a separate worksheet object called a heat map. Most spreadsheet heat maps are made with Conditional Formatting color scales. The feature works directly on cells, which makes it practical for comparing monthly sales, response times, attendance, inventory levels, survey scores, or a matrix of measurements without building a separate chart.

A heat map is most useful when the layout itself carries meaning. For example, in a table with products as rows and months as columns, a color scale can make seasonal peaks visible while preserving the labels and exact figures. It is less suitable when readers need precise comparisons among many close values, so keep the numbers visible or provide a more exact table alongside the colors.

Create a heat map with a color scale

In Microsoft Excel for Microsoft 365 on Windows, select the numeric cells you want to format, then use Home > Conditional Formatting > Color Scales. Choose a two-color or three-color preset from the gallery. Excel immediately colors the selected range according to its values. Include only the data cells if labels or totals should not influence the scale.

Arrange the data in a clear table, with row and column headings outside the numeric range.
Select the values to compare. For a monthly sales matrix, select the cells containing sales, not the month names, product names, or grand totals.
Choose Home > Conditional Formatting > Color Scales.
Pick a preset that makes both ends of the range distinguishable. A two-color scale emphasizes low-to-high progression; a three-color scale adds a midpoint.
Review the result with the values and labels visible. Change the preset or rule if the colors obscure text or fail to highlight meaningful differences.

On Mac, Conditional Formatting is also available from the Home tab, with Color Scales in its menu. Excel for the web includes conditional formatting and color-scale options as well, although the arrangement of commands and the rule-editing interface can differ from the Windows desktop app. If you cannot find a particular setting, check the version and use the Conditional Formatting controls in the Home tab rather than assuming the feature is unavailable.

For a quick comparison, imagine a range of weekly order counts from 12 to 96. A color scale assigns colors based on the selected values; it does not mean that a particular shade always represents 50 orders. The same number can appear with a different shade in another range because the scale is calculated from that range’s minimum and maximum unless you set fixed thresholds.

Choose the range and scale carefully

What you include in the selected range has a direct effect on how the heat map reads. If you include a total row containing much larger values than the individual entries, the other cells may look nearly alike. If you include separate categories that are measured on very different scales, the largest category may dominate the color range and make smaller categories difficult to distinguish.

Use comparable values together. Apply one scale to values measured in the same units, such as revenue by month or minutes per task.
Keep headings separate. Text labels help people interpret a matrix, but should generally sit outside the formatted numeric cells.
Decide whether totals belong. Keep totals out when the goal is to compare individual entries; include them only if they are meaningful members of the same comparison.
Consider the data shape. A very large outlier can compress the colors for the rest of the cells. A fixed threshold or separate scale for a comparable subgroup may communicate the pattern more clearly.
Check the meaning of direction. High values are not always good. For error counts or delays, a color convention that highlights high values as a warning may be more useful than one that makes them look positive.

A single scale across a table answers, “Which values are high or low overall?” Separate scales for rows or columns answer a different question: “Which values are high or low relative to this row or column?” Excel’s ordinary color-scale rule evaluates the selected range as a whole. To compare each row independently, apply rules to individual row ranges or use a more deliberate setup; do not assume that one rule automatically normalizes every row separately.

Customize colors and thresholds

To adjust a rule, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, select the rule and choose Edit Rule. Depending on the rule and Excel version, the dialog lets you choose a two-color or three-color scale, change colors, and set the minimum, midpoint, and maximum types and values. The dialog may show rules for the current selection or the whole worksheet; check its “Show formatting rules for” setting if the rule you expect is not listed.

A minimum or maximum based on the lowest or highest value adapts when the data changes. A fixed number gives you a stable benchmark, useful when the same target matters across multiple periods. For instance, if 100 units is the target, a fixed threshold can help preserve that interpretation as new data is added. A percent setting locates a point proportionally within the numeric span, while a percentile setting locates it in the distribution of values. Those are not interchangeable: a percentile can be useful when values are unevenly distributed or contain outliers.

Three-color scales add a midpoint color, which can clarify values around a meaningful center such as zero, a target, or a typical score. Choose the midpoint type to match the question. A midpoint at the median emphasizes relative position in the data; a numeric midpoint can mark a real operational threshold. If the data include negative and positive values, a three-color scale centered on zero can make direction visible, provided zero is actually meaningful for that measure.

Color choice matters too. Select colors with enough contrast, and avoid relying on red-versus-green alone because some readers have color-vision differences and colors may render differently on screen or in print. A palette with distinct lightness levels is often easier to interpret. If users need to distinguish exact categories, add labels or use another visual encoding instead of depending on color alone.

Understand how Excel assigns the colors

A color scale maps values to colors over a range. In a basic two-color scale, Excel uses colors at the low and high ends and interpolates between them. A three-color scale also defines a midpoint color. The rule is conditional formatting, so the displayed appearance can recalculate when values in the range change, when the rule’s range changes, or when the rule settings are edited.

By default, the low and high ends are tied to the values in the formatted range. This is why a color is not a universal measurement: the strongest color in one table might represent 40, while in another it might represent 4,000. If the reader must compare separate tables or reporting periods, use consistent fixed thresholds where appropriate, or put the periods in one shared comparison range. Add a note explaining the thresholds when the colors have operational meaning.

Color scales are different from a chart. A chart can show a trend across time or categories and has axes or other visual structure; a heat map retains the worksheet’s grid and labels. It is also different from manually filling cells with colors: conditional formatting is rule-based and can update automatically, while manually applied fills do not inherently respond to changed values.

Common problems and how to fix them

Every cell looks almost the same. The range may contain an outlier, or the values may be close together. Review the minimum and maximum, consider a meaningful fixed threshold, or format comparable groups separately.
A total row controls the colors. Edit the Applies to range in Manage Rules so the total is excluded, or create a separate rule for the totals if they need their own visual treatment.
Blank cells look confusing. Check whether the cells are truly blank or contain formulas returning an empty string. Decide whether missing data should remain uncolored, be labeled as missing, or receive a separate rule; do not let a blank-looking cell imply a genuine low value.
The colors change after adding data. The rule’s Applies to range may not include the new rows or columns. Update the range in Manage Rules, or place the data in an Excel table and confirm the rule extends to new entries.
Labels or numbers are hard to read. Change the palette, font color, or number format, or use a more restrained scale. A colored cell should remain legible at normal zoom and in any printed output.
Multiple rules conflict. Open Manage Rules and inspect the rules applied to those cells. Remove obsolete rules or adjust their ranges and order where relevant; overlapping formatting can make the result unexpected.

Also check the data before changing the colors. Numbers stored as text, error values, inconsistent units, or hidden rows can undermine the comparison. A heat map summarizes what Excel sees in the selected cells; it cannot determine whether the source values are accurate or comparable.

Heat maps versus other Excel options

Use a color scale when the main task is scanning a rectangular range for relative highs and lows while keeping the exact values in place. Use a data bar when comparing magnitudes cell by cell and a bar inside each cell is more intuitive. Use icon sets when the reader needs a small number of status categories, such as below, near, or above a target. These options are also available through Home > Conditional Formatting.

A PivotTable can summarize a larger dataset before formatting, for example by showing total sales by product and month. A chart may be a better choice when the main message is a trend, a ranking, or a relationship between variables. A heat map does not replace those tools; it is a compact way to add a visual layer to a table. Excel also supports geographic filled maps for location-based data, but those are chart features and are not the same as worksheet color scales.

For a larger or frequently updated dashboard, consider whether the heat map should be paired with a clear legend or written thresholds. Excel’s cell color scale does not automatically provide a labeled legend showing the exact value represented by each shade. A small key, explanatory text, and visible numbers can prevent readers from treating a relative color as an absolute rating.

Frequently Asked Questions

Does Excel have a built-in heat map feature?
Excel creates common worksheet heat maps with Conditional Formatting color scales. They color cells based on values; they are not a separate chart type.

Can I make a heat map in Excel without numbers showing?
Yes. You can hide displayed values with number formatting or adjust the font, but retaining the numbers is usually more accessible and lets readers verify the color-based impression. Test the result carefully so the cells do not appear empty or misleading.

Why does the same value have a different color in another table?
Each rule usually scales colors to the values in its own selected range. Use matching fixed thresholds or a shared range if colors need to mean the same thing across tables.

Can a heat map show values above and below zero?
Yes. A three-color scale can use zero as a numeric midpoint, with separate colors for negative and positive values. Make sure the midpoint and palette match the data’s meaning.

Can Excel color each row independently?
A single color-scale rule generally evaluates its applied range as a whole. For row-by-row comparisons, apply separate rules to the rows or use another carefully designed method, then check that new rows are included.

Will the heat map update when I change a number?
Yes, conditional formatting recalculates as values change, as long as the changed cells are within the rule’s Applies to range and the rule remains active.

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.