Ranking with Pivot Tables is a hidden feature and is a extremely helpful when performing ad hoc analysis, this tutorial goes through a step by step guide on how to rank with Pivot Tables.

Choosing what to Rank

Ranking can be performed on these areas for a Pivot Table:

  • Page Area
  • Row Area
  • Column Area

The example below goes through a Row Area field.

Locating the Rank Options

With Excel Pivot Tables ranking is known as AutoShow and sorting is known as AutoSort, in this tutorial I will just stick to calling them ranking and sorting.

To get to the options is fairly simple. First you need to open up the PivotTable field options for the field you want to rank, this can be done two different ways:

  • Double clicking on the PivotTable field
    Step 2 - Double Click Field Settings
  • Right click in the area you want to rank then click the Field Settings… option.
    Step 1 - Field Settings

Either way will get you to the PivotTable field dialog box (note the field name in the Name: area)

Step 2 - Pivot Table field options

To get to the rank and sort options click on the Advanced button.

Step 4 - Rank and Sort Options

An Example

In this example I have the Revenue and Loss of a Continent and the Countries in those continents. I want to rank the:

  • The top 2 Countries
  • By Revenue
  • In Descending Order

Here is the data set we will be working with:

Dataset

After creating the Pivot Table the data now looks like this.

Pivot Table

Navigate to the PivotTable field Advanced Options and I have set up the options as seen below.

Example 1 - Rank and Sort

After setting the options (seen above) and clicking the OK button twice the Pivot Table now looks like this.

Pivot Table - Ranked and Sorted

There you go, this is how to show the top 2 countries by revenue in descending order.

You should also take note that the field we have set up the ranking and sorting on is not highlighted blue.

If I wanted to show the bottom 2 countries by revenue in ascending order, the PivotTable field Advanced Options would look like this:

Bottom 2 Example

Click here to download this example as an Excel Workbook

I have gone over only applying the ranking and sorting to a Row Area, you can apply this method to the Page Areas and Column Areas as well - if you would like to see this tutorial expanded on this please let me know.

You can also cascade your ranking and sorting, for example you can have multiple Row, Column or Page Area fields with ranking applied (see the example workbook).

If you have any questions or suggestions please comment.

Enjoy!