This chapter is from the book
This chapter is from the book
This chapter is from the book
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.26, there is no need to show subtotals for each market because there is only one sales rep for each market.
)
Figure 3.26 Sometimes you do not need subtotals at every level.
If you used the Subtotals drop-down 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. Choose one cell in the Market column. On the Analyze tab, choose Field Settings. Change the Subtotals setting from Automatic to None (see Figure 3.27).
)
Figure 3.27 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, select None under Subtotals, as shown in Figure 3.27.
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 for the Region field, select Custom and then select the types of subtotals you would like to see. The dialog in Figure 3.28 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 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.28 By selecting the Custom option in the Subtotals section, you can specify multiple subtotals for one field.