Back
Excel

Add Rank to PivotTable

image

 

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.

clip_image001

This will essentially assign a value of 1 to each row.

clip_image002

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.

clip_image003

The result will be a new data field that reports the relative rank of each Market.

clip_image004

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.

clip_image005

Right-click on the second instance of data measure and select Show Value As, then Rank Largest to Small Smallest.

clip_image006

Once your ranking is applied, you can adjust the labels and formatting. This will leave you with a clean looking ranking report.

clip_image007