Excel Heatmap Color Scale: How to Apply and Customize

In Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and pick a preset to create a heatmap. Excel colors each cell according to its value within the selected range; you can customize the colors and thresholds through Conditional Formatting Rules Manager.

What an Excel heatmap color scale does

A heatmap is a way to make patterns in a table easier to see by encoding values as colors. In Excel, a color scale is a type of conditional formatting: it changes a cell’s fill color based on the cell’s value, without changing the value itself. The worksheet still contains ordinary numbers that can be sorted, filtered, calculated, and copied.

For example, suppose a table contains monthly sales for several products. A green-yellow-red scale can make larger figures green, middle figures yellow, and smaller figures red. The colors draw attention to relative highs and lows, but they do not explain why a value is high or low, and they do not replace the underlying data.

Excel’s built-in Color Scales gallery includes two-color and three-color options. A two-color scale blends from one endpoint color to another. A three-color scale adds a midpoint color, which is useful when you want low, middle, and high values to read as distinct groups. These are gradients, not fixed categories: cells between the chosen points receive intermediate shades.

Apply a color scale step by step

Select the range of numeric cells to format. If the range has a header, leave the header out so it is not treated as a value.
On the ribbon, choose Home > Conditional Formatting > Color Scales.
Point to a preset to preview it, then select the scale you want. The formatting appears on the selected cells.

For instance, selecting B2:E13 before applying a scale colors that 12-row by four-column block. Excel evaluates the values in the range to determine the scale. If you select multiple columns together, the same overall scale is generally used across the selected block; a cell’s color is not necessarily a comparison only with the other cells in its row.

To change or remove the result, select a cell in the formatted range and use Home > Conditional Formatting > Manage Rules. In the rules manager, set the display to the current selection or the worksheet as needed, select the color-scale rule, and choose Edit Rule or Delete Rule. Check Applies to if the formatting covers too many or too few cells. Excel for Mac has conditional-formatting controls on the Home tab as well, though the dialog layout can differ. Excel for the web supports applying color scales from its Conditional Formatting controls, while some detailed rule-editing options may be easier to access in the desktop app.

Choose colors that match the data

The right palette depends on what the numbers mean. A simple light-to-dark scale can communicate magnitude without implying that one end is good and the other bad. A red-to-green palette may suit a measure where low values are unfavorable and high values are favorable, but that assumption is not appropriate for every dataset. For example, a high defect count is usually undesirable, while a high satisfaction score may be desirable.

Use a two-color scale when you want to show a straightforward progression, such as fewer to more units sold.
Use a three-color scale when the midpoint has meaning, such as values below, near, and above a target.
Keep contrast readable. Very dark fills can make worksheet text hard to read, especially when values are displayed on top.
Consider color vision and printing. Avoid relying only on a red-green distinction. Check that the result remains understandable in grayscale or with a color-vision deficiency.

Color has no universal meaning in a heatmap. Add a clear heading, units, and—when the interpretation is not obvious—a note explaining which direction is favorable. A red cell should not be left to imply risk if red merely represents a larger number.

Set custom colors and thresholds

Presets are quick, but custom rules help when the scale needs to match a target or a reporting standard. Select a cell in the range and open Home > Conditional Formatting > Manage Rules. Select the relevant color-scale rule, choose Edit Rule, and set the colors and the minimum, midpoint (for a three-color scale), and maximum. The exact dialog labels can vary slightly by Excel build.

Each point has a type and a value. Common choices include the lowest or highest value in the range, a number, a percentage, or a percentile. A number is useful for a meaningful fixed boundary: if values are temperatures, for example, you might want a specified temperature to be the midpoint rather than whichever value happens to be in the middle of the data. A percentile uses the distribution’s rank, so it can be more useful when the range contains outliers. A percentage type is a position within the range and should not be confused with a percentile of the data.

Imagine a service table where the acceptable response time is 24 hours. If the data ranges from 2 to 72 hours, a default midpoint based on the range may not land on 24. Setting a numeric midpoint to 24 can make the color transition reflect the operational target. Verify the chosen type and value in the rule dialog: a midpoint of 24 as a number means something different from a midpoint set at the 50th percentile.

Understand how Excel calculates the colors

By default, Excel uses the minimum and maximum values in the applicable range as the scale endpoints. With a three-color scale, it also uses a midpoint, commonly the 50th percentile. Values between points are assigned blended colors. Consequently, a value’s color depends on the scale and the other values included in it, not just on the value alone.

