An Excel heat map is a regular number table where cell colors change according to the values. It makes high, low, and unusual patterns visible without building a separate chart.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac when a table of sales, scores, timelines, workloads, or measurements needs color-based pattern spotting.
Apply a Color Scale
This is the fastest heat map method.
- Select only the numeric cells, not the row or column labels.
- Open the Home tab.
- Click Conditional Formatting.
- Hover over Color Scales.
- Choose a two-color or three-color gradient.
Excel colors the lowest values, highest values, and middle values based on the selected range. The formatting updates automatically when values change.
Fix a heat map that looks wrong
Most problems come from mixed data or old rules.
- Check cells that did not color. They may be text or blanks rather than numbers.
- Open Home > Conditional Formatting > Manage Rules.
- Set the top dropdown to This Worksheet so all rules are visible.
- Delete duplicate or conflicting rules that apply to the same range.
- Check for extreme outliers that stretch the scale and make normal values look too similar.
More control
Create a custom color scale
Open Conditional Formatting > Manage Rules > New Rule. Choose Format all cells based on their values, then pick a 2-Color Scale or 3-Color Scale and set your own minimum, midpoint, maximum, and colors.
Use fixed thresholds when colors need meaning
By default, the highest selected value gets the hottest color, even if it is not objectively high. Set Minimum and Maximum to Number values if red should always mean over 90, for example.
Try other visual formats
Data Bars show horizontal bars inside cells. Icon Sets show arrows, lights, or symbols. These can be clearer than color alone, especially in black-and-white printouts.
Google Sheets has a similar tool
In Google Sheets, use Format > Conditional formatting, then switch to Color scale in the side panel.
Sources
- Microsoft Support – Use conditional formatting to highlight information (2025)
- Microsoft Support – Video: Create a heat map with conditional formatting (2024)
- Microsoft Support – Manage conditional formatting rule precedence (2025)