How to Create a Heat Map in Excel
Heat maps are a great tool to analyze a lot of data at once, as it uses different colors to represent data instead of having to compare every value. If you need a heat map for your job but don’t know where to start, we’re here to help.
In this article, we’ll show you how to create a heat map in Excel and how you can customize it by adding more formatting rules or removing the numbers.
Create a Heat Map With Conditional Formatting in Excel
You can create a heat map in Excel using theConditional Formattingfeature. This method has the advantage that you can change the data, and the heat map will update accordingly. Here’s how you can create a heat map once you’ve gathered all your data:
How to Add More Rules to the Heat Map
If you want tocreate a professional-looking Excel spreadsheet, you can add more rules to your heat map. Head toConditional Formatting > Color Scalesand selectMore Rules. You can choose a new rule from theSelect a Rule Typelist.
For this example, we’ll pick theFormat all cells based on their valuesoption and setFormat Styleto3-Color Scale. Now, you could edit theMinimum,Midpoint, andMaximumsettings. You can even change the colors to make the map easier to understand. Once you’re done setting the new rules, clickOK.
Add a Heat Map to Excel Pivot Table
If you wanted to analyze your data from different perspectives, there’s a chance you’ve alreadycreated a pivot table in Excel. You can still add a heat map to your table by going through the above steps, but if you edit the table, Excel might not apply the conditional formatting rules to the new data, depending on your settings.
However, you can easily fix it and have the pivot table update itself every time you change the data.
How to Remove Numbers From Your Heat Map in Excel
If you want to visualize data without getting into details, you can remove the numbers from your heat map. This is a greatdata visualization method that adds value to your reports.
To remove each cell’s value without affecting the visual representation, open theHometab,select the cells and go toFormat > Format Cells.
From theCategorymenu, select theCustomoption. Then, type;;;(three semicolons) and clickOK.
That’s it. You can now visualize the heat map without any numbers.
Visualize Data With Heat Maps in Excel
Once you get the hang of it, creating a heat map for your tables shouldn’t take too much. Even if you need to modify the data after creating the heat map, you should have no problem keeping the heat map updated using the methods that we’ve presented.
The truth is, that a visual representation, such as a heat map, is easier to grasp than text and numbers. Even if you can’t use a heat map, Excel has plenty of chart and graph types that you can use to showcase data.
When numbers get boring, let these charts and graphs do the talking.
Quality apps that don’t cost anything.
The best features aren’t the ones being advertised.
The fix was buried in one tiny toggle.
I gripped my chair the entire time—and then kept thinking about it when the screen turned off.
You’re not getting the most out of what you pay for iCloud+.
