How to Make a Heatmap in Excel (2 Easy Ways)

This article illustrates how to make a heatmap in excel. A heatmap is a very useful technique to visualize data to show the magnitude of a phenomenon. Assume you have a dataset containing the daily covid-19 cases by country. Now it may not be convenient to compare data while they are in number format. But if you can present the data using colors i.e highlight the higher number of cases in red, lower number of cases in green, and so on. It will be much easier to understand the data in this way.

Heat maps are often used in weather reports. But you can use it to present various types of data. Follow the article to learn how to make a heatmap in excel using your dataset.

Download Excel Workbook of Heat Map

You can download the free heatmap excel template from the download button below.

2 Ways to Make a Heatmap in Excel

Assume you have the following dataset showing the average monthly temperature in Fahrenheit of some cities in the USA. Now you need to make a heatmap so that users can understand the trend of the data by just glancing at the dataset.

Follow the methods below to achieve that by applying conditional formatting in excel.

1. Make a Heatmap with Conditional Formatting

Follow the steps below to make a heatmap using conditional formatting.

📌Steps:

• First, select the entire dataset excluding the labels as shown in the following picture.

• Then select Home >> Conditional Formatting >> Color Scales >> Red – Yellow – Green Color Scale as shown in the picture below.

• Next, the following heatmap will be created.

• After that, select the entire heatmap, right-click on it, and then select Format Cells.

• Next, select the Custom category from the Number tab, type three semicolons (;;;) in the Type field, and click OK.

• Finally, you will be able to make a heatmap without numbers as shown below.

Read More: How to Create a Zip Code Heat Map in Excel (with Useful Steps)

2. Make a Dynamic Heatmap with Scroll Bar

Now follow the steps below to make a dynamic heatmap in excel in case you have a large dataset.

📌Steps:

• First, copy the city names (from the dataset) to a new sheet and format the area only where you want the data to be visible as follows.

• Then select Developer >> Insert >> Scroll Bar (Form Control) and drag the cursor to place it anywhere you like.

• Next, right-click on the scroll bar and select Format Control.

• After that, set the Minimum value to 1, Maximum value to 7, Incremental change to 1, Page Change to 2, enter a cell reference for the Cell link, and then click OK.

• Now apply the following formula in cell C4 and drag it to the last cell of the visible area (H13). Change any formatting if required. (The dataset is in Sheet1)

• Then select the visible values and apply the Conditional Formatting Color Scales as earlier.

• Finally, you can use the scroll bar to show the part of the dataset as required.

How to Make a Geographic Heatmap in Excel

Assume you have the following dataset containing the state-wise total Covid-19 cases in the USA.

Now follow the steps below to create a geographic heat map using that data in excel.

📌 Steps:

• First, click anywhere in the data or select it entirely. Then go to Insert >> Maps >> Filled Map.

• Next, the following geographic map will be created.

• Then, right-click on the data points and select Format Data Series.

• After that, set the Series Color to Diverging (3-color) and change the color sets as required.

• Finally, you will be able to create the following geographical heat map in excel.

How to Create a Risk Heatmap in Excel

You can also create a risk heat map in excel. Follow the steps below to be able to do that.

📌 Steps:

• First, create a table specifying the impact and likelihood labels as shown below.

• Then enter the following formula in cell D6 and drag the Fill Handle icon to fill the entire table

• After that, apply conditional formatting color scales to the values in the table.

• Finally, you will be able to create the following risk heat map in excel.

Things to Remember

• Don’t forget to select the range before applying conditional formatting.
• You must apply the proper references while entering the formulas to avoid errors.

Conclusion

Now you know how to heat map in excel. Do you have any further queries or suggestions? Please let us know in the comment section below.  Stay with us and keep learning.

<< Go Back to Heatmap in Excel | Data Visualisation in Excel | Learn Excel