COVID-19 Data Insights

From Importing and Modeling Data to Building Interactive Dashboards in Excel

Author: Kagna Em

Date: June 2025

Methodology

DATA SOURCES

​

�This dataset contains historical records from late 2019 to 14 December 2020, reporting the daily number of newly confirmed COVID-19 cases and deaths worldwide. In addition to case and death counts by country, attributes such as country name, country code, and geographical coordinates (longitude and latitude) are essential for enabling geographic visualizations.

​

This analysis is based on three primary resources:

• COVID-19 case data, extracted from the ECDC on 14 June 2015:�https://www.ecdc.europa.eu/en/publications-data/download-todays-data-geographic-distribution-covid-19-cases-worldwide
• Country codes and geographical locations (longitude and latitude)
• A global base map for visual representation

��

COVID-19 DATA SET

The dataset from ECDC was provided in CSV format and contains daily reports of COVID-19 cases and deaths across the world, along with country population, continent, and 14-day cumulative case rates. The records span from late 2019 through the end of 2020, totaling approximately 61,900 entries, as illustrated below:��

GEOGRAPHICAL COORDINATES

An additional table contains the geographical coordinates of 197 countries and territories. It serves as a dimension table in the data model, with GeoID as the primary key used to establish a relationship with the main fact table, covid-data, sourced from ECDC.��

A BLANK WORLD MAP

A world map will be included in the visualization at a later stage.�

CREATING DATA TABLE

The purpose of this exercise is to utilize data modeling and database features in Power Pivot within Microsoft Excel, rather than analyzing the data directly through a basic PivotTable. To achieve this, all relevant datasets must be converted into data tables and properly connected within a structured data model.��

CALENDAR / DATE TABLE

A Date table (named Calenda in this model) is essential for time-series analysis, periodic automation, and convenient date-based reporting. It can be generated using the built-in wizard or created manually using DAX calculations.��

DATA MODELING

Data modeling was introduced in Excel through the Power Pivot feature, transforming Excel from a simple spreadsheet tool into a lightweight relational database. In this exercise, the covid-data table serves as the fact table, while Calendar, CountryGeo, and CountryAndTerritory function as dimension tables within the data model as shown here.��

DAX MEASURES

DAX enables powerful calculations and cross-table lookups within the data model. For example, the covid-data table retrieves Longitude and Latitude values from the CountryAndTerritory table using a common GeoID key. In addition to lookups, DAX is used to compute key metrics such as Total Cases, Total Deaths, Case Rate, and Death Rate, enhancing analytical insights across the dataset.��

PIVOT TABLE FROM DATA MODEL

Once a proper data model is in place, generating PivotTables becomes more convenient and flexible. Columns, rows, and values can be sourced from different tables within the data model, as illustrated here:

VISUALIZATION

There are many ways to visualize this data. In this exercise, the primary focus is on data modeling, DAX calculations using Power Pivot, and generating insights through visualization based on the provided dataset. The figure below demonstrates how the data is structured, analyzed, visualized, and interpreted to support effective storytelling.��

Total COVID-19 Cases Over Time

Observation:

• America had the highest cumulative total cases by 2020 Q4, showing rapid acceleration starting from 2020 Q1.
• Europe surpassed Asia during 2020 Q3–Q4, suggesting a significant wave.
• Asia showed early growth in Q1–Q2 2020, then plateaued or slightly declined in pace.
• Africa and Oceania had relatively low case numbers throughout the period, showing minimal growth.

​

Total COVID-19 Cases Over Time

Insight:

• COVID-19 spread and escalation varied greatly by continent, with the Americas and Europe experiencing the most significant cumulative case growth.
• Asia likely had stronger early containment compared to other continents.
• Africa and Oceania may have had effective containment or underreporting.

​

Global Distribution of COVID-19 Cases Worldwide

Observation:

• Larger green bubbles indicate countries/regions with more cases.
• North America, South America, Europe, and parts of Asia (India, Russia, Iran) show large bubbles, indicating high case volumes.
• Africa and Oceania display smaller bubbles overall.

​

Global Distribution of COVID-19 Cases Worldwide

Insight:

• The pandemic had a broad global impact, but with concentration in densely populated or globally connected countries.
• Bubble sizes suggest either case prevalence or population-adjusted spread, reinforcing disparities in outbreak severity.

​

Total COVID-19 Deaths Over Time

Observation:

• Europe experienced the highest number of deaths by 2020 Q4, with a sharp rise from 2020 Q2 onward.
• Asia and America also showed significant growth in deaths, peaking around Q3, then slightly decreasing for America in Q4.
• Africa and Oceania had low death counts throughout, with minor increases in Q2–Q3.

Total COVID-19 Deaths Over Time

Insights:

• Europe had the most severe mortality impact, possibly due to aging populations, early outbreaks, and overwhelmed health systems.
• The declining trend in America in Q4 could suggest improved medical response or data delays.
• Africa and Oceania consistently show lower death figures—possibly due to lower case counts, stronger early interventions, or reporting limitations.

Global Distribution of COVID-19 Deaths

Observation:

• Europe had the highest deaths by Q4 2020.
• Asia and America peaked in Q3.
• Africa and Oceania remained low.
• Large red bubbles represent high death counts.
• Europe, North America, South America, and parts of Asia (India, Iran, etc.) had substantial COVID-19 deaths.
• Africa and Oceania had smaller, sparser bubbles.

Global Distribution of COVID-19 Deaths

Insights

• Europe's high mortality is visually evident with dense and large bubbles across the region.
• Some developing regions (e.g., in Africa) show fewer deaths, which may reflect either less exposure or limited detection/reporting.
• The visual suggests concentration of global COVID-19 fatalities in wealthier or more densely populated areas, particularly those with aging demographics.

SUMMARY

Key findings include:

• Total Cases (by Q4 2020): America had the highest cumulative cases, with rapid acceleration from Q1 2020. Europe surpassed Asia in Q3-Q4 2020. Asia showed early growth but then plateaued. Africa and Oceania had relatively low case numbers.
• Global Case Distribution: High case volumes were concentrated in North America, South America, Europe, and parts of Asia (India, Russia, Iran). Africa and Oceania showed smaller case numbers.
• Total Deaths (by Q4 2020): Europe experienced the highest number of deaths with a sharp rise from Q2 2020.
• Asia and America also had significant growth in deaths, with America showing a slight decline in Q4.
• Africa and Oceania maintained low death counts.Global Death Distribution: Europe, North America, South America, and parts of Asia (India, Iran) had substantial COVID-19 deaths, visually represented by large red bubbles.
• Africa and Oceania had smaller, sparser bubbles.

SUMMARY

• In summary, the pandemic's spread and impact varied significantly by continent, with concentrations in densely populated and connected regions. Europe experienced the deadliest outcome, while America saw the widest spread. Africa and Oceania generally experienced minimal impact, though the presentation cautions about potential data completeness issues in these regions.

Key Takeaways

• The spread and impact of COVID-19 varied greatly by continent.
• Death and case distribution maps show concentration in densely populated and connected regions.
• Temporal analysis highlights different pandemic waves and control effectiveness.
• Europe saw the deadliest outcome; America had the widest spread.
• Africa and Oceania experienced minimal impact, with caution around data completeness.