Excel Heat Map Conditional Formatting: Setup and Tips

How-to basics · Updated November 19, 2026

In Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and select a palette; Excel immediately shades values from lower to higher. A color scale is a conditional-formatting heat map: it displays patterns in the numbers without changing the underlying values.

How Excel color scales work

A color scale assigns colors according to each cell’s value within the selected range. In a common two-color scale, the lowest value gets one color and the highest gets another, with intermediate values shaded along a gradient. A three-color scale adds a midpoint color, which can make low, middle, and high values easier to distinguish.

By default, Excel bases the gradient on the range’s current values. If the smallest number is 10 and the largest is 90, those values anchor the scale; a value between them receives an intermediate shade. Add a new high value later and Excel may recalculate the scale, changing the appearance of existing cells. This behavior is useful for quickly spotting relative patterns, but it means a color does not necessarily represent a permanent threshold.

Color scales are visual cues, not calculations or rankings. They do not sort the data, replace the values, or establish that a particular value is good or bad. The meaning depends on the colors and the data: dark red could indicate high risk in one table and high sales in another. Add a clear heading or legend when readers may not know how to interpret the colors.

Apply a heat map to a range

For a quick heat map, use a rectangular range of numeric values. In Excel for Microsoft 365 on Windows:

  1. Select the cells to format, such as B2:F20. Include the values, but usually leave column headings and row labels out.
  2. On the ribbon, select Home > Conditional Formatting > Color Scales.
  3. Choose a two-color or three-color preset. Excel applies the scale to the selection immediately.
  4. Review the result against the numbers and the meaning of the data. If the gradient exaggerates small differences or hides important ones, adjust the scale or use fixed thresholds.

The menu offers several color combinations. A standard red-yellow-green scale is easy to recognize, but it is not automatically the right choice: readers may interpret green as favorable and red as unfavorable, which can mislead when high values are not necessarily good. A single-hue light-to-dark scale is often a more neutral choice for counts, quantities, or intensity.

Excel for Mac has conditional-formatting color scales under Home > Conditional Formatting, though dialog layouts can differ by release. Excel for the web also supports color-scale formatting from the Home tab. If you need to set exact scale boundaries or inspect complicated rule settings, use the desktop application because the available editing controls can vary in the web version.

Choose a scale that fits the data

Use a two-color scale when the main message is simply lower versus higher. It works well for a table of monthly quantities, for example, where the important task is finding unusually small and large values. Use a three-color scale when a meaningful middle point matters, such as values below, near, and above a target. The midpoint is not necessarily the arithmetic average; you can define it to represent a business or measurement threshold.

  • Choose colors with enough contrast. Very pale shades may disappear on a white background, while very dark fills can make cell text hard to read.
  • Consider color-vision accessibility. A red-green gradient can be difficult for some readers to distinguish. A blue-white gradient or another clearly separated palette may communicate intensity more reliably.
  • Keep comparisons consistent. If two tables are meant to be compared, matching their minimum and maximum settings helps ensure that the same color means the same value.
  • Check outliers. One unusually large value can stretch an automatic scale and make most other cells look almost identical.

Automatic scales are convenient when the goal is to see relative highs and lows inside one selection. Fixed values are better when colors need stable meaning across periods or sheets. For example, if a service-level target is 80, a fixed midpoint at 80 can make the color transition meaningful even when the selected data changes.

Read a concrete example

Suppose B2:F13 contains monthly sales for five products, with each row representing a product and each column a month. Applying a color scale to the whole range highlights the smallest and largest monthly values across all products and months. This is useful for finding overall peaks, but it does not answer which month is unusually high for a particular product: a product with consistently large sales may dominate the scale.

If the goal is to compare months within each product, apply a separate scale to each row instead. Select B2:F2, apply a color scale, and repeat for the remaining rows, or create separate rules for the row ranges. Each row then has its own low-to-high gradient. This reveals within-product variation, but identical colors no longer guarantee identical sales amounts across different products. The right scope depends on the question being asked.

For a year-over-year comparison, a fixed scale can be more useful. Imagine that values are percentages and zero represents no change. A three-color scale with a negative minimum, zero midpoint, and positive maximum can distinguish declines from growth. If each year’s range is set to its own automatic minimum and maximum, a small decline in one year might look as strong as a much larger decline in another. Setting consistent boundaries avoids that false comparison.