This has an important consequence when comparing tables. If January and February each have their own independently applied scale, the darkest color in each month marks that month’s relative high, even if one month’s actual numbers are much lower. If readers need direct comparisons across months, apply one scale to the combined range or set shared numeric endpoints. Conversely, separate scales can be useful when the aim is to reveal patterns within each table rather than compare absolute amounts between tables.

Blanks and text entries do not act like ordinary numeric values in a color scale. Zeros, however, are numbers and can affect the scale. If zero means missing or not applicable in your dataset, decide whether it should be represented as zero or left blank before applying formatting. Also check for unexpected values, such as a negative number caused by an entry error, because an extreme value can stretch the scale and make most other cells look almost the same color.

Apply different scales by row or category

A frequent source of confusion is selecting a large rectangular range when each row should be judged independently. If you apply one scale to a whole table, a product’s monthly value is compared with values across that whole selected range. A row containing consistently modest figures may then look nearly uniform, even if it has meaningful variation within the row.

To compare values within each row instead, apply a separate color-scale rule to each row, or select the individual row ranges and create the rules separately. For a modest table, that is straightforward. For a large or frequently changing table, maintaining many separate rules can become cumbersome, so consider whether a shared scale better serves the reader. Excel’s ordinary color-scale rule is designed around a selected range; it does not automatically mean normalize every row independently just because the data is arranged in rows.

For category-based comparisons, apply scales to clearly defined groups or use fixed numeric boundaries shared across groups. Keep the group boundaries visible and consistent. Otherwise, a user may read two identically colored cells as equal when the cells actually belong to different scales.

Fix common color-scale problems

Most cells look almost the same: Look for an outlier or a very wide range of values. Set meaningful numeric endpoints or use a percentile-based boundary where appropriate.
The header is colored: Edit the Applies to range so it contains only the data cells, not the labels.
New rows are not formatted: Extend the rule’s Applies to range. If the data is an Excel table, check how the range behaves when rows are added and confirm the rule manager covers the new cells.
The colors seem wrong after sorting or editing: Inspect the values, range, and rule thresholds. The scale may recalculate when the values in its range change, so a cell can change color even if its own value did not.
Two rules seem to conflict: Open Manage Rules and review overlapping rules and their order. Remove obsolete rules or adjust their ranges so the intended formatting is clear.

When a worksheet is shared, document the scale’s meaning and units. Conditional formatting is visual guidance, not data validation: it will not stop someone from entering an implausible value. Use appropriate data-entry checks separately if preventing invalid entries matters.

Color scales compared with other formatting

Color scales are useful for continuous variation, but they are not the only way to highlight data. Data bars show approximate magnitude with bars inside cells; they are often easier to interpret when readers need to compare size while retaining a visible number. Icon sets assign symbols to ranges or thresholds and can emphasize status categories, but they may reduce the sense of gradual change. Highlight Cells Rules can mark values above or below a fixed number, which is better when a specific cutoff matters more than relative ranking.

These options are also available through Home > Conditional Formatting. Choose the method that matches the question. Use a color scale to ask where values sit in a spread, a fixed rule to ask whether a value crosses a limit, and data bars to compare approximate magnitudes. Avoid stacking several visual systems on the same cells unless each adds distinct information; too many colors, icons, and fills can make a table harder to read.

Frequently Asked Questions

How do I make a heatmap in Excel?
Select the numeric range, then choose Home > Conditional Formatting > Color Scales and select a preset. Use Manage Rules if you need to edit its range, colors, or thresholds.

Can I create a heatmap with my own colors?
Yes. Open the color-scale rule through Home > Conditional Formatting > Manage Rules, choose Edit Rule, and select the colors for the minimum, midpoint, and maximum as applicable.

Does Excel compare each cell with its row?
Not automatically. A rule applied to an entire table evaluates the selected range as a whole. Apply separate rules to rows if each row needs its own relative scale.

Why does the same number have a different color?
The color depends on the scale’s range and thresholds. If another value changes the minimum or maximum, or the cell is placed in a different range, the same number can receive a different shade.

Can I use fixed values instead of minimum and maximum?
Yes. Edit the rule and choose Number for the relevant endpoint or midpoint, then enter the numeric boundary. This is useful when colors should correspond to a real target or limit.

Does a color scale change the cell value?
No. It changes the cell’s conditional formatting only. The underlying number remains available for calculations, sorting, and other spreadsheet operations.

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.