How to Create a Heat Map in Excel (Step-by-Step Guide)

Excel is a number-crunching software. It stores, presents, and allows processing numbers as no other spreadsheet software does.

However, numbers might not always be the best choice to present data. Presenting data visually (that the readers can interpret in a second) is way better 📊

And heat maps help you in doing so in big time. I know what you’re going to ask next. What is a heat map? And how and why do you create it in Excel?

All of these questions are answered in the tutorial below. Get your free practice workbook for it here and let’s slide in.

Table of Contents

What is a Heat Map in Excel?

A heat map is a colored picture of your data.

It is an excellent data visualization technique that pictures the magnitude of a sea of data using 2 or 3 color dimensions 🎨

Looking at it here will make more sense.

These are the regional sales of Product A over the year.

Regional yearly sales of A

A glance at this data doesn’t give me a lot of insight about how the sales have been.

But the same data when shaded with colors tells the tale of how this product has performed in all fours regions.

heat map of sales data points

This is what we call a heat map (coloring into data based on the data values)

In a heat map, all numbers are shaded within 2 or 3 different colors based on their values. Smaller numbers are colored in a lighter shade and bigger numbers are colored in a darker shade (or vice versa, you can choose it).

This helps you interpret data merely by looking at it 👀

Why create a Heat Map

Heat maps offer excellent data visualization benefits. Some of the top reasons why heat maps are a hit include the following 👇

  • Helps speedy data interpretation which in turn leads to accurate and informed decision-making without having to spend hours navigating your decision through a sea of numbers.
  • Color variations offer an immediate visual cue of the relative values and of other relationships between data.
  • Better visuals. Who wouldn’t prefer a gradient-shaded spreadsheet that speaks of numbers over a dull one?

Let’s hope that’s enough persuasion to read this blog until the end to learn all the great ways to create heat maps in Excel.

How to create a Heat Map in Excel

There is a long list of Excel charts that you can readily create in Excel. But unfortunately, heat maps are not on the list.

However, you can create it in Excel in other ways.

There’s one basic way to create a heat map in Excel (through conditional formatting) but talking of how you can make it more and more dynamic – the sky is the limit 🎿

Basic Heat Map

This is the dataset that we are going to create a heat map out of. It shows the sale of a product in 4 different regions over 12 months.

Dataset for heat map

Hold on tight as here we go.

Step 1) Select the numbers in your dataset that you want shaded.

Numbers of dataset

Step 2) Go to the Home tab > Styles > Conditional Formatting.

Conditional formatting button

Step 3) From the drop-down menu, hover your cursor over the option for Color Scales. From the color scales open towards the right, and choose whichever color scale you like better.

As you hover your cursor over any color scale, Excel will give you a quick preview of how it looks when applied to the selected data 🔍

Different color scheme

Once you have selected any color scale, your data will turn into a heat map like below.

Excel Heat map with low and high values

Lowest values go red, with mid-ranged values yellowish-orangish and the highest values are highlighted in green color.

That’s straightforward enough.

The cool part: if you later on make any changes to the data, the colors will update automatically since there is conditional formatting running behind it 🏃‍♀️

For example, in the above image, the sales in the North Region for December are 190 and the highest hence shaded in the darkest green.

If I input the sales of North for January as 250, the shading will also change.

Colors change

While this is cool as the data colors update automatically, if the datasets are huge, this might make your workbook go slow.

Heat Map with Custom color scale

If you’re someone who’s into aesthetics, you might not like the pre-made color scales of Excel. And that’s fine.

You can always customize a color scale of your own that pleases your eyes or that better goes with how your brain perceives numbers or with your brand palette 🖌

Here’s how you can do it:

Step 1) Select the data where you want the color scale applied.

Step 2) Go to the Home tab > Styles > Conditional Formatting > New Rule.

New rule of conditional formatting

Step 3) In the Formatting Rule dialog box, make the following selections:

Rule Type: Format All cells based on their values

Format Style: 2-Color Scale or 3-Color Scale (as you like).

Editing rule for color coding

Step 4) For the Minimum, Midpoint, and Maximum values, select the type as Formula.

Step 5) Write the Formulas as:

Click to copy
Click to copy
Click to copy

Excel will automatically pick out the minimum, average, and maximum values from the given dataset and as you make any changes to it since formulas are running at the backend, the color scales will automatically update.

Step 6) Choose the colors for each of the minimum, midpoint, and maximum values as you want.

Editing rule with values

You can also select the value type and numbers and manually insert the value you want to be treated as minimum, midpoint, and maximum 📝

Step 7) Click okay to apply the conditionally formatted color scales to the selected data.

Here’s how it looks.

Color scales

Could have been any color and any number. The control lies with you!

