A heat map shades cells along a color gradient by value — low numbers cool, high numbers hot — turning a wall of numbers into an instant visual. Excel’s built-in Color Scales do it in two clicks, no formula needed.
How it works
The same grid as a heat map — bigger numbers are “hotter.” (Here the shading shows the gradient idea.)
| A | B | C | |
|---|---|---|---|
| 1 | Region | Q1 | Q2 |
| 2 | West | 95 | 40 |
| 3 | East | 60 | 88 |
The formula
This is a built-in feature, not a formula. The setup:
How it works
Color scales map each value onto a gradient:
- Select the block of numbers you want to visualize.
- Home → Conditional Formatting → Color Scales, then choose a 2-color (min→max) or 3-color (min→mid→max) gradient.
- Excel finds the smallest and largest values and colors every cell on the gradient between them — automatically rescaling as the data changes.
- For control over the endpoints, use More Rules to set the min/mid/max by value, percentile, or number.
Related visual rules: Data Bars draw an in-cell bar proportional to the value, and Icon Sets add traffic-light or arrow icons by threshold — both live next to Color Scales under Conditional Formatting.
Try it: interactive demo
Drag a value; watch the heat-map color shift along the gradient.
Variations
Two-color scale
Simplest min→max gradient — pick a 2-color preset.
Data bars instead
In-cell proportional bars: Conditional Formatting → Data Bars.
Fixed endpoints
Use More Rules to pin min/max to specific numbers so colors stay comparable across sheets.
Pitfalls & errors
Outliers wash out the gradient. One huge value pushes everything else to the “cold” end. Set the max by percentile (e.g. 90th) instead of the actual maximum.
Color scales include text/blank cells oddly. Apply the rule only to the numeric range so blanks don’t skew the scale.
Accessibility. Color alone can be hard to read for some viewers — pair with data bars or keep the numbers visible.
Practice workbook
Frequently asked questions
How do I create a heat map in Excel?
How do I stop one big value from washing out the colors?
What's the difference between color scales, data bars, and icon sets?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class