Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Gothard F. wins an autographed copy of Pivot Table Data Crunching for his question: “I would like to find a way to embed the rank number next to the data in a pivot table without having to have the data sorted. I have multiple pieces of data (in separate columns) associated with a company, each having a potentially different rank for that company. I’d like the rank numbers to change for each associated column as the pivot table changes by including/excluding/adding rows (companies). The company data is sorted in alphabetical order.”

Microsoft added the rank feature to Excel 2010 pivot tables. For the purposes of this column, I will first run through how to work with this feature in Excel 2010, and then move on to Excel 2007.

Say that you are starting with the pivot table shown in Figure 1 and you want to add a rank field to the % to Quota column.

Fig. 1

In the pivot table field list, drag the % to Quota field to the Values drop zone a second time. This will duplicate the original % to Quota field, although without any formatting that you applied to the original field. Select one cell in the new % to Quota column as shown in Figure 2.

Fig. 2

The Excel 2010 Pivot Table Options ribbon tab offers a new Calculation group. This group offers settings that were previously buried in the Field Settings dialog box (see Figure 3).

Fig. 3

Open the Show Values As dropdown (see Figure 4). Choose Rank Largest to Smallest. Note that several of the calculations in this dropdown are new in Excel 2010, such as % Running Total In, % of Parent Row Total, and the Rank options.

Fig. 4

It is not relevant in this pivot table since there is only one row field, but Microsoft still asks you to choose which row field should be used to determine the ranks. Click OK (see Figure 5).

Fig. 5

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

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

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

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

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.