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