How to Create a Heatmap in Excel (Static, Dynamic and Geographic)

What Is a Heatmap?

A heat map visually represents data using colors in a two-dimensional or geographic area.
Each data point corresponds to a color based on its value.
Colors range from cool (e.g., blue or green) to warm (e.g., red or orange), reflecting data magnitude.

1. Creating a Static Heat Map

1.1 Creating a Heat Map with Default Color Scale

Select your dataset.
Go to the Home tab and choose Conditional Formatting.

Selecting Conditional Formatting

Select Color Scales and pick your preferred scale.

You will see that you have got your desired heat map.

1.2 Creating a Heat Map with Custom Color Scale

Follow the previous method.

In the New Formatting Rule dialog, choose Format all cells based on their values.
Select 3-Color Scale and set colors for minimum and maximum values.
Press OK.

You will get the heat map with your selected color scale.

1.3 Creating a Heat Map Without Numbers

Create a heat map as before.
Right-click on the selected cells, choose Format Cells, and select Custom format.

Press OK.

You will get the heat map without numbers.

1.4 Creating a Heat Map with Square Cells

Insert a heat map following the previous method.
Select the Headers.
Go to the Home tab and select Alignment.
Go to select Orientation and select Rotate Text Up.

Select the columns of the heat map.
Go to the Home tab and select Format.
From the Cell Size drop-down list, select Column Width.

Adjust column width to 3.

Select the rows of the heat map and go to the Home tab.
Select the Cells group, click on Format and select Row Height.

Adjust the Row height to 19.5.

Press OK.
You will get your heat map with square cells.

Read More: How to Make a Heatmap in Excel

2. Creating a Dynamic Heat Map

2.1 Create a Dynamic Heat Map using a Pivot Table

Select the data from the Pivot Table.
Go to the Home tab and select Conditional Formatting.
Select Color Scales from the drop-down list and click on More Rules.

In the New Formatting Rule dialog box, select the 3rd option from Apply Rule To. Under Select a Rule Type, select Format all cells based on their value.
Select the 3-Color Scale option under Edit the Rule Description and select colors for Minimum and Maximum value.
Press OK.

You will get your dynamic heat map.
Add new data to the Pivot Table, and it will automatically update the heat map.

2.2 Creating a Dynamic Heat Map with Check Box

Go to the Developer tab and select Insert.
Select Check Box from Form Control.

Click and drag your mouse cursor where you want the Check Box.

We have inserted the Check Box and changed the text.

Right-click on the Check Box and select Format Control.

In the Format Control dialog box select the Control tab.
Choose Unchecked and select a cell for Cell Link.
Click on OK.

Open the Edit Formatting Rule dialog box for the dataset and select Format cells based on their values.
Choose the 3-Color Scale as Format Style.
For Minimum select Formula as Type and enter the following formula as Value:

=IF($B$4=TRUE,MIN(C7:E18),FALSE)

For Midpoint select Formula as Type and insert the following formula as Value:

=IF($B$4=TRUE,AVERAGE(C7:E18),FALSE)

For Maximum select Formula as Type and enter the following formula as Value.

=IF($B$4=TRUE,MAX($C$7:$E$18),FALSE)

Select colors for Minimum, Midpoint, and Maximum >> press OK.

If you check the Check Box you will be able to see the heat map.

If you uncheck the Check Box, the heat map will disappear.

2.2 Creating a Dynamic Heat Map Without Numbers

Select the dynamic heat map with Check Box.
Go to the Home tab and select Conditional Formatting.
Choose New Rule.

The New Formatting Rule dialog box will appear.
Select Use a formula to determine which cells to format and enter the following formula:

=IF($B$4=TRUE,TRUE,FALSE)

Select Format.

The Format Cells dialog box will appear.
Go to the Number tab, select Custom and insert “;;;” as format Type.
Press OK.

Select OK.

You will be able to see the heat map without numbers if you check the Check Box.

If you uncheck the Check Box the heat map will disappear, and you will get the dataset.

2.3 Using the Scroll Bar in a Heat Map

We will use the following dataset for this example. We will use the Scroll bar to show sales data for 3 years at a time.

Go to the Developer tab, select Insert and choose Scroll Bar from Form Control.

Insert the Scroll Bar at your desired position.

Right-click on the Scroll Bar >> select Format Control.

In the Format Control dialog box, go to the Control tab.
Select Minimum value, Maximum value, Incremental change, Page change and select a cell for Cell link.
Press OK.

Select the first cell of the heat map and enter the following formula:

=INDEX(Dataset_2!$C$5:$G$17,ROWS($C$5:C5),'Using Scroll Bar'!$F$6+COLUMNS(Dataset_2!$C$5:C5)-1)

Press Enter and drag the Fill Handle to the right to copy the formula.

Right-click and drag the Fill Handle down to copy the formula to the other cells.

Select Fill without Formatting.

We have the data from the dataset.

Insert a heat map following the previous methods.

You can get a heat map for different years by simply clicking the scroll bar.

3. Creating a Geographic Heat Map

Select the dataset and go to the Insert tab.
Select Maps and choose Filled Map.

We have inserted the geographic heat map and changed the color.

Download Practice Workbook

You can download the practice workbook from here:

Creating Heat Map.xlsx

Frequently Asked Questions

What is a Heat Map Used For?
- A heat map serves various purposes, including:
  - Data analysis
  - Statistics
  - Finance
  - User experience design
Is a Heat Map in Excel a Chart?
- Yes, a heat map in Excel is a two-dimensional chart where values are represented using colors for improved visualization.
Is a Heat Map in Excel Used for Numerical Data?
- Absolutely! Heat maps depict relationships between different values, and they work exclusively with numerical data.

Heatmap in Excel: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!