Microsoft Office Tutorials and References
In Depth Information
Tip 78: Using a Pivot Table Instead of Formulas
4. Specify At Each Change in Gender, Use Function Average, and Add Subtotal to Score.
The result of adding subtotals is shown in Figure 78-2. Notice that Excel also creates an outline, so
you can hide the details and view just the summary.
The formulas inserted by Excel use the SUBTOTAL function, with 1 as the first argument (1 represents
average). Here are the formulas:
The formula in cell B30 calculates the Grand Average and uses a range that includes the other
two SUBTOTAL formulas in cells B14 and B29. The SUBTOTAL function ignores cells that contain
other SUBTOTAL formulas.
Figure 78-2: Excel adds subtotals automatically.