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…
7 Responses to “Extract data from PDF to Excel – Step by Step Tutorial”
Dear Chandoo,
Thank you very much for this and it is very helpful.
However, all the Credit Card Statements are now password protected.
Please advise how can we have a workaround for that
Hello sir,
How to check two names are present in the same column ?
Thanks and Regards
Hi, Thank you for the great tip. One problem, when I click on get data >> from file, I don't see the PDF source option. How can I add it?
I tried to add it from Quick Access toolbar >>> Data Tab, but again the PDF option is not listed there.
I am using Office 365
Hi, Thank you for your video. I see you used the composite table, but I when I load my pdf, it does not load any composite table. It has 20 tables and 4 pages for one bank statement. I have about 30 bank statements that I want to combine. Your video would work except that I can't get the composite table and each of the tables I do get or the pages does not have all the info. what to do?
Dear Chandoo,
How do we select multiple amount of tables/pages in one PDF and repeat the same for rest of the PDF;s in the same folder and then extract that data only on power query.
Thank you
Hi, Thank you for your video. I see you used the composite table, but I when I load my pdf, it does not load any composite table. It has 20 tables and 4 pages for one bank statement. I have about 30 bank statements that I want to combine. nice share
One bank statement takes up 20 tables and four pages in this document. I need to consolidate roughly thirty different bank statements that I have. Your video would be useful if I could only get the composite table, which I can't for some reason, and each of the tables or pages that I can get is missing some information.