You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
Very handy, thanks for the tip Mike!
It cannot be sorted in the Excel 2007!
I'm having issues with this...
When I try to add the calculated field, Excel don't add an aditional column to show it, but aditional rows; that is, Excel place the 1's among the Sum of Sales_Amount values.
Curiosly, if I try with Market as column field, Excel correctly do add an aditional row...
I hope you can help me. Excel 2003. Thanks in advance.
I think , it works with Excel 2010 version , I could did it well
...don't work in Office 2007 - explain in detail
Hi, I have an instance where I want to add both a Sales Qty and a Margin $ rank, largest to smallest (which is working perfectly) but then I also need to add a 'Best Rank' (which is the minimum value of these 2 ranks). Is there any way for me to do this inside the Pivot? I am using Excel 2010.
Thanks very much.
Does work in Excel 2007
Sorry - Does NOT work Excel 2007
Trying Excel for Mac 2011...can find a way to rank within pivot table that stays relative to the label field. argh
Thanks Superb One dear
I find buying China wholesale products from Yiwu China is very cheap.