In Episode #1380, Bill looks at =RANK. Excel 2010 makes it easy to Rank a Column in a Pivot Table. Although...the way that Pivot Tables handle ties is different than how the RANK function handles ties. Watch the podcast and learn a bit about =RANK.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1380: Rank Rank.
Well hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
A couple of questions came up recently about how to rank a PivotTable.
If you are in Excel 2007 or newer you can choose the column and then from the Home tab, Conditional Formatting, Data Bars, Colour Scales and Icon Sets.
Now one thing you want to do is you want to go in after you've applied that and choose Manage Rules and under Manage Rules or Edit the Rule use this setting up here for PivotTables; All cell showing the value field for the row field.
That will help as you change the PivotTable later.
But if we actually want to show a numeric rank there is an easy way to do that in Excel 2010.
So this numeric field here is percentage to quota.
I'm going to take that numeric field from the field list and add it to the values area and we get the exact same values that we just had but I'm going to choose one cell in that new column and I'm going to go to the Options Tab and here this is brand new in Excel 2010; select Show Values.
This used to be hidden deep in the dialog box for each field and they added some great things here such as “Percentage of row total” and “Percentage of column total” but what I want is Rank largest to smallest and within the market field.
That's great and oh bad formatting there let me fix that up.
Select Number Format and go with number.
All right.
Now here's the one thing I noticed; I actually kind of doctored this data set up to make sure that there really was a tie.
I had two different regions at 95% to quota and check this out it's pretty wild how they handled that tie.
They actually have two different values with 3 which is similar to how the =RANK function used to work.
I guess that's not surprising but what is surprising is the next value is then assigned the value of 4.
That's very much unlike the old rank function.
The rank function would have been this formula =RANK(D5,D$5:$D$14) and Ctrl+Enter.
It also assigned the 3 and the 3 but then it's skipped 4 and it went directly to 5.
All right, so just kind of unusual that Microsoft had one way of dealing with the rank function they created a new RANK.AVERAGE function that would assign both of those ties a value of 3.5 but nothing would have assigned it like this.
So the PivotTable team obviously had their own take on how ties should be handled and I don't see any setting that allows me to change that.
I went to PivotTable options to look for something that would say quit doing rank this weird way.
Apparently we're just stuck with it that way.
So very easy way to add ranks but just be aware that those results are going to be different than if someone else is using the =RANK function.
Oh hey, all right I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel
Learn Excel from MrExcel podcast episode 1380: Rank Rank.
Well hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
A couple of questions came up recently about how to rank a PivotTable.
If you are in Excel 2007 or newer you can choose the column and then from the Home tab, Conditional Formatting, Data Bars, Colour Scales and Icon Sets.
Now one thing you want to do is you want to go in after you've applied that and choose Manage Rules and under Manage Rules or Edit the Rule use this setting up here for PivotTables; All cell showing the value field for the row field.
That will help as you change the PivotTable later.
But if we actually want to show a numeric rank there is an easy way to do that in Excel 2010.
So this numeric field here is percentage to quota.
I'm going to take that numeric field from the field list and add it to the values area and we get the exact same values that we just had but I'm going to choose one cell in that new column and I'm going to go to the Options Tab and here this is brand new in Excel 2010; select Show Values.
This used to be hidden deep in the dialog box for each field and they added some great things here such as “Percentage of row total” and “Percentage of column total” but what I want is Rank largest to smallest and within the market field.
That's great and oh bad formatting there let me fix that up.
Select Number Format and go with number.
All right.
Now here's the one thing I noticed; I actually kind of doctored this data set up to make sure that there really was a tie.
I had two different regions at 95% to quota and check this out it's pretty wild how they handled that tie.
They actually have two different values with 3 which is similar to how the =RANK function used to work.
I guess that's not surprising but what is surprising is the next value is then assigned the value of 4.
That's very much unlike the old rank function.
The rank function would have been this formula =RANK(D5,D$5:$D$14) and Ctrl+Enter.
It also assigned the 3 and the 3 but then it's skipped 4 and it went directly to 5.
All right, so just kind of unusual that Microsoft had one way of dealing with the rank function they created a new RANK.AVERAGE function that would assign both of those ties a value of 3.5 but nothing would have assigned it like this.
So the PivotTable team obviously had their own take on how ties should be handled and I don't see any setting that allows me to change that.
I went to PivotTable options to look for something that would say quit doing rank this weird way.
Apparently we're just stuck with it that way.
So very easy way to add ranks but just be aware that those results are going to be different than if someone else is using the =RANK function.
Oh hey, all right I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel
