Excess mortality and Covid-19 deaths

I wanted to visualize excess mortality in Portugal and its relationship with deaths by Covid-19 during the pandemic.

To calculate excess mortality, it is, of course, necessary to define which reference value we are using. In most cases, the average number of deaths during the period 2015-2019 is used. In Portugal, at a national level, the time series for the daily number of deaths goes back to 2009. There was a noticeable increase in deaths between 2009 and 2019 (due to population aging). I took this evolution into account before calculating the median value for that period (The median is not influenced by extreme values, which seems to be helpful due to winter variability and summer heatwaves).

The chart contains several variables:

• The baseline (median for the period 2009-2019);
• The interquartile range (IIQ, between 25% and 75%), the narrowest variation band, with the darkest tone;
• The band between Q1-IIQx1.5 and Q3+IIQx1.5 (lighter tone) helps to identify extreme values;
• The weekly values in each year;
• Deaths in 2020;
• Deaths in 2021;
• Excess mortality in 2021;
• Deaths by Covid-19 in 2021;
• Three indicators at the top of the chart, with the cumulative excess mortality for the year (absolute and relative values), and the cumulative Covid-19 deaths in 2021.

What strikes me as interesting about this chart is how the shapes commonly used in charts (dots, lines, areas and bars) can be combined to create a representation that is not too complex to read and allows attention to focus on different aspects of mortality. It also makes use of annotations, thus avoiding the need for subtitles.

The chart also demonstrates how flexible Excel is. This is easy to do using programming languages, but it is impossible when using many other tools.