Page 61 - Excel Data Analysis

04 537547 Ch03.qxd 3/4/03 11:50 AM Page 47

EVALUATE WORKSHEET DATA 3

The Subtotal option actually inserts the SUBTOTAL function in each If you want to remove all
summary row in the form of a formula, which updates automatically subtotals from your worksheet,
whenever you modify a value in the corresponding data records. click Data ➪ Subtotals to
You can make modifications to each formula to alter the summary display the Subtotals dialog
range or function. See Chapter 4 for information on formulas. box. Click the Remove All
button. Excel removes the
The first argument of the SUBTOTAL function defines the function subtotal and final total rows
that summarizes the data records. Excel uses numbers between 1 from the worksheet.
and 11 to identify the functions in the following order: Average,
Count, Count Nums, Max, Min, Product, StDev, StDevP, Sum,
Var, and VarP. The second argument specifies the range of cells to
subtotal. For example, the following formula sums the subtotal
range, and uses a 9 in the first argument to correspond to the Sum
function, the ninth function in the list:
=SUBTOTAL(9,B3:B10)

Sum

Amount

› Select the function that ■ Click an option to replace ■ Excel summarizes the data ■ This example sums each
you want to insert. current subtotal rows, place a using the selected function. group and creates subtotal
page break between groups, and final total amounts.
ˇ Click the column or or to place subtotals below
columns to which you want each group.
to apply the function.
Á Click OK.

47