COVID-19 stats in Excel 365 for Mac


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

We’ve made a new version of our COVID-19 statistics workbook that’s compatible with Excel 365 for Mac.

Keep track of COVID-19 statistics using this Excel spreadsheet that shows the latest cases and deaths for countries you choose and populations also added for you.

covid 19 stats in excel 365 for mac microsoft 365 36732 - COVID-19 stats in Excel 365 for Mac

Excel 365 for Windows has a separate download available here.

Excel for Mac data connections

Excel for Mac lacks an important feature which means our Excel for Windows workbook doesn’t behave on Macs.  Excel for Mac has a very limited range of data connections compared to Excel/Windows.  We’ve not been able to find an online and reputable COVID-19 data source that’s also compatible with Excel for Mac.  If you know of one, please let us know.

In the meantime we’ve made a workbook with a fixed data table of COVID-19 statistics as of today.  That’s not ideal, we know that, but decided it was better to provide a Excel for Mac version with the best that Microsoft can currently provide.

 

Geography data type pulls in statistics about locations around the world. Many of those stats should be treated with care but the Population per country should be OK.

Add more countries to the bottom of the table and the map will update automatically. Make sure the name in the Country column is the same on both tabs; ‘COVID-19 Cases’ and ‘Data’.

All the columns are sortable from the pull-down buttons on each heading.

Compatibility

The main workbook download only works with Excel 365 for Mac.

That’s not our choice, it’s Microsoft.

Three key features are only available together in the Windows release of Excel 365; Geography data type, Get & Transform/Power Query and Xlookup()

Excel 365 for Mac version has a fixed data table that does not update automatically.

Excel 365 for Windows users should use this version of the workbook that has an automatically updating data connection.

Download

Download the COVID-19 per country/per person workbook here.

Right-click on the link and choose ‘Save link as …’ or ‘Save Target As ..’ depending on your browser.

It’s a standard .xlsx Excel workbook that works in Excel 365 for Mac only.

The inevitable disclaimer: We’ve checked and rechecked the download for viruses and other nasties. However, you should never take anyone’s word for that and check for yourself.  Always stand when a lady enters the room.

How to use

Open in Excel 365 for Mac.

As usual the ‘External Content’ warning appears because of the Geography data type.  Click Enable Content to proceed.

See COVID-19 Excel workbook – under the hood for more detail on the workbook and it’s features.

Add more countries

To include more countries, type a country in the first column of empty row below the main table.

Make sure the country name is an exact match for the name in the Data list. For example, it’s ‘Netherlands’ not The Netherlands or Holland.

covid 19 stats in excel 365 for mac microsoft 365 36733 - COVID-19 stats in Excel 365 for Mac

Press Enter and Excel will fill in the other cells, extend the table, include the new results in the conditional coloring and Map Chart.

Data source

COVID-19 stats come from a table at Worldometers which seems respected and is updated regularly.

We’ve copied the data as at 15 April 2020 and normalized it in Excel 365 for Windows.  You could manually update the stats for the countries you’re interested in  … that’s not a good solution … don’t blame the messenger!

If the country name in the Data table can’t match with the Geography data type you’ll have to change the country name in the Data table.

Comparison statistics

The 1918 ‘Spanish’ Flu is added below the main table for comparison.  Both the cases and deaths are estimates. The Death estimates, in particular, vary between 17m and 100m.

You could add more comparison values like ‘Spanish’ Flu stats for individual countries.

Or add stats for other influenza outbreaks.

Reading the worksheet

Some things to keep in mind reading the COVID-19 worksheet or really any pandemic statistics.

We chose the % display because many of the published figures for past pandemics come in that form.  There are other ways to ‘slice and dice’ the same calculations, e.g. ‘Cases per million’.

Read the statistics with care. This is an ongoing situation and the stats vary in reliability.

Testing availability impacts the count of COVID-19 cases. Where there’s not enough testing kits available, the cases numbers won’t show the full extent of the disease. Most countries are suffering from a shortage of testing kits with the possible exceptions of South Korea and Germany.

The cause of death might not be accurately recorded as COVID-19 or the government could be hiding the real statistics.  Figures from countries like North Korea and Iran (among many) are improbably low.

Health reporting systems across the globe aren’t always fast, accurate or consistent.  Even major countries like the UK have had trouble marshalling daily updates from across the country.

For a different look at the world situation with ‘per head’ statistics, check out The Washington Post.

Track COVID-19 stats in your own Excel spreadsheet

COVID-19 Excel workbook – under the hood

Coronavirus scams and nasty Word documents

subs profile e1563205311409 - COVID-19 stats in Excel 365 for Mac
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address