• Technology
  • CFO.com | US

Spreadsheets: Rank Items in a Pivot Table

Maybe your Final Four college basketball ranking was scrambled this year, but here's how to fix ranked items in a pivot table when you are using Excel 2010, as well as prior versions.

 

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.

Fig. 6

MrExcel 3-6-11 Fig06

 

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.

Fig. 7

MrExcel 3-6-11 Fig07

 

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.

Fig. 8

MrExcel 3-6-11 Fig08

 

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).

 

Fig. 9

MrExcel 3-6-11 Fig09

 

Fig. 10

MrExcel 3-6-11 Fig 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.

Discuss

Your email address will not be published. Required fields are marked *