Microsoft Office Tutorials and References
In Depth Information
Working with Tables
h Var: Displays the variance of the values in the column. Variance is another statistical
measure of how “spread out” the values are.
h More Functions: Displays the Insert Function dialog box so that you can select a function
that isn’t in the list.
Figure 9-10: Several types of summary functions are available for the Total row.
Using the drop-down list, you can select a summary function for the column. Excel inserts a
formula that uses the SUBTOTAL function and refers to the table’s column using a special structured
syntax (described later). The first argument of the SUBTOTAL function determines the type of
summary displayed. For example, if the first argument is 109, the function displays the sum. You
can override the formula inserted by Excel and enter any formula you like in the Total row cell.
For more information, see the sidebar “About the SUBTOTAL function.”
The SUBTOTAL function is one of two functions that ignores data hidden by filtering
(the other is the new AGGREGATE function). If you have other formulas that refer to
data in a filtered table, these formulas don’t adjust to use only the visible cells. For
example, if you use the SUM function to add the values in column C and some rows are
hidden because of filtering, the formula continues to show the sum for all the values in
column C — not just those in the visible rows.
If you have a formula that refers to a value in the Total row of a table, the formula
returns an error if you hide the Total row. However, if you make the Total row visible
again, the formula works as it should.