Microsoft Office Tutorials and References

In Depth Information

**Adding a Total Row**

A few things happen here. The Total row has moved in
atop
Class Averages, because that latter row

is no longer part of the table
. If this looks messy and/or puzzling, you could delete the Class Averages

data, or insert some rows between Total and Class Averages in order to separate the two, which is what

we’ll do. In addition, the data in the
last
table field—Averages—has been added, and only that field. But

the function the table uses here isn’t SUM, but one we’ve yet to introduce—
SUBTOTAL
, and it looks like

this, as it appears in cell I2:

=SUBTOTAL(109,[Average])

Here, SUBTOTAL calculates data for a field—in this case Average, the name of the field on the far

right of the table. The 109 refers to the particular
mathematical operation
being conducted on the

range—which is the default operation, Sum. But click the down arrow by that cell and you’ll see (Figure

6–38):

Figure 6–39.
Calculation options for the Total row

You can click any of the possibilities above, and it will give SUBTOTAL a new code which carries out

that operation instead. These are the SUBTOTAL codes representing each of the above operations, as

they appear in the drop-down-menu sequence:

Average - 101

Count All - 102

Count (only cells containing) Numbers – 103

Max -104

Min – 105

Sum – 109

Standard Deviation – 108

Variance – 110

In fact, the SUBTOTAL codes we see above sport an extra “1” because its codes are really 01, 02, etc.

That first digit is still another code, instructing SUBTOTAL to ignore, and so not compute, any data on

table rows
you
may have hidden via the Hide Rows command (but more importantly, the data on rows

hidden by the AutoFilter
will
still be computed).

And if you click any cell in the Total row for any of the other fields, a similar drop-down arrow

appears. Click, and you can select from the same options (in fact, you may want to select None for the

Average field in our case—because Sum here is really just
adding
the test averages of the individual

students, probably a bit of information you don’t need. Note as well that the
More Functions…
option

allows you to select
any of the
others in Excel’s collection as well).