Displaying Totals and Subtotals

By default, Excel generates grand totals for all outer fields in your PivotTable using the same summary function as the body of the table. In Figure 22-3, for example, row 30 displays grand totals for each quarter of each year, as well as for the years themselves. Column L, meanwhile, displays per-category totals by channel. The intersection of column L and row 30 displays the grandest of totals, the sum of all sales for the period covered by the table. Because the body of the table uses the SUM function, all these grand totals do as well.

To remove grand totals from a PivotTable, right-click any cell in the table, and click PivotTable Options. On the Totals & Filters tab in the PivotTable Options dialog box, clear the Show Grand Totals For Rows check box, the Show Grand Totals For Columns check box, or both check boxes.

Naturally, PivotTables are not restricted to calculating sums. For other calculation options, see "Changing PivotTable Calculations" on page 731.

Customizing Subtotals

By default, Excel creates subtotals for all but the innermost fields. For example, in Figure 22-3, cell B6 displays the sum of cells B7: B9 (the Children subtotal for Quarter 1 of 2005), cell C10 displays the sum of cells C11: C13 (the Mystery subtotal for Quarter 2 of 2005), and so on. Columns F and K display yearly subtotals. The innermost fields, Channel (for the row axis) and Quarter (for the column axis), do not have subtotals.

To find options affecting all subtotals, select a cell in the PivotTable, click the Design tab under PivotTable Tools, and then click Subtotals on the left edge of the Ribbon:

You can use this menu to turn subtotaling off altogether or to move row-axis subtotals from their default position above the detail items to a position below.

To customize subtotals for a particular field, right-click an item in the field, and then click Field Options. (Alternatively, select an item in the field, click the Options tab under PivotTable Tools, and then click Field Settings in the Active Field group.) Figure 22-9 shows the Field Settings dialog box for the Category field in our example PivotTable.

Figure 22-9: In the Field Settings dialog box, you can override the default subtotaling behavior for a particular field.

The Automatic option on the Subtotals & Filters tab in this dialog box means-as Automatic means throughout Excel-you're letting the program decide what to do. In other words, this option gets the default behavior. You can turn off subtotals for the selected field by selecting None. Selecting Custom lets you change the default subtotal calculation, such as from Sum to Average. And, as the text above the function list suggests, you're not limited to one function. You can select as many as you need by holding down Ctrl while you click. Figure 22-10 shows a PivotTable with four subtotaling calculations applied to the Category field. (Note that when you have multiple subtotals for a field, Excel moves them below the detail.)

Figure 22-10: You can generate subtotals using more than one summary function; this table uses four for the Category field.

By using the Field Settings dialog box, you can also generate subtotals for innermost fields-subtotals that Excel usually does not display. Such inner subtotals appear at the bottom of the table (just above the grand total row) or at the right side of the table (just to the left of the grand total column). Figure 22-11 shows an example of inner-field subtotaling.

Figure 22-11: Subtotals for Channel, an inner field, appear in rows 30–32 of this table.