Microsoft Office Tutorials and References
In Depth Information
cause of filtering, the formula continues to show the sum for all the values in column C
— not just those in the visible rows.
About the SUBTOTAL function
The SUBTOTAL function is very versatile, but it's also one of the most confusing functions in Excel's arsenal. First
of all, it has a misleading name because it does a lot more than addition. The first argument for this function re-
quires an arbitrary (and impossible to remember) number that determines the type of result that's returned. For-
tunately, the Excel Formula AutoComplete feature helps you insert these numbers.
In addition, the SUBTOTAL function was enhanced in Excel 2003 with an increase in the number of choices for its
first argument, which opens the door to compatibility problems if you share your workbook with someone who
uses an earlier version of Excel.
The first argument for the SUBTOTAL function determines the actual function used. For example, when the first
argument is 1, the SUBTOTAL function works like the AVERAGE function. When you enter a formula that uses
the SUBTOTAL function, the Formula AutoComplete feature guides you through the arguments, so you never
have to memorize the numbers.
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.
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.
If you have a formula that refers to a value in the Total row of a table, the formula re-
turns an error if you hide the Total row. However, if you make the Total row visible
again, the formula works as it should.
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-12, 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.