Coronavirus Heat Map Excel Model
  • Coronavirus Heat Map Excel Model
  • Coronavirus Heat Map Excel Model
  • Coronavirus Heat Map Excel Model
  • Coronavirus Heat Map Excel Model
  • 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
certified

Coronavirus Heat Map Excel Model

Create a heat map to show Corona virus cases

Description
This file takes its inspiration from a heat map graphic in a post on the BBC news website © BBC
https://www.bbc.com/news/world-51235105. 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 ourworldindata.org © ECDC [2005-2019]
https://ourworldindata.org/coronavirus-source-data. 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 ourworldindata.org 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

Discuss

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

Reviews


keyboard_arrow_leftkeyboard_arrow_right

Any questions on Coronavirus Heat Map Excel Model?

The user community and author are here to help. Go ahead!


4.7 / 5 (3 votes)

please wait...