Today’s author is Mike Alexander, a Microsoft Excel MVP who shows us how to add a rank to a PivotTable in Excel 2003, 2007, and 2010. For more information, visit www.datapigtechnologies.com.
Adding Rank in Excel 2003/2007
Step 1: Sort your pivot table on the data measure you are trying to rank.
Step 2: Add a Calculated Field that will have the formula =1.
This will essentially assign a value of 1 to each row.
Step 3: Right-click on your newly created calculated field and go into its Value Field Settings dialog box. There, you will select the “Show Values As” tab. Change the setting for “Show Data As” to be “Running Total In”. Because this field is used to rank the Markets, we change the Base Field to be Market.
The result will be a new data field that reports the relative rank of each Market.
Adding Rank in Excel 2010
Start with a pivot table similar to the one shown here.
Notice that the same data measure is shown twice – In this case, SumOfSalesAmount.
Right-click on the second instance of data measure and select Show Value As, then Rank Largest to Small Smallest.
Once your ranking is applied, you can adjust the labels and formatting. This will leave you with a clean looking ranking report.