This week we’ll show you how to quickly rank items from smallest to largest using a feature that already exists in PivotTables. Let’s say you’re analyzing your products or even expenses and need to rank them from the smallest to the largest value, instead of writing your own formulas in calculated fields, you can use the Rank Smallest to Largest feature in PivotTables to assign ranks or position without sorting or re-arranging your list.
In our example we’ve already created the PivotTable. If you would like to learn how to create a PivotTable or learn about other PivotTable topics, download our free 15 Excel PivotTables and PivotCharts Tips & Tricks eBook.
Note: Download the workbook to practice this exercise
Applies To: Microsoft® Excel® 2007, 2010 and 2013
In our example, the PivotTable has been set up with the Product Sales field allocated to the values area twice. In the second column the other Product Sales field has been renamed Rank.
1. Right click on one of the values in the Rank column and select as below.
2. Select Show Values As, and then Rank Smallest to Largest.
3. Select OK.
The items will be quickly ranked without sorting or re-arranging the list. The same approach can be used to rank other items like expenses.