Set thresholds and edit a rule

To control the scale rather than accept the preset, select the cells and choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose Format all cells based on their values, then select a two-color or three-color scale. Set the colors and the Type and Value for the minimum, midpoint if present, and maximum. Available types include options such as lowest or highest value, number, percent, and formula, depending on the scale position.

A Number boundary is an actual numeric cutoff. Percent is a position in the selected range: for example, 50 percent represents the halfway point between the minimum and maximum, not necessarily the statistical median. Percentile is different: it refers to a position in the distribution of values. This distinction matters when values are unevenly distributed. For example, the halfway point between 0 and 100 is 50, even if most values cluster near 10; the 50th percentile would reflect the middle of the observed distribution.

To inspect or change existing formatting, select a cell in the range and choose Home > Conditional Formatting > Manage Rules. Check the Show formatting rules for setting so the relevant worksheet rules appear. You can edit a rule, change its Applies to range, or remove it. If several rules overlap, review their order and settings; a rule intended for a different range may be affecting the appearance you see.

Common problems and what to check

  • The colors look almost identical. Check for an outlier, then consider fixed boundaries or a separate scale for each row or category. A wide range can compress the visible differences among ordinary values.
  • Blank or text cells do not shade as expected. Color scales are intended for numeric values. Check whether numbers were imported as text, and convert them to real numbers if appropriate. Do not assume a blank cell means zero; it may represent missing data.
  • The scale changes after adding data. Automatic minimum and maximum values can change when the rule’s range or its values change. Use fixed numeric boundaries when the same colors must retain the same meaning over time.
  • Formatting appears on the wrong cells. Open Manage Rules and inspect Applies to. A copied rule can cover a larger or different range than intended.
  • The text is difficult to read. Choose lighter fills, adjust the font color, or use a simpler palette. Check the result in grayscale or on a printed page if people will use a printout.

A common misconception is that the most intense color always identifies a statistically unusual value. A standard color scale only maps values to colors; it does not test significance, account for seasonality, or determine whether a result is abnormal. For a meaningful alert, define the threshold from the context of the data rather than relying on the gradient alone.

Heat maps compared with other formatting

Color scales are best for continuous numeric comparisons: they show gradation rather than dividing values into a few categories. Data bars, available through Home > Conditional Formatting > Data Bars, make approximate magnitudes visible inside cells while retaining a bar-length cue. Icon sets group values into visual categories such as arrows or traffic lights. Highlight Cells Rules can mark values above, below, or equal to a specified threshold.

For a custom rule that highlights each row’s maximum while ignoring blank cells, select A2:F10 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Assuming the data is in A2:F10, enter =AND(ISNUMBER(A2),A2=MAX($A2:$F2)). If row 2 contains 12, 19, 16, 19, a blank, and 7, the rule highlights both cells containing 19; tied maximum values are both highlighted. This is a threshold-style highlight, not a heat map, but it can complement one when readers need the exact peak called out.

Choose the format that matches the task. A color scale helps reveal gradual patterns; a formula rule or Highlight Cells Rule is clearer when the question is whether a value crosses a defined boundary. You can combine rules, but keep the visual result restrained so users can tell which formatting conveys the primary message.

Frequently Asked Questions

Can I make an Excel heat map without a formula?
Yes. Select the numeric range and choose Home > Conditional Formatting > Color Scales. Excel applies a built-in scale without a worksheet formula.

Can I use different heat maps for each row?
Yes. Apply separate rules to each row, or create rules for the individual row ranges. This compares values within each row, but colors may represent different numeric values from one row to another.

Can I make the same color mean the same number everywhere?
Yes. Edit the color-scale rule and set fixed numeric minimum, midpoint, and maximum values. Apply consistent settings to each range you want to compare.

Why did my heat map change when I added a value?
The rule may use automatic lowest and highest values. New data can change those endpoints and therefore the colors assigned to existing cells. Set fixed boundaries if the scale should remain stable.

Can a heat map show missing data as zero?
A color scale does not automatically make blanks mean zero. If zero is the intended meaning, enter or calculate zero values deliberately; otherwise, keep missing data distinct so the display does not imply a measurement that was never recorded.

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.