Microsoft Office Tutorials and References
In Depth Information
If you're an array formula aficionado, you might be tempted to use a literal array in place of the criteria range.
In theory, the following array formula should work (and would eliminate the need for a separate criteria range).
Unfortunately, the database functions do not support arrays, and this formula simply returns a #VALUE! error.
Excel's Data ⇒ Outline ⇒ Subtotal command is a handy tool that inserts formulas into a list automatically. These
formulas use the SUBTOTAL function. To use this feature, your list must be sorted because the formulas are in-
serted whenever the value in a specified column changes. For more information about the SUBTOTAL func-
tion, refer to the sidebar, “About the SUBTOTAL function,” earlier in this chapter.
When a table is selected, the Data ⇒ Outline ⇒ Subtotal command is not available. There-
fore, this section applies only to lists. If your data is in a table and you need to insert
subtotals automatically, convert the table to a range by using Table
Tools ⇒ Design ⇒ Tools ⇒ Convert To Range. After you insert the subtotals, you can con-
vert the range back to a table by using Insert ⇒ Tables ⇒ Table.
Figure 9-25 shows an example of a list that's appropriate for subtotals. This list is sorted by the Month field, and
the Region field is sorted within months.
This workbook, named nested subtotals.xlsx, is available at this book's website.
To insert subtotal formulas into a list automatically, activate any cell in the range and choose Data ⇒ Out-
line ⇒ Subtotal. You will see the Subtotal dialog box, similar to the one shown in Figure 9-26.
The Subtotal dialog box offers the following choices:
• At Each Change In: This drop-down list displays all the fields in your table. You must have sorted the list
by the field that you choose.
• Use Function: Choose from 11 functions. (Sum is the default.)
• Add Subtotal To: This list box shows all the fields in your table. Place a check mark next to the field or
fields that you want to subtotal.
• Replace Current Subtotals: If checked, Excel removes any existing subtotal formulas and replaces them
with the new subtotals.
• Page Break between Groups: If checked, Excel inserts a manual page break after each subtotal.