Quick, clean, and to the point

How to rank with a pivot table

In this video, we'll look at how to rank data in a pivot table.

Here we have data that represents one month of sales for a camping store. You can see the store sells tents, backpacks, headlamps, and so one. In fact, after I summarize the data in a pivot table, I can get an exact list of items sold by adding the Item field to the Rows area.

And, when I add Amount as a value field, I'll get a breakdown of total sales by item. I'm going to rename this column "Sales", and set the number format to Currency.

Now, looking at the pivot table, we can easily see that Tents are the top item by sales, followed by car racks, sleeping bags, backpacks, and finally headlamps.

To make it a little easier to see this, I can sort by Sales in descending order.

Now, what if I want to add an actual rank value to the pivot table?

This is easy to do.

The first step is to add the field you want to rank to the Values area. In this case, I want to rank total sales, so I'll simply add the Amount field again.

Then I'll rename the column to "Rank".

Now I need to change the display to show rank.

This is done in with Value Field settings under Show Values as. In the dropdown, near the bottom, you'll find options to rank values smallest to largest and largest to smallest. In this case, we want largest to smallest.

Now, whenever you rank with a pivot table, you need to set a base field. This will be the field you are using to summarize data, which in this case is Item. You can see Excel has already selected the correct field.

When I save these settings, we have rank in the pivot table.

Notice, the pivot table remains unsorted, but I can easily sort in descending order using the rank value.

To prove this is all working, I'll temporarily a data value for headlamps to a large number. When I refresh, we get a new ranking.

I'll undo that change.

Now, if you change the field used to summarize data, you'll break the rank settings. If I replace item with state, the Rank column shows errors. To fix, just update the base field setting. In this case, I need to change the base field to State.

Finally, notice there is no requirement to keep the original amount field in the pivot. If I remove this field, the ranking still works fine.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.