Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Ranks in a Pivot Table

By using a pivot table, you can summarize your data in an understandable manner. And, that summary can help you to analyze data on category wise, product wise, month wise, etc.

One of the common practices for analyzing data is by ranking them. Ranking categories and products can help you to compare them with each other.

The best part about ranking is you can check what is on the top and what is at the bottom.

If we use ranking in a pivot table we can save our a lot of time and efforts and it will help us in our analysis.

So today, in this post, I'd like to show you 3 different methods to add a rank column in a pivot table.

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

Here I have mentioned different methods for different Excel versions. 

In Excel 2010 and above, we have a pre-defined handy option. But in Excel, 2007 and below we have to use calculated field.

In Excel 2010 and Above

  • Add value field twice in the pivot table for which you want to measure the ranking.
Add Ranks In Pivot Table In Excel 2010 & Above
  • 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.
Add Ranks In Pivot Table in Excel
  • It will convert the column values into ranks.
  • After that just sort your data to analyze the ranking.
Add Ranks In Pivot Table in Excel

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.
Add Ranks In Pivot Table in Excel
  • Now, open calculated field dialog box and enter “=1” in formula input bar.
Add Ranks In Pivot Table in Excel
Add Rank In Pivot Table Excel 2007 & 2010
  • 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.
Add Rank In Pivot Table Excel 2007 & 2010
  • Now, you have a column with rankings in your pivot table.
Add Rank In Pivot Table Excel 2007 & 2010

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 

This method is a little bit tricky, but it works like a charm.

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.
Add Formula In Column To Add Rank In Pivot Table

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.

Add Rank Formulas In Raw Data To Add Rank In Pivot Table
  • 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.
Create Pivot Table To Add Rank In Pivot Table Create Pivot Table

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.
Sort Data To Add Rank In Pivot Table Create Pivot Table
  • 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.
Add Formulas To Add Rank In Pivot Table Create Pivot Table

Sample File

Download this sample file from here to learn more.

Conclusion

Ranking helps to compare items with each other. And, when we use a rank column in our pivot table it helps us to understand data easily.

From all of the above methods, you can choose the best for you. If you ask me, I use Excel 2016 version and ranking in a pivot table is no big deal in this.

I hope this tip will help you to get better insights from your data.

Now tell me one thing. Which method do you like the most out of all 3? Or, is there any other method which you use for ranking? Please share with me in the comment section, I would love to hear from you.

And, please don’t forget to share this tip with your friends.

  • Useful Article. I use the RANK function most often, but when I have to use Pivot tables, I use the ranking feature within Pivot table. While using Rank functions, I use in conjunction with COUNTIF to break ties. Best wishes.

    • Puneet Gogia

      Interesting. Thanks for sharing. 🙂

  • Adolfo

    When using Rank within the PivotTable how we use a second criteria column to break ties?

    • Puneet Gogia

      You can use countifs instead of countif.

  • Cesar Rodriguez

    Hi,
    This solution does not word with my specific problem:
    I have a Pivot with 5 different fields as rows, lets say A, B.C, D & E.
    When I want to show values as RANK, excel request to specify the Base Field, and although I tried with all them (from A to E), it does not work.

    Would you mind to explain how to do it when there are several fields in rows?

    Thanks!!!

  • محمد حلمي

    Thanks
    very good Knowledge

  • Noman

    Hi – Can we apply rank in pivot table on multiple criteria?
    If Yes, please share its detail or any other useful medium to perform it.
    Regards