Show Rank in Excel Pivot Table

A pivot table is a great way to quickly summarize number, to see how your business is doing. But sometimes those numbers are too large to analyze in your head, so let the pivot table give you a bit of help, with the pivot table Rank calculation.

Add Rank to Excel Pivot Table http://blog.contextures.com/

Compare Sorted Fields

Last week, I was looking at my website statistics in a pivot table, and had one field with the number of views per page. Then, I added some revenue data, to see which pages were the most profitable. I could sort by either field, but it was difficult to try and match them up – were the pages with the most views also the most profitable pages? Once the numbers are more than 2 digits, it’s hard to sort them in your head. Well it is for me anyway!

To show you what I mean, I created an example with fake data, for food product sales. If we sort the pivot table to see the products with the highest sales, does that match up with the number of cases sold?

In the screen shot below, the Cases seem to be in a similar order to the Sales $ numbers, but it’s hard to tell for sure, at a quick glance.

showasrank05

Use the Rank Calculation

To make it easier to spot an differences in the order, I added another copy of the Cases field, between the two existing value fields. Then, I changed that field’s Show Values As setting, to use the Rank Largest to Smallest calculation.

showasrank01

In the revised pivot table, it is much easier to spot any differences in the order of Cases versus Sales $. It’s very easy for us to sort the numbers 1-10 in our head!

showasrank04

Watch the Video

To see the steps for adding a field, and changing it to use the Rank calculation, please watch this short video.

For the written instructions, you can go to the pivot table Rank calculation page on my Contextures website.

__________________

Add Rank to Excel Pivot Table http://blog.contextures.com/

You may also like...

1 Response

  1. Rudi says:

    Nice tip :)

    To add even more visual queue to this, you could use conditional formatting to shade the cell where the value above the one being evaluated is larger; for example (in your screenshot): =$D3>$D4. This would be particularly useful if your pivot table is dozens of rows long.

Leave a Reply to Rudi Cancel reply

Your email address will not be published. Required fields are marked *