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 http://www.datapigtechnologies.com/.
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.
No comments:
Post a Comment