Coronavirus Heat Map Excel Model

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!