How to Make a Heat Map in Excel (That's Readable)

A heat map in Excel isn't a special chart. It's ordinary cells coloured by their values, using a conditional formatting feature called Color Scales. That makes it quick to set up and easy to update, because the colours change on their own whenever the numbers do.

How to make a heat map in Excel

1. Select the cells with your numbers. Leave out headings and totals.
2. Go to the Home tab.
3. Click Conditional Formatting, then Color Scales.
4. Hover over the options to preview them, then click the one you want.

That's a working heat map. The rest of this guide is about making it read clearly.

Two-colour or three-colour scale?

Scale	How it shades	Use it for
Two-colour	A gradient from one colour to another	Showing more versus less, like visits per day
Three-colour	Low, middle and high each get their own colour	Good, average and poor, like scores against a target

In a three-colour scale, the top colour marks the highest values, the middle colour the middle values, and the bottom colour the lowest. Pick the order that matches meaning: if high numbers are good, put green at the top. If high numbers are bad, like complaints or errors, flip it so high is red.

Customise your heat map colours and range

1. Select the cells and choose Conditional Formatting, then Manage Rules.
2. Select the colour scale rule and click Edit Rule.
3. Choose a 2-Color Scale or 3-Color Scale.
4. Set the Minimum, Midpoint and Maximum. Each can be the lowest or highest value, a number, a percent, a percentile or a formula.
5. Pick the colour for each, then click OK.

The catch: one extreme value can wash out the whole map

By default, Excel spreads the colours between the very lowest and very highest value. If one cell is far higher than the rest, say a single viral day, every other cell looks almost the same colour, and your heat map shows nothing useful. Microsoft's suggestion is to base the scale on percentiles instead. Set the Minimum to a low percentile and the Maximum to a high one, and the extreme values simply take the end colours while everything else gets a spread of shades again.

For three-colour scales, the midpoint sits at the 50th percentile by default, which works well most of the time. Change it to a fixed number when there's a meaningful middle, such as a target.

Why some cells aren't coloured

If a cell contains a formula that returns an error, such as a divide-by-zero error, Excel skips the formatting on that cell, leaving a blank-looking gap in your map. Wrap the formula in IFERROR, for example =IFERROR(A2/B2, ""), and the cell either shows a result or stays empty, and the rest of the map colours as it should.

Make your heat map easier to read

• Hide the numbers for a pure colour grid: use a custom number format of three semicolons. The values are still there in the formula bar.
• Make cells square by matching column width and row height, which looks more like a classic heat map.
• Add a key beside the grid so readers know what the colours mean.
• Use soft colours so text stays readable, and avoid red and green alone if colour-blind readers will see it.

Heat map versus data bars and icon sets

Color Scales sit alongside two other conditional formatting styles, and it's worth knowing when they're better. Data bars put a small bar inside each cell, which is easier to read when you want people to compare exact sizes rather than spot patterns. Icon sets add arrows, flags or traffic lights, which suit a short status column. A heat map shines when you've got a grid, such as days against hours, and you want the hot and cold spots to jump out. You can't sensibly stack all three on the same cells, so pick the one that matches the question you're answering.

Ideas for heat maps

• Website visits by day of week and hour.
• Email results by send day and subject line type.
• Task completion by team member and week.
• Survey scores by question and group.

Heat maps and sharing

Colour scales stay put when you share the workbook, open it in Excel on the web or turn it into a PDF. They don't survive saving the workbook as a static web page, so if you're publishing the table on a website, take a screenshot or build it in your site's own table tool instead.

If you're making heat maps of marketing results, my guide to measuring social media success without an analytics tool suggests which numbers are worth mapping.

My verdict

Select your numbers, choose Conditional Formatting, Color Scales, and you've got a heat map in seconds. The step that makes it useful is fixing the scale: switch to percentiles when one big number drowns everything else out, and wrap error-prone formulas in IFERROR so there are no blank holes. Pick colours that match meaning, and add a key. For presenting the finished thing, see dashboards for marketers.

Frequently asked questions

Does Excel have a heat map chart?

Not as a chart type. You make one with Conditional Formatting and Color Scales on your cells.

How do I change heat map colours in Excel?

Go to Conditional Formatting, Manage Rules, edit the colour scale rule and pick new colours.

Why does my Excel heat map look all one colour?

One extreme value is stretching the scale. Base the minimum and maximum on percentiles instead.

How do I show only colours without numbers?

Use a custom number format of three semicolons to hide the values.

Why are some cells in my heat map blank?

Their formulas return errors. Wrap them in IFERROR.

Can I make a heat map in Excel for Mac?

Yes. Use Home, Conditional Formatting, Color Scales in the same way.