Microsoft Office Tutorials and References
In Depth Information
When you click OK, Excel analyzes the database and inserts formulas as specified — it even
creates an outline for you. Figure 9-28 shows a worksheet after adding subtotals that summarize by
month. You can, of course, use the SUBTOTAL function in formulas that you create manually.
Using the Data
Subtotals command is usually easier.
Figure 9-28: Excel adds the subtotal formulas automatically and creates an outline.
If you add subtotals to a filtered database, the subtotals may no longer be accurate
when you remove the filter.
The formulas all use the SUBTOTAL worksheet function. For example, the formula in cell E20
(Grand Total) is as follows:
Although this formula refers to other cells that contain a SUBTOTAL formula, those cells are not
included in the sum to avoid double-counting.
You can use the outline controls to adjust the level of detail shown. Figure 9-29, for example,
shows only the summary rows from the subtotaled table. These rows contain the SUBTOTAL
formulas. I hid columns B and C because they show only empty cells.
In most cases, using a pivot table to summarize data is a much better choice. Pivot
tables are much more flexible, and formulas aren’t required. Figure 9-30 shows a
pivot table created from the data. Refer to Chapter 18 for more information about pivot