When you need to sort and rank a field within a large number of data items it can be confusing to determine the ranking of the data you are looking at if the list is too long, or you may want to turn the Pivot Table into hard data for further analysis along with the ranking field.
So, we can get Excel Pivot Table to show ranking which is really useful. Firstly create a Pivot Table as normal and add in your data measure twice as in my example below.
- Right click your second instance of your data measure
- Select Show Value As
- Rank Smallest To Largest
After the ranking has been applied, just adjust the labels and formatting to suit your taste and make your report look a bit better. There, easy ranking of your data fields in a Pivot Table.
Create Multiple Reports From One Pivot Table
Break Your Pivot Table Filters Into Multiple Columns
Aditya says
Hi,
Nice tip.
Does this work only for Excel 2010 and above? I use Excel 2007, I don;t see the option of ‘Show Values As’ option…
Thanks,
Aditya
IronChef76 says
This option is not available in Excel 2007.
Barbara says
Hello
In Excel 2007, add in your field twice, then in the Value Field Settings – Show Value As – Rank Largest To Smallest
Thanks
Barbara
Karma Basnyat says
Hi Barbara,
After ranking, can you create a custom subtotal for the top few and all other? I haven’t been able to find how to do it. For eg. I have 30 clients all ranked with total revenue. But now I want to subtotal the top 20 and then all other in one. So, two subtotals and one grand total. Can you do that?
thanks!
Karma
Karma says
Hi Barbara,
After ranking, can you create a custom subtotal for the top few and all other? I haven’t been able to find how to do it. For eg. I have 30 clients all ranked with total revenue. But now I want to subtotal the top 20 and then all other in one. So, two subtotals and one grand total. Can you do that?
thanks!
Karma
Barbara says
Hi Karma
Have you grouped the Top 20 then all others, this will allow you to then have 2x subtotals and a grand total. Hope this helps.
Barbara
Johnd836 says
I am in agreement with pretty much everything that you mentioned entirely! Excellent website document! dedfcdeekccd
Johnd896 says
Great article post.Really thank you! Fantastic. fdbbfebdbdgb