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.
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.
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.
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!
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.
__________________
