By default, Excel creates subtotals for all but the innermost fields. For example, in Figure 23-3, cell B6 displays the sum of cells B7:B9 (the Children subtotal for Quarter 1 of 2009), cell C10 displays the sum of cells C11:C13 (the Mystery subtotal for Quarter 2 of 2009), 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 23-9 shows the Field Settings dialog box for the Category field in our PivotTable example.

Figure 23-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—that you’re letting the program decide what to do. In other words, this option gives you 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 23-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.)

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 23-11 shows an example of inner-field subtotaling.

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

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