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 the Conditional Formatting feature. 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:
Select the data you want to include in the heat map. Open the Home tab. Go to Conditional Formatting > Color Scales. Choose one of the displayed options. You can test multiple options by hovering your mouse over them, as Excel will preview how the heat map will look.
How to Add More Rules to the Heat Map
If you want to create a professional-looking Excel spreadsheet, you can add more rules to your heat map. Head to Conditional Formatting > Color Scales and select More Rules. You can choose a new rule from the Select a Rule Type list.
For this example, we’ll pick the Format all cells based on their values option and set Format Style to 3-Color Scale. Now, you can edit the Minimum, Midpoint, and Maximum settings. You can even change the colors to make the map easier to understand. Once you’re done setting the new rules, click OK.
Add a Heat Map to Excel Pivot Table
If you wanted to analyze your data from different perspectives, there’s a chance you’ve already created 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.
Select the cells containing relevant data. Go to Conditional Formatting > Color Scales and pick one of the available options. Again, open the Conditional Formatting menu and click Manage Rules. Excel will display the Conditional Formatting Rules Manager window. Click the Edit Rule button. Check the Selected cells option. Select the cells with relevant data and click OK.
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 great data visualization method that adds value to your reports.
To remove each cell’s value without affecting the visual representation, open the Home tab, select the cells and go to Format > Format Cells.
From the Category menu, select the Custom option. Then, type ;;; (three semicolons) and click OK.
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.
