One of the many Olympic traditions is how the medal table is sorted. Here’s how to handle Olympic sorting in Excel, as an example of its special sorting features plus some other related tricks because we can’t help ourselves.
Olympic Medal Table sorting
Traditional Olympic medal tables prioritise the number of gold medals won.
If countries have the same number of gold medals, the order is then dictated by which has the most silvers, and finally.
That’s why Japan is on top of this listing during the Tokyo Olympics even thought both China and USA have won more medals overall.
Sorting Olympic-style in Excel
For our examples we’ll use the final medal table from the 2000 Olympics, known as ‘The Best Olympics Ever!’ by our Sydney born-and-raised Editor in Chief.
It’s a simple Excel table with the total medals calculated in Column E.
Of course, you can sort by the pull-down menu at the top of each column but that won’t help with the special Olympic ordering.
For that, go to Data | Sort & Filter | Sort to open up a lot more sorting power.
With this dialog, sorting is possible by levels with the next level used to sort when there are two or more rows of equal value.
Normally, sorting should only be necessary by Gold, then Silver and Bronze. We’ve added to more levels to show that many levels are necessary and it’s good practice to add many levels down to something unique (like country name).
The result is a list sorted in the way the IOC would approve.
But …
That’s OK for a static medal table which won’t change values. That type of Excel sort is a ‘once only’ task. If the values change, the sort will NOT update automatically.
We changed the Silver medal tally for Italy to match Germany. That should put the France row down one, but it doesn’t, unless the user remembers to reapply the sorting.
Changing the medal tally for a past Olympics might seem impossible but these days medals can be withdrawn and awarded to others, years later, if athletes fail drug tests.
One option is to make a button on the worksheet which will reapply the sort as needed. But that requires some VBA coding and a .xlsm file that some users try to avoid.
Automatic updating sort
In Excel 365 for Windows and some other Excel’s the way to ensure data is always sorted correctly is using PowerQuery.
PowerQuery can take an Excel table as a data source then sort the table in any way you like then drop a sorted table back into the same workbook.
The original table becomes the source of data for the rest of the workbook.
Start with a table of the medal data then choose Data | Get & Transform | From Sheet.
That appears in PowerQuery like this.
With PowerQuery you can do more than just sort the table but add calculations, split text and other trickery. We’ll focus on multi-level sorting which is done a little differently than in Excel itself.
In PowerQuery, choose the column to sort by then Home | Sort in whichever order you like.
Then click the next column (next level) and choose Sort again.
Look at the formula line to see PowerQuery building up a Table.Sort command in nested levels.
Now we have a table, properly sorted and will stay sorted correctly even if the source data changes.
But wait, there’s more …
There’s a list of countries, which you can expand on with the Geography Linked Data Type and some calculations
Use Geography Data Type to add more details about each country like flag, population, GDP etc. Use that info to compare the medal tallies against country statistics (medals per million population etc).
PowerQuery doesn’t play nice with Linked Dara Types (an LDT column generates an error in PowerQuery, which can be ignored. Most data from LDT’s are accepted because they are merely numbers or text however some LDT fields like Image (aka flag) generate errors in PowerQuery. A bug that should be fixed so at least PowerQuery can accept the image and load it in the final table.
Another way to do Olympic Ranking
If all that messing about with nested sorting seems like too much hassle, there’s another way.
There’s a simple formula that will create a ranking value for each country with Gold medals valued a lot more than Silver which rate more than Bronze.
An Excel column form looks like this:
= (Gold*40^2) + (Silver * 40) + Bronze
Now each Gold medal has a notional value of 1600, Silver 40 and Bronze 1. The total number doesn’t matter, just the relative placements. Even the value 40 is somewhat random, replace it with any number above say, 20. The brackets aren’t strictly necessary
A custom column in PowerQuery is a little different because ^ isn’t possible for exponents.
([Gold]*Number.Power(40,2) ) + ([Silver]*40) + [Bronze])
Visa stay calculator for European visitors – with Excel
BMI calcs can do more in Excel
Excel’s better Average(), try TrimMean() instead
Excel Subscriptions Manager
