Make your own live Olympics medal tally in Excel

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

Our readers and supporters can now download a free Excel workbook which imports the latest Tokyo Olympics medal tally into Excel.

All you need is a recent Excel for Windows with Get & Transform – Excel 365, Excel 2019 or Excel 2016 for Windows. (Alas, not Excel for Mac).

There’s a simple table of the ‘Top Ten’ including a reminder of when the workbook was last updated.

Switch to another tab to see all the countries.

The workbook gets the latest medal tallies direct from the Tokyo Olympic organisers.

Points of interest …

Some notes on how the workbook was made including practical things that often get overlooked.

PowerQuery helps with a special case, Russia which isn’t officially competing at the Games. The name ‘ROC’ or ‘Russian Olympic Committee’ is used instead. We added a ‘Replaced Value’ step in PowerQuery to change ‘ROC’ to ‘Russia OC’ . Replaced Value is a really handy feature of PowerQuery to fix special cases in the data.

Top 10 medal countries

A separate list of the ‘Top 10’ countries is absurdly easy in PowerQuery, make a new query with the main medals table as the source, change the sorting then use ‘Keep Top Rows’ to show as many as you like.

Last Refreshed

We’ve mentioned this before. Almost any PowerQuery should have a field showing when the query was last updated. Do that by making a separate one field query and the formula.

= #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}})

Explore in Excel!

The workbooks is just the base for lots of nerdy fun exploring the medal tally in many different ways. Go crazy.

There are many extensions and expansions possible. A common one is comparing medal counts with a country’s population. Alas, PowerQuery doesn’t ‘play nice’ with Excel 365’s Linked Data Types (Microsoft developers, please note). A manual listing is possible but an automatically updating list (allowing for new countries and differing names) is a lot more complicated than it should be.

Our readers can download the workbook now from their Exclusive Downloads page — find the link near the bottom of any issue of Office Watch or Office for Mere Mortals.

Not getting our newsletters? Join Office Watch or Office for Mere Mortals right now to get the best independent Office news, great discounts and exclusive downloads.

Excel Subscriptions Manager
Get COVID-19 vaccination stats and more from Excel

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