Heat Map in Pivot Table

Pivot Tables are an excellent way to summarize widespread data in Excel. And heat map makes a top-notch data representation technique.

How about we bring these both together? Wow! That’s terrific 🤯

Here we have sales for a range of products over 12 months.

Multiple product sales in many months

And here I have made a Pivot Table out of it. Makes the data so clean and sorted (Woo!).

Pivot table out of data

Learn how to create Pivot Tables in Excel in the blog here.

Once a Pivot Table is created, follow these steps to convert it into a heat map:

Step 1) Select the numbers in the Pivot Table that you want shaded.

Numbers in Pivot Table

Step 2) Go to the Home tab > Styles > Conditional Formatting.

Step 3) From the drop-down menu, hover your cursor over the option for Color Scales and select whichever color scale you like better.

The Pivot Table will turn into a heat map like here.

Pivot tables become heat map

Everything’s good until here, but there’s a problem.

As you add additional rows or columns to it, the conditional formatting will not automatically extend to it.

Color scales do not apply to additional rows/columns

To make sure the conditional formatting rules extend to the new entries to the Pivot Table, follow these steps here 🤿

Step 4) Select any shaded cell from the Pivot Table.

Step 5) Go to the Home Tab > Conditional Formatting > Manage Rules.

Step 6) From the Conditional Formatting Rules Manager, select the rule and click on Edit Rule.

Edit rule from conditional formatting

Step 7) In the Edit Formatting Rule dialog box, select the third option to apply the rule.

Option to apply the rule

This option says, “All cells showing “Sum of Sales” values for “Region” and “Product”.

Step 8) Click Okay to apply the rule change.

Now whenever you add new entries to the Pivot Table, the conditional formatting will apply to them as well.

Dynamic Heat Map in Excel

A heat map in Excel is cool, but it’d be even cooler if it becomes dynamic such that the color scaling on your dataset appears and goes away with a button 🎯

This is easily achievable in Excel with a few steps. Follow these:

Step 1) Go to the Developer tab > Insert > Checkbox.

Insert checkbox

Pro Tip!

If you can’t see the Developer Tab on the Ribbon, you need to activate it from Excel Options following the steps below:

  1. Go to the File tab > Excel Options.
  2. From the pane on the left, select “Customize Ribbon”.
  3. From the windowpane on the right of the Excel options, find and check the box for the Developer tab.
  4. Press okay.
  5. The Developer tab would be added to the Ribbon.

With this, the checkbox is added to your sheet.

Checkbox added

You can rename/resize this checkbox by double-clicking on it. Like I have named it “Turn into a Heat Map”

Checkbox named

Step 2) Link the checkbox to a certain cell, right-click the checkbox > Click Format Control.

Format Control

Step 3) Go to the Control tab > Enter the cell reference for the cell to which you want to link it.

I have linked it to Cell H7 (can be any cell)

Step 4) Press okay.

Format Control box

Now as you check the check box, the linked cell will show TRUE and as you uncheck it, it will show FALSE ✅

How the checkbox works

Step 5) Now set up a conditional formatting rule.

Step 6) Select the data to be turned into a heat map.

Step 7) Go to the Home tab > Styles > Conditional Formatting > Color Scales > More Rules.

More color scales

Step 8) In the New Formatting Rule dialog box, make the following selections:

Rule Type: Format All cells based on their values

Format Style: 2-Color Scale or 3-Color Scale (as you like).

Step 9) For the Minimum, Midpoint, and Maximum values, select the type as Formula.

Step 10) Write the Formulas as,

Click to copy
Click to copy
Click to copy
New formatting Rule

You see we have encoded the IF function for each of the values. These formulas are conditional to work only IF the value in Cell H7 (linked to the checkbox) is TRUE.

The rest of the Formula is the same as we did for customizing the color scales above. The minimum, midpoint, and maximum values will be found by Excel automatically based on the MIN, AVERAGE, and MAX formulas 🚀

Step 11) Set up the color for each minimum, midpoint, and maximum value as you want it to be.

Step 12) Press Okay to apply the rule.

Now toggle around the checkbox and checking and unchecking it.

Checkbox checked

The heat map also appears and disappears as the button is checked and unchecked.

Checkbox unchecked

How cool is it?

Conclusion

The above tutorial is a pack of mind-blowing information.

By now, we have learned how you can create a heat map in Microsoft Excel (the basic way and then by being creative with the custom color scales option).

Also, how can you convert a Pivot table into a heat map? And my favorite part is how you can create a dynamic heat map that can be toggled with a button ✌

Much cool information. If you liked learning about heat maps in Excel, you’ll surely love the data visualization tools explained through the following Spreadsheeto Excel tutorials too. Do give them a read.