Coronavirus Heat Map Excel Model
Originally published: 08/04/2020 20:36
Last version published: 13/04/2020 16:58
Publication number: ELQ-95968-2
View all versions & Certificate

Coronavirus Heat Map Excel Model

Create a heat map to show Corona virus cases

This file takes its inspiration from a heat map graphic in a post on the BBC news website © BBC Heat maps can be a great way to present large amounts of data in a compact yet easily understandable way, in this example for new confirmed Corona virus cases. I wanted to recreate this in Excel.

Firstly, I found and downloaded relevant data from © ECDC [2005-2019] The data was in comma separated variable format, which means that for each row of data (the dates), all the data is in a single column and each field (the countries) is separated by a comma. After opening the file in Excel, it was necessary to use the standard Excel functionality “convert text to columns” to split out the country data into Excel columns. In the accompanying video, I show you how to do this and how to sort the data left to right(!) to get the countries with the largest totals (USA, Spain, Italy…) at the left.

I then took the data for the 16 countries with the largest totals. First, I created a standard line graph which you can see is “too busy” to analyse effectively. So, I then set out to create a heat map, like the one in the BBC post. To do this, it was necessary to copy and then transpose the data, so that the data in columns (the countries) appeared in rows and the columns then represent the dates.

Finally, I used 3 scale conditional formatting and narrow columns to create the heat map. I added a scale, so you can see what each colour represents, and dates at the bottom.

In the download file, you can examine the heat map and the formatting to see how they work. I also recommend that you watch the accompanying explanatory video. Then have a go yourself, either using the data on the website or your own data.
Have fun and stay safe!

This Best Practice includes
1 Excel file

Gary Knott offers you this Best Practice for free!

download for free

Add to bookmarks


Further information

Enable an understanding of heat map graphics which can be better than other graph types for large amounts of data and to teach users how to build their own

Need Excel and basic skills in Excel

Small data sets

4.8 / 5 (5 votes)

please wait...