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.