Building a COVID-19 Dashboard - or really any other similar data trend - in Excel can help track and present the data more effectively. In this guide, we're going to show you tips of building a COVID-19 Dashboard in Excel.
Obviously, you first need the data to be displayed on your dashboard. Feel free to use a method like shown in Tracking COVID-19 Data in Excel using Power Query, and utilize Power Query to fetch the data from a source like covidtracking.com.
Tips on building a COVID-19 dashboard
Double-check your numbers before posting
Due to this being data that changes over time, and is updated daily, we would recommend double checking your data set. This can be especially important given the sensitivity of the subject!
Use a common denominator
Including a common denominator, can provide a different perspective for your audience. In this scenario, you can, for instance, use the total number of tests per number of residents, in addition to the total number of tests.
Colors
Colors are obviously very important when creating dashboards. We recommend using either contrasting colors, or shades of the same color to make the chart easier to read.
Elements of a dashboard
Main elements of a dashboard are of course the visualizations. It's much easier to see the difference of chart elements, rather than glancing through a table full of numbers. Although, this is not to say you can't use supporting tables to display raw data as well! Excel is very rich when it comes to visualizations like charts, maps, and drawing tools.
Another important element of a dashboard is of course the data itself. You may have a solid data which contains lots of information. However, raw data is not enough on its own for creating a detailed report. You may need to modify the data by summarizing, consolidating, or filtering the results. Use Excel formulas, Pivot Tables, and other data tools to help your data analyzing process.
Building a COVID-19 Dashboard in Excel
Let’s start to build a COVID-19 Dashboard in Excel with the above items in mind. We are using the data we have exported at Tracking COVID-19 Data in Excel using Power Query. This data contains daily information of COVID-19 cases in the US.
Excel is fetching the data from covidtracking.com using Power Query. Please check the tracking guide as well as our Power Query 101 article if you want to learn more.
The data
Here is a snippet of our data. Date column contains dates from the end of January to the day of this article's publishing.
A dashboard ideally should not display this much raw data. Thus, we're going to want to customize these values a bit. For example, let's take a look at the daily cases, regardless of states.
Country-wide daily progression
Pivot tables are great for displaying several categories at a time. Simply select the data and create a Pivot Table by following Insert > Pivot Table in the ribbon.
Move the date field into the Rows section, and the one you want to summarize into Values. Excel automatically adds the Months field into the Rows section and groups the date values.
Next, add a Pivot Chart. You can find Pivot Chart option in either Insert or Analyze (belongs to the Pivot Table) tabs in ribbon.
A Pivot Chart is a bit different than regular charts. It has a connection with the pivot table and has special field buttons on it. The chart is refreshed when its Pivot Table is refreshed or vice versa. Consider moving this chart and the next ones to a separate worksheet, which is dedicated to the dashboard.
Recovered cases by state
Our next chart will be a pie chart to compare the recovered cases with positive numbers in each state. This time, we aggregate numbers by states, regardless of time.
Our pivot chart has positive and recovered case numbers in the Values section as the first one. However, note that this time he category label. state, is in the Columns section.
Because a pie chart is not designed to display more than one data column, it only shows the first one, “AK”, in our example. The answer for displaying the other states is with the help of a slicer.
A slicer is a fancy filter for Pivot Table and Pivot Charts. You can find Insert Slicer icon in Analyze tab when the corresponding Pivot Table or Pivot Chart is active.
Mark the options you want to slice (filter) and click OK to create one.
A slicer is linked with only corresponding pivot table or chart by default. Use Report Connection dialog to link a slicer to more than one visualization.
Number of deaths by states
Excel has two types of map support:
Our sample has a Bing map which can display 2 numbers at once. Let’s see how you can create it.
Once again, it starts with a Pivot Table. Full state names (state_full) is in Rows section and Values section have positive and death numbers.
If you have Excel 2013 or a never version, you can find the Bing Maps feature in Insert tab of the ribbon.
Once the map is visible, select your data and click the location icon on the map.
By default, a Bing Map displays pie charts per locations (states). You can change visualization type and color in the settings menu.
At this point, the map shows the values for whole period. You can bind a timeline to your Pivot Table to filter dates and see the epidemic numbers in small periods. A timeline is a slicer type which specialized for dates. You can find it under the Analyze tab, next to the slicer button.
Statistics
Adding some numbers as a highlight can make your dashboard look even better. You can see that the numbers rely on daily and total statistic in our example. Excel’s statistical formulas like SUMIFS is your friend for this type of calculations. You can find detailed information on our Tracking COVID-19 Data in Excel using Power Query article.
Layout
The last step is the placement of the charts and filters. Obviously, you can also do this as you create the visualizations. Ideally, you don’t want to place too much into a small area. Conversely, visualizations that are too big can also overpower others. Try to display the most important information that fits in a screen, without too much scrolling.
