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.
- Select your data
- Go to Home > Sort & Filter > Custom Sort
- Now specify the sort levels and sort orders.
- 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.
Learn more about SORTBY function & other new formulas in Excel 365.
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.
Example file – Olympics Medal Table style sorting in Excel
Please download this Excel file if you want to practice the custom sort or SORTBY() approach.
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.
What about you? Do you use custom sort? What is your experience like? Please share your tips & thoughts using comments.
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…
8 Responses to “Introducing PHD Sparkline Maker – Dead Simple way to Create Excel Sparklines”
This looks like it could be very useful for a project I'm putting together right now, thank you so much. Quick & silly question, how do I copy & paste the sparkline as a picture?
Question answered. For anyone else:
Select chart>Hold Shift key & select Edit/Copy Picture>Paste
[...] more information about PHD Sparkline Maker, please read this article and to learn more about Sparklines, read this article from Microsoft Excel 2010 blog. Also there [...]
Am I right in thinking that the y-axis is set automatically by excel?
That makes it possible to get the column chart not to start at zero.
Andy - yes, it is currently set to 'auto', which defaults to a zero base for positive values, but you can change that by left-clicking the chart, then choosing (in Excel 2007):
"Chart Tools/Layout/Axes/Primary Vertical Axis/More Primary Vertical Axis Options"
PUBLIC SERVICE ANNOUNCEMENT: When manually editing a chart's minimum/maximum axis values, PLEASE be sure there's a valid reason and that doing so won't skew the message shown by the data (e.g. by exaggerating differences). If in doubt, go back and read Tufte. (W.W.T.D.?)
[...] gridlines, axis, legend, titles, labels etc.) and resize it so that it fits nicely in a cell [example]. This is the easiest and cleanest way to get sparklines in earlier versions of excel. However this [...]
thanks for the work creating the template!!!!
looks good