How to Make a Heatmap in Excel (with Free Template)

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 Template 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.

dataset to make a heatmap

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.

select dataset

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

apply conditional formatting color scales

  • Next, the following heatmap will be created.

heatmap with numbers

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

format cells

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

custom format

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

make a heatmap in excel without numbers


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.

format visible area

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

insert form control scroll bar

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

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.

format object

  • 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)
=INDEX(Sheet1!$C$4:$N$13,ROW()-3,$B$4+COLUMNS($C$4:C4)-1)

apply INDEX formula

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

conditional formatting

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

make a dynamic heatmap in excel


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.

dataset for geographic heat map

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.

insert filled map

  • Next, the following geographic map will be created.

basic geographic heat map

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

format data series

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

series color

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

make a geographical heatmap 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.

basic table to make a risk heatmap

  • Then enter the following formula in cell D6 and drag the Fill Handle icon to fill the entire table
=$C6*D$5

complete risk matrix

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

color scale for risk heat map

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

make a risk heatmap 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. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

5 Comments
  1. Hi,, please give me a tip on how to create an amazing payroll format based on 15days payroll period,, Thanks…

  2. Is it possible to create Heatmap based on Cities ? (Not State, Country)

    But based on cities?

    • Reply
      Naimul Hasan Arif Nov 15, 2022 at 10:42 AM

      Definitely possible. You just need to follow the following procedures to do so.
      After selecting the entire dataset, go to the Insert tab. Followingly, click on Filled Map from the Maps option and you will have your desired output.

Leave a reply

ExcelDemy
Logo