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.
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.
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.
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.
Hold on tight as here we go.
Step 1) Select the numbers in your dataset that you want shaded.
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. 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 🔍
Once you have selected any color scale, your data will turn into a heat map like below.
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.
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.
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).
Step 4) For the Minimum, Midpoint, and Maximum values, select the type as Formula.
Step 5) Write the Formulas as:
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.
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.
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.
And here I have made a Pivot Table out of it. Makes the data so clean and sorted (Woo!).
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.
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.
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.
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.
Step 7) In the Edit Formatting Rule dialog box, select the third 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.
With this, the checkbox is added to your sheet.
You can rename/resize this checkbox by double-clicking on it. Like I have named it “Turn into a Heat Map”
Step 2) Link the checkbox to a certain cell, right-click the checkbox > Click 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.
Now as you check the check box, the linked cell will show TRUE and as you uncheck it, it will show FALSE ✅
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.
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,
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.
The heat map also appears and disappears as the button is checked and 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.