How to Create a Heat Map in Excel

Heatmaps are a great tool for analyzing lots of data at once because they use different colors to represent data instead of having to compare each value. If you need a heatmap for your work but don’t know where to start, we’re here to help.

In this article, we will 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 heatmap in Excel with Conditional formatting special feature. The advantage of this method is that you can change the data and the heatmap will update accordingly. Here’s how you can create a heatmap after you’ve collected all your data:

1. Select the data you want to include in the heatmap.
2. open that home Tab.
3. Go to Conditional Formatting > Color Scales.
4. Choose one of the options presented. You can test several options by hovering over them as Excel will show you a preview of what the heatmap will look like.

How to add more rules to the heatmap

If you want to create a professional-looking Excel spreadsheet, you can add more rules to your heatmap. Go to Conditional Formatting > Color Scales and select More rules. You can select a new rule from the Choose a rule type list.

For this example we select the Format all cells based on their values option and setting format style to 3 color scale. Now you can edit them minimum, Focusand Maximum the settings. You can even change the colors to make the map easier to understand. When you are finished setting the new rules, click OK.

Add a heat map to the Excel pivot table

If you want to analyze your data from different perspectives, chances are you’ve already created a pivot table in Excel. You can still add a heatmap to your spreadsheet by following the steps above, but when you edit the spreadsheet, Excel may not apply the conditional formatting rules to the new data, depending on your settings.

However, you can easily fix it and update the pivot table every time you change the data.

1. Select the cells with relevant data.
2. Go to Conditional Formatting > Color Scales and select one of the available options.
3. Open the again Conditional formatting menu and click manage rules. Excel displays the Conditional Formatting Rules Manager window.
4. press the edit rule Button.
5. Check the Selected Cells Possibility.
6. Select the cells with relevant data and click on them OK.

How to remove numbers from your heat map in Excel

If you want to visualize data without going into detail, you can remove the numbers from your heatmap. This is a great data visualization technique that adds value to your reports.

To remove the value of each cell without affecting the visual representation, open the home tab, Select the cells and go to Format > Format Cells.

Of the category menu select the custom Possibility. Then enter ;;; (three semicolons) and click OK.

That’s it. You can now view the heatmap without numbers.

Visualize data with heatmaps in Excel

Once you get the hang of it, creating a heatmap for your tables shouldn’t take too much time. Even if you need to change the data after creating the heatmap, you should have no trouble keeping the heatmap up-to-date using the methods we’ve introduced.

The truth is that a visual representation, such as a heat map, is easier to understand than text and numbers. Even if you can’t use a heatmap, Excel offers numerous chart and graph types that you can use to present data.