How do I add a rank number field to my pivot table?

Problem

When you are sorting and ranking a field with a large amount of data items, it can be difficult to determine the number ranking of the current data item you are analyzing. Furthermore, you may want to turn your pivot table into hard values for further analysis. An integer field that contains the actual rank number of each data item could prove to be helpful in analysis outside the pivot table.

Solution

Select a cell in your pivot table. From the PivotTable toolbar, use the Pivot Table drop-down and select Formulas, Calculated Field.

Figure A.17 shows the Insert Calculated Field dialog box. Give the field a name such as MarketRank. The formula should be =1. Click the Add button to create the field.

Figure A.17. Adding a rank field requires a simple calculated field that will assign a value of 1 to each market.

Initially, the MarketRank field looks fairly useless, reporting that all markets have a rank of 1. Right-click the Sum of MarketRank heading and choose Field Options.

In the PivotTable Field dialog box, give the field a name such as Rank_. Click the Options button to expand the dialog box.

In the expanded dialog box, change the setting for Show Data As to be Running Total In. Because this field is used to rank the markets, change the base field to be Market. Figure A.18 shows the completed dialog box.

Figure A.18. Change the rank field to be a running total within Market.

The result will be a new data field that reports the relative rank of each market, as shown in Figure A.19.

Figure A.19. After you change the setting to Running Total In, the calculated field properly shows the rank of each market.

CAUTION

The rank field will only work for the Market field. If you reshape the report to have Region replace Market, you will have to change the field options to change the base field to Region.