Use Excel to get your own automatically updating Medal Table for the 2024 Paris Olympics then make charts and maps too. Our newsletter readers get a working example workbook to try themselves.
Not interested in the Olympic medal count? This article and download are a good, working example of how to automatically download from a web page into Excel, fix up the data with PowerQuery and link the download to multiple tables.
The main trick is to find a web page that has a list of countries and medals won that’s updated often. Then import into Excel where you can make your own charts and maps which automatically update as the Games continue, like this from the end of Day 1.
Count and Sort any way you like
There’s been a lot of talk on social media about how to rank Olympic medal counts. People and broadcasters will generally choose the method that puts their country in a better position! That happens at every Olympics.
Our Excel worksheet can be sorted any way you like. Use the filter buttons at the top of the table to sort by any column.
There are several options to choose from:
- Gold medals, then to break ties Silver then Bronze. That’s the standard Olympic method.
- Count all medals (Gold, Silver, Bronze)
- Medal Score (Gold = 3, Silver = 2, Bronze = 1). The workbook has calculated a Medal Score in PowerQuery (see below).
Find a web page
We found a page on Wikipedia that has a simply formatted table of medals and countries.
The official Olympic site’s table won’t load into Excel but the Wikipedia table appears to be both accurate and updated regularly.
Import into Excel
You could copy that web table into Excel but that would only have the current values without any updates. We need a way to get a copy of that table regularly to keep an Excel spreadsheet up to date.
In modern Excel for Windows (including 365 and 2021) go to Data | Get Data.
Enter the link https://en.wikipedia.org/wiki/2024_Summer_Olympics_medal_table#Medal_table then choose the table to import.
PowerQuery
Now comes the nerdy fun bit … PowerQuery. This is where you can import data and then setup steps to change or tidy up the table to show just what you need.
We’ve added steps to:
- Promote Headers – takes the top table row and makes them column headers.
- Remove blank rows, bottom row (totals), some unwanted columns.
- Rename a column from ‘NOC’ to ‘Country’.
- Add a calculated column for a medal score (Gold = 3, Silver = 2, Bronze = 1).
- Sort the rows by multiple criteria, a la Olympic tradition. Gold then Silver then Bronze.
All these steps are automatically repeated whenever a new version of the table is imported.
Close and Load
When you’re done with PowerQuery ‘Close and Load’ into Excel as a table.
From there you can make other tables, charts and even maps
Gold!
We made another Query to show just the Gold medals. Do that by linking from the existing Query then adding more steps. Right-click on the main query and choose ‘Reference’.
Reference or linking is faster because Excel only imports data once. The alternative would be to make a second complete query including another download of the same web table.
With the new referenced query, just add the additional steps needed to make the new table. In this case, remove the unnecessary columns and remove the rows/countries without Gold medals.
That gives you a simple table which Excel can turn into a chart or map (Insert | Recommended Charts).
Automatic updating
The spreadsheet will update from the web page whenever you click Data | Refresh All. Setup automatic updating by selecting the main query then from Data | Refresh All | Connection Properties, choosing whichever options you like.
We like “Refresh data when opening the file” so the tables and charts are always up to date when the workbook is opened.
Download Bonus!
Download the complete Excel workbook with map and queries.
That gives you a headstart to make your own lists and charts. Or look at the PowerQuery steps to see how the tables where made.
The workbook includes comparisons with population or GDP (Gross Domestic Product) – works in Excel 365 for Windows / Mac only.
This download is available EXCLUSIVELY to Office Watch subscribers. The link to this and other special downloads is in each issue of Office Watch or Office for Mere Mortals.
Join today and you can get all the exclusive downloads with your first free issue.
Olympic Rankings in Excel; Gold, Silver, Bronze
Get the real Olympic logo for Office documents
Paris 2024 Olympics complete calendar in Outlook
Get a Paris 2024 Olympics event times into Outlook calendar
