Adding Rank in Pivot Tables in Excel

5/7/2020

 

1. Add a Rank Column in a Pivot Table for Different Versions

2. Using RANK.EQ and RANK.AVG in Source Data 

3. Add a Separate Rank Column in a Pivot Table

 

 

 

1. Add a Rank Column in a Pivot Table for Different Versions

In Excel 2010 and Above

• Add value field twice in the pivot table for which you want to measure the ranking.

• Select any of the cells from second data column and right click on it.
• Go to “Show Values As”. You have two different options to add ranking “Rank Smallest To Largest” or “Rank Largest To Smallest”, select any one you want to use.

• It will convert the column values into ranks.
• After that just sort your data to analyze the ranking.

This is the easiest method to add ranking to a pivot table if you are using Excel 2010 or earlier version.

​Important: While using this method, ranks will change when you filter pivot table.

In Excel 2007 and Below

• Sort the data in ascending order on which you want to calculate the ranking.

• Now, open calculated field dialog box and enter “=1” in formula input bar.

• This will add a new field in you pivot with value 1 in all cells.

• Select any of the cells in that field & right click.
• Now, select “Value Field” option.
• In value field setting dialog box, go to “Show Values As” tab.
• From the drop-down list, select “Running Total In” and click OK.

• Now, you have a column with rankings in your pivot table.

Alert: Make sure that you have sorted your pivot table in ascending or descending order.

 

 

 

2. Using RANK.EQ and RANK.AVG in Source Data 

The benefit of using RANK.EQ and RANK.AVG is that you don’t have to add anything to your pivot table.

Just follow these simple steps.

• First of all, in your raw data insert a new column and add below formula into it.

=IF(COUNTIF(C$2:C2,C2)>1,””,SUMIFS($E$2:$E$1507,$C$2:$C$1507,C2))

Here's how the formula works:

This formula will add a single total for each category in the column. And, you can use that total to calculate the rank for each category.

  Now, add two more columns at the end of your data and add below formulas in them.

=IF(H2=””,0,RANK.EQ(H2,$H$2:$H$1507,1))

=IF(H=””,0,RANK.AVG(H2,$H$2:$H$1507,1))

Here's how the formula works:

Here, RANK.EQ & RANK.AVG will calculate the ranking for the category in your data dump. We have used both of these functions to get different ranking type.

• Now, create a pivot table using this data dump, just like below. You can use any of the both columns to calculate the ranking.

Important: While using this method, ranking will not change when you filter the items.

 

 

 

3. Add a Separate Rank Column in a Pivot Table

Instead of using formulas or any other method you can also add a manual separate column for ranking. When you want to add ranks for an instance, you can use this method.

Just follow these simple steps.

• Take a pivot table and sort the values in ascending or descending order as you like.

• And, in the next two columns outside the pivot table add below formulas.
  • =RANK.EQ(E4,$E$4:$E$15,1)
  • =RANK.AVG(E4,$E$4:$E$15,1)

• Drag them below and you have the ranking along with pivot table.

Sample File

Download this sample file from here to learn more.