Add A Rank Number Field To Your Pivot Table


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.

 

 

Ranking Pivot Table_1

 

 

 

 

 

 

 

 

 

 

 

  • Right click your second instance of your data measure
  • Select Show Value As
  • Rank Smallest To Largest

Ranking Pivot Table_2

 

 

 

 

 

 

 

 

 

 

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.

Ranking Pivot Table_3

 

 

 

 

 

 

 

 

 

 

 

 

More Excel Tips

Create Multiple Reports From One Pivot Table

Break Your Pivot Table Filters Into Multiple Columns

Advanced Excel & Power Pivot Training Classes

Become a Data Analysis & Dashboarding Monster!

power-pivot-excel-school-introduction-and-benefits-v4

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts