VWO Logo
Request Pricing
Dashboard
Like this post?
Read our in-depth guide to Website Heatmap
3 Min Read

How To Create Heatmaps in Excel [4 steps]

Astha Khandelwal
Astha Khandelwal
A marketing enthusiast who holds expertise in writing creative content and has a knack for learning innovative things.

Analysts use heatmaps to analyze the magnitude of an event by visual cues. A data visualization technique, heatmaps are utilized to derive quick interpretations of the intensity of the event and do course-corrections accordingly.

One of the examples of a heatmap can be the visual representation of COVID-19 cases being registered globally. The following map shows the geographic distribution of the cumulative number of COVID-19 cases reported per 100,000 people worldwide. Darker shades of orange denote most affected countries, and the light yellow hues indicate the opposite.

geographic heatmap in excel for the number of COVID-19 cases
Geographic distribution of 14-day cumulative number of reported COVID-19 cases per 100 000 population, worldwide, as of 16 April 2020[1]

There are quite a few efficient ways to generate a heatmap, such as using readily available free heatmap generators or integrated analytical tools. Microsoft Excel is another great option to explore.

Creating a Heatmap in Excel

When using Excel, you can either create a heatmap by manually coloring each cell depending on its value or enter a smart formula/function to do all the taxing work for you. We’d suggest you use the latter method to create a heatmap. 

Let’s consider the dataset extracted from the above represented COVID-19 globally registered cases as an example to learn how to create a heatmap using an Excel function – apply Conditional Formatting.

CountryCasesDeathsConfirmed Cases
US63966430985422943
India1238041410415
UK984761286869002
Brazil28320173621484
Philippines54533493142
Canada28364101018769
Netherlands28153313414539
Germany130450356956928
Australia6458631482
France1062061716749217
Italy1651552164754581
Pakistan65051244214
Spain1776331857975497
Russia2449019821713
Singapore3699102699
Indonesia51364693459
Israel125011306910
Malaysia5072832164
Cumulative number of reported COVID-19 cases of top 18 countries, as of 16 April 2020[1]

Step 1: Enter Data

Enter the necessary data in an Excel sheet. We entered the data above.

dataset for creating heatmap in excel

Step 2: Select the Data

Select the dataset for which you want to generate a heatmap. In this case, it would be B2 through D19.

selecting the data in excel for creating the heatmap

Step 3: Use Conditional Formatting

Go to Home, click on Conditional Formatting and select Color Scales. The color scale offers quite a few options for you to choose from to highlight the data. 

use conditional formatting in excel to create the heatmap

In our case, we’ve used the first option where cells with high values are colored in green and ones with low value in red. 

Note: Use a color scheme that best matches your data interpretation needs. 

Step 4: Select the Color Scale

Once you select a color scale, you’ll see a heatmap as shown below:

select the color scale through conditional formating to create the heatmap

In this color scale, Excel assigns a green color to the cells with the highest values and red to ones with the lowest values. Meanwhile, the remaining values are assigned colors based on the descending value order showing a gradient of different shades falling between green and red. 

While this was just one of the ways to generate a heatmap using Excel, you can get as creative as you want to. Excel also gives you the leverage to drill down and create mapping views of specific data sets as well. However, if you’re planning to create heatmaps to study the performance of your website or particular pages, we’d recommend you to use better, integrated tools than Excel. Use VWO Heatmaps. They not only help you see how visitors engage with your website but also highlight web elements that catch their attention or distract them. To know more VWO Heatmaps, sign up for a free demo session or opt-in for a free trial.


Like this post?
Read our in-depth guide to Website Heatmap
More from VWO on Website Heatmap

Heatmaps for UX: Quantifying Interaction for Better Website Experiences

One of the many questions that User Experience designers want to answer with considerable certainty…

Read More
Shanaz Khan Khongthaw

Shanaz Khan Khongthaw

10 Min Read

How to Choose the Right Heatmap Colors Palette?

The Colorful World of Warm and Cool Heatmap Colors The earliest record of the distinction…

Read More
Shanaz Khan Khongthaw

Shanaz Khan Khongthaw

8 Min Read

What is Heatmap Visualization? When & How to Use?

What is Heatmap Visualization? By definition, heatmap visualization or heatmap data visualization is a method…

Read More
Shanaz Khan Khongthaw

Shanaz Khan Khongthaw

5 Min Read
Shanaz Khan from VWO

Hi, I am Shanaz from the VWO Research Desk.

Join our community of 10,000+ Marketing, Product & UX Folks today & never miss the latest from the world of experience optimization.

A value for this field is required.

Thank you!

Check your inbox for the confirmation mail