No matter how much pivot tables have improved, they still make some silly choices, such as the column title “Sum of % to Quota.” Provided the active cell is still in the newly created column, you can edit the heading in the box shown in Figure 6.
As you can see in Figure 7, Roswell is ranked #1 with 105% of quota. Miami is #2, and so on, down to West Palm Beach at #12.
If you change the choices in the region dropdown in cell B1, the ranks automatically recalculate. In Figure 8, the pivot table is filtered to only Florida cities. Miami is now #1.
Everyone Disagrees with How Excel Handles Ties
People have always argued about how Excel should calculate ranks. If you use the old RANK function, you might see something like this: 1, 2, 2, 4, 5, if there is a tie for second place. This provides no one ranked at 3. Starting in Excel 2010, the new RANK.AVE function would rank the same series as 1, 2.5, 2.5, 4, 5.
Apparently, the pivot table team doesn’t agree with either method. As you can see in Figure 7 (above), Tampa and Columbus are in a two-way tie for 5th with 94%, and Tallahassee is reported as 6th with 92%. Augusta and Fort Myers are in a tie for 7th, followed by Macon at 8th. Unfortunately, there isn’t any way to control how Microsoft does this calculation, so you are stuck with it the way it is.
Dealing with Rank Before Excel 2010
Before Excel 2010, you could add a calculation outside of the pivot table. In Figure 8 (above), the formula for C4 would be =RANK(B4,$B$4:$B$11). Unfortunately, as Gothard alluded to in his question, you will constantly have to edit that formula as new rows are added to the pivot table.
Showing the Rank in the Same Column
The problem with the method in this article is that you will be adding a new column every time you want to add a rank.
Reader Alan K. suggests that a heat map would highlight values with different colors from best to worst. This feature was available back in Excel 2007 and allows the rank to be shown in the same column. Here’s how to work with heat map feature using Excel 2007. Select the values in the pivot table. From the Home tab, select Conditional Formatting, Color Scales, and then the first thumbnail (see Figure 9). The largest values will be in bright green, and the lowest values will be in red (see Figure 10).
CFO contributing editor Bill Jelen is the author of 32 books about Excel, including three editions of Pivot Table Data Crunching. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.