Analyzing Microsoft Excel 2010 Data with PivotTable Reports

Displaying Totals and Subtotals

By default, Excel generates grand totals for all outer fields in your PivotTable by using the same summary function as the body of the table. In Figure 23-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 use that function 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 800.

Customizing Subtotals

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.