Visualizing the COVID-19 dataset using Microsoft Excel 3D Maps

February 15, 2020 by Philipp Wagner

In my last post on COVID-19 I have shown how to write the John Hopkins University dataset to a SQL Server database:

This post shows how visualize the data using the 3D Map feature of Microsoft Excel.

Visualizing the COVID-19 data using Excel

So it all starts with getting the data into an Excel Sheet. From the Ribbon Bar you select ...

  • Data -> Get Data -> From Database -> From SQL Server Database

... like this.

COVID Data in SQL Server

In the Dialog Box you then enter the Server and Database. We have used the database name SampleDatabase in my article on writing the dataset to the SQL Server.

Database Connection details in Excel

If you have successfully entered the connection details, then select the sample.Observation table and click the Load Button.

Selecting the Observation table, that contains the data to be displayed

Excel will then load the dataset into a new Sheet it calls Sheet2 initially. The right hand pane will show you the number of rows loaded, and errors if any.

The loaded Observation data in an Excel Sheet

Now from the Ribbon Bar you create a 3D Map by navigating to ...

  • Insert -> 3D Map -> Open 3D Maps

... like this.

Opening the Excel 3D Map Dialog

Microsoft Excel will now automatically load the data from the Sheet into the 3D Map. On the right pane you can see, that it already correctly mapped the columns Country, Lat, Lon and Province. And some data points are already displayed in the map.

Creating the Excel 3D Map

Now let's configure the 3D Map to make use of the data!

  1. In the Ribbon Bar I first select Flat Map to get a 2D projection of the world.
  2. In the Data section of Layer 1 I change the visualization to Bubbles.
  3. In the Size section of Layer 1 I am adding Deaths (Maximum), Recovered (Maximum) and Confirmed (Maximum).
  4. In the Time section I am configuring to use the Timestamp column.
  5. In the Layer Options I am adjusting the color scales, so it better matches colors of official pages.

Once I am done, I am finally configuring the Tooltip to show useful data.

Final 3D Map

If you now press Play Tour from the Ribbon Bar you can see how the distribution behaves over time.

Conclusion

Microsoft Excel makes it easy to build simple visualizations of the COVID-19 dataset, without putting any energy into programming or data shaping. You can jump through any point in time using the 3D Map time slicer and run a tour, to see how the distribution evolves.