How To Create A Heat Map In Excel

Analysts use heatmaps to analyze the magnitude of an event using visual cues. A data visualization technique, heatmaps are utilized to derive quick interpretations of the intensity of an result and practice form corrections accordingly.

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

There are quite a few efficient means to generate a heatmap, such as using readily available gratis heatmap generators like VWO'southward AI-powered heatmap generator or integrated analytical tools. Microsoft Excel or Google Sheets is another groovy option to explore. Allow'southward dive correct in!

Creating a heatmap in Excel/Google Sheets

When using Excel or Google Sheets, you tin can either create a heatmap by manually coloring each prison cell depending on its value or human activity smartly and enter a formula/function to do all the taxing work for y'all. Nosotros'd suggest you use the latter method to create a heatmap.

Let'due south consider the dataset extracted from the above represented COVID-19 globally registered cases equally an example to learn how to create a heatmap using the function—apply "Conditional Formatting."

Country	Cases	Deaths	Confirmed Cases
US	639664	30985	422943
India	12380	414	10415
Britain	98476	12868	69002
Brazil	28320	1736	21484
Philippines	5453	349	3142
Canada	28364	1010	18769
Netherlands	28153	3134	14539
Germany	130450	3569	56928
Australia	6458	63	1482
France	106206	17167	49217
Italy	165155	21647	54581
Pakistan	6505	124	4214
Espana	177633	18579	75497
Russia	24490	198	21713
Singapore	3699	10	2699
Indonesia	5136	469	3459
Israel	12501	130	6910
Malaysia	5072	83	2164

Epitome source: European Centre for Disease Prevention and Command

Step i: Enter data

Enter the necessary data in a new sheet. We entered the data in a higher place.

Step 2: Select the data

Select the dataset for which you desire 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 provisional formatting

If yous are using Excel, go to "Abode," click on "Conditional Formatting," and select "Color Scales." The color scale offers quite a few options for yous to choose from to highlight the data.

use conditional formatting in excel to create the heatmap

In our instance, we've used the start option where cells with high values are colored in light-green and ones with low values in red.

If you are using Google Sheets, you will find "Conditional Formatting" under the "Format" option in the bill of fare bar.

Then select "Color Scale" and cull the relevant colors for the midpoint, minpoint, and maxpoint.

Note: Employ a colour scheme that best matches your information interpretation needs.

Step 4: Select the color scale

One time you select a color scale, you'll see a heatmap every bit shown below:

select the color scale through conditional formating to create the heatmap

In this color scale, Google Sheets assign a greenish color to the cells with the highest values and red to the ones with the everyman values. Meanwhile, the remaining values are assigned colors based on the descending value club showing a gradient of unlike shades falling between green and red. And there you have it: your beautiful heatmap to analyze visitor experience and get more than conversions.

While this was just 1 of the ways to generate a heatmap using Excel or Google Sheets, y'all can become as creative every bit you want to. It also gives you the leverage to drill downwardly 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, nosotros'd recommend you to use more than robust and integrated tools than Excel, such as VWO heatmaps. They not simply assist you lot see how visitors engage with your website but also highlight web elements that catch their attention or distract them.

VWO'due south costless AI-powered heatmap generator allows you to predict how visitors interact with your web page. It enables you to approximate bottlenecks based on user experience for you to take required optimization measures. You lot tin literally follow your visitors' trails on your webpages and analyze granularly how they interact with each static or dynamic element.

To know more than about how you lot can leverage VWO heatmaps to visualize company behavior and draw valuable insights, sign up for a free demo session from one of VWO's optimization experts or opt-in for a costless trial to give it a spin yourself and assess whether it meets your unique requirements.