Exceljet

Quick, clean, and to the point

Pivot table rank example

Pivot tables provide a built-in ranking feature, and can rank smallest to largest or largest to smallest. In the example shown, a pivot table is used to group and rank sales by item. The pivot table is also sorted by rank, so that the top sales items appear first.

Fields

The source data contains six fields, but only two fields are used to build the pivot table, Item and Amount:

Pivot table rank example field configuration

Item has been added as a Row field. The Amount field has been added twice as a value field. The first instance of Amount has been renamed "Sales" and and set to sum values:

Pivot table rank example sales field settings

The second instance of Amount has been renamed "Rank". The rank value is created setting "Show values as" to "Rank largest to smallest", with Item set as the Base field:

Pivot table rank example rank field settings

Items are sorted by Rank in Descending order:

Pivot table rank example sort settings

Steps

  1. Create a pivot table, and tick "Add data to data model"
  2. Add Item field to the rows area
  3. Add Amount as a Value field, and rename to "Sales"
  4. Add Amount as a Value field, and rename to "Rank"
  5. Set Rank to "largest to smallest with Item as base field
  6. Sort pivot table by Item in descending order

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.