Customizing a pivot table

Adding and removing subtotals

Subtotals are an essential feature of pivot table reporting. Sometimes you might want to suppress the display of subtotals, and other times you might want to show more than one subtotal per field.

Suppressing subtotals with many row fields

When you have many row fields in a report, subtotals can obscure your view. For example, in Figure 3-34, there is no need to show subtotals for each market because there is only one sales rep for each market.

FIGURE 3-34 Sometimes you do not need subtotals at every level.

If you used the Subtotals drop-down menu on the Design tab, you would turn off all subtotals, including the Region subtotals and the Market subtotals. The Region subtotals are still providing good information, so you want to use the Subtotals setting in the Field Settings dialog box. Choose one cell in the Market column. On the PivotTable Analyze tab, choose Field Settings. Change the Subtotals setting from Automatic to None (see Figure 3-35).

FIGURE 3-35 Use the Subtotals setting in the Field list to turn off subtotals for one field.

To remove subtotals for the Market field, click the Market field in the bottom section of the PivotTable Fields list. Select Field Settings. In the Field Settings dialog box, select None under Subtotals, as shown in Figure 3-33. Alternatively, right-click a cell that contains a Market and remove the check mark from Subtotal Market.

Adding multiple subtotals for one field

You can add customized subtotals to a row or column label field. Select the Region field in the bottom of the PivotTable Fields list, and select Field Settings.

In the Field Settings dialog box for the Region field, select Custom and then select the types of subtotals you would like to see. The dialog box in Figure 3-36 shows five custom subtotals selected for the Region field. It is rare to see pivot tables use this setting. It is not perfect. Note that the count of 211 records in cell D25 automatically gets a currency format like the rest of the column, even though this is not a dollar figure. Also, the average of $12,333 for South is an average of the detail records, not an average of the individual market totals.

FIGURE 3-36 By selecting the Custom option in the Subtotals section, you can specify multiple subtotals for one field.