Microsoft Office Tutorials and References

In Depth Information

**Using a Pivot Table Instead of Formulas**

4.
In the Subtotal dialog box, specify At Each Change in Gender, Use Function Average, and

Add Subtotal to Score.

The result of adding subtotals is shown in Figure 103-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:

=SUBTOTAL(1,B2:B13)

=SUBTOTAL(1,B15:B28)

=SUBTOTAL(1,B2:B28)

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 103-2:
Excel adds subtotals automatically.