Microsoft Office Tutorials and References
In Depth Information
Working with Tables
When the first argument is greater than 100, the SUBTOTAL function behaves a bit differently.
Specifically, it does not include data in rows that were hidden manually. When the first argument
is less than 100, the SUBTOTAL function includes data in rows that were hidden manually but
excludes data in rows that were hidden as a result of filtering or using an outline.
To add to the confusion, a manually hidden row is not always treated the same. If a row is
manually hidden in a range that already contains rows hidden via a filter, Excel treats the manually
hidden rows as filtered rows. After a filter is applied, Excel can’t seem to tell the difference
between filtered rows and manually hidden rows. The SUBTOTAL function with a first argument
over 100 behaves the same as those with a first argument under 100, and removing the filter
shows all rows — even the manually hidden ones.
The ability to use a first argument that’s greater than 100 was introduced in Excel 2003. You can
use this updated version of the SUBTOTAL function anywhere in your workbook; that is, it’s not
limited to tables. Be aware, however, that this function is not backward compatible. If you share
your workbook with someone who is using a version prior to Excel 2003, the SUBTOTAL
function will display an error if you use a first argument greater than 100.
Another interesting characteristic of the SUBTOTAL function is its ability to produce an accurate
grand total. It does this by ignoring any cells that already contain a formula with SUBTOTAL in
them. For a demonstration of this ability, see the “Inserting Subtotals” section later in this chapter.
Using formulas within a table
Adding a Total row to a table is an easy way to summarize the values in a table column. In many
cases, you’ll want to use formulas within a table. For example, in the table shown in Figure 9-11,
you might want to add a column that shows the difference between the Actual and Projected
amounts. As you’ll see, Excel makes this very easy when the data is in a table.
Figure 9-11: Adding a calculated column to this table is easy.