Sandra asks:
“Any way to add a rank to a pivot table”?
DataPig Answers:
In Excel 2010, it’s really easy. In Excel 2007/2003, it’s a bit clunky.
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.
RELATED STUFF
- Creating a Frequency Distribution with a Pivot Table
- Exploding a Dataset using a PivotTable
- Cut the Size of Your Pivot Table Workbooks in Half
- Transposing a Dataset with a PivotTable
- AutoFilter a PivotTable
- Comparing Tables with a PivotTable
- Excel 2010 Week – PivotTable SubTotals
- Avoiding Data Cut-Off in Excel
- Why FIND when you can SEARCH
- Sort by Color in Excel 2003
