## Sorting values in Olympic Medal Table style [Quick Tip]

It is Olympic season. Everyone I know is tracking the games and checking their country’s performance. One thing that we notice when looking at medal tally is,

A single Gold medal is worth more than any number of Silver medals. Like wise, a single Silver medal is worth more than any number of Bronze medals.

So, when you look at the ranking of countries, you see countries with single Gold medal higher up than countries with lots of Silver and Bronze medals (but no Gold).

## So how do we sort our data in Olympic medal table style?

It is simpler than it looks. All you have to do is use custom sort feature in Excel.

2. Go to Home > Sort & Filter > Custom Sort
3. Now specify the sort levels and sort orders.
4. Click ok and you are done!

## Using SORTBY() formula to sort the table

Excel 365 introduced a new formula to sort data by multiple-levels using formulas. SORTBY

Assuming your medal data is in the table named medals you can sort with below formula.

``````=SORTBY(medals, medals[Gold],-1, medals[Silver], -1, medals[Bronze],-1,
medals[Team / NOC],1)``````

The -1 parameter tells SORTBY to sort in descending order.

## What if your version of Excel does not have SORTBY

Well, there is a work around. Add an extra column in your data and calculate sort order using a formula, as shown below.

Once you calculate sort order, sort on this column in descending order and you are done.

## Video – Sorting Excel data in Olympic medal table style

Watch this short & fun video to learn the sorting technique outlined in this page.

## Do you use custom sort?

Custom sorting is very useful when you 2-3 levels in your data. For example, sorting all projects by department & % completed or sorting all products by region & sales volume. I use it often to understand how my data is.

## More Quick Tips on Sorting & Filtering

If you find yourself constantly sorting and filtering, then check out below tips. I am sure you will learn something new.

• Sorting:
• Custom sorting in Pivot tables
• Which formula to use to check if a list is sorted?
• Formula 1 style sorting in Excel
• How to round and then sort data in Excel
• Sorting text values using formulas!
• Shuffling a list in random order in Excel
• How to sort across columns (ie change sort orientation)
• Filtering:
• Filtering values using advanced filters
• How to filter odd or even rows only?
• Right click to filter fast…

The post Sorting values in Olympic Medal Table style [Quick Tip] appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.