Make a Heat Map in Excel in Minutes
A heat map in Excel replaces numbers with color, so you can spot high and low values at a glance. You do not need add-ins or macros; the built-in conditional formatting engine handles the job. The steps below work in Excel 2016, 2019, and Microsoft 365, and they assume your data sits in a rectangular range with headers in the first row.
More from this site
Keep reading the latest coverage
Prepare Your Data First
Clean data makes clean maps. Remove blank rows and columns inside the range, confirm numbers are stored as values rather than text, and keep the layout flat. A single header row on top and a single header column on the left gives Excel the anchors it needs for the color scale.
- Select the numeric cells only, leaving row and column headers outside the selection.
- Check for mixed formats or hidden characters that can break the color scale.
- For best results, use a continuous block without merged cells.
Apply a Built-In Color Scale
The fastest route is a preset color scale. With the numeric range selected, go to Home > Conditional Formatting > Color Scales and pick a two-color or three-color ramp. Excel automatically maps the lowest values to one color and the highest to another, with midpoints blending in between.
Choose the Right Ramp
Two-color scales work well for simple high-low comparisons. Three-color scales add a midpoint, which helps when the data clusters at the extremes. Pick diverging palettes (such as red-white-blue) when you want neutral values in the middle and extremes on both sides.
Customize the Colors and Rules
To control which colors represent which values, open Conditional Formatting > Manage Rules, edit the rule, and set the type to Percentile, Number, or Formula. You can lock specific colors to exact thresholds, recolor the mid-point, and even apply different ramps to different columns.
| Setting | What It Controls | When to Use It |
|---|---|---|
| Minimum / Midpoint / Maximum | Value anchors for the color ramp | When you want fixed thresholds, not auto-scaled percentiles |
| Color type (Percentile, Number, Formula) | How Excel interprets the anchor value | When outliers skew the automatic scale |
| Apply to | Exact range the rule covers | When your selection includes blank or text columns |
Use a Formula Rule for Advanced Maps
For more control, build a custom rule with a formula. Home > Conditional Formatting > New Rule > Use a Formula to Determine Which Cells to Format. For example, =A1>90 applies formatting only to cells above 90. This approach lets you highlight exact bands, create two-color rules on the same range, or flag anomalies with a distinct fill.
Read the Heat Map Correctly
A heat map shows relative magnitude, not absolute differences. Adjacent colors suggest similar values; sharp transitions suggest breakpoints. Always note the scale at the right or top of the color bar, and avoid overloading the map with more than three to four color bands unless the story demands it.
Export and Share Without Losing the Colors
When you copy a heat-mapped range to PowerPoint or Word, keep it as a picture or paste with source formatting. Saving as PDF preserves the colors but locks editing. If you need a static snapshot for a report, export the sheet as a PDF or paste the range as an image to prevent accidental edits.
When a Heat Map Is the Wrong Chart
Heat maps shine for dense grids and matrices. They are less effective for time-series lines, single-number KPIs, or data with too many categories, where a bar chart or table may communicate faster. Use a heat map when the goal is pattern spotting across rows and columns, not precise value comparison.