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.