Sunday, January 30, 2011

Add the PivotTable Rank


Today's author is Mike Alexander, Microsoft Excel MVP that shows us how to add a rank to a PivotTable in Excel 2003, 2007 and 2010. For more information, visit

Adding rank in Excel 2003/2007

step 1: Sort the PivotTable data to the extent that you are trying to rank.

step 2: Add a calculated field that has the formula = 1.


This essentially assigns a value of 1 for each row.


step 3: Right click on your newly created calculated field and go in the value field settings dialog box. There, select the tab "show values as" change the setting to "show data as" being "In" running total. Because this field is used to classify markets, let's change the database market.


The result is a new data field that indicates the relative rank of each market.


Adding rank in Excel 2010

Start with a pivot table that resembles the one shown here.

Note that the same amount of data is shown twice – In this case, SumOfSalesAmount.


Right click on the second instance of measurement data and select Show as value, and then rank the largest to smallest.


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


View the original article here

No comments: