To use this feature, your list must be sorted, because the subtotals are inserted whenever the value in a specified field changes.
To insert subtotal formulas into a list automatically, move the cell pointer anywhere in the list and choose Data -> Subtotals:
This dialog box offers the following choices:
- At Each Change In: This drop-down list displays all fields in your list. The field that you choose must be sorted.
- Use Function: This gives you a choice of 11 functions. You should normally use Sum (the default).
- Add Subtotal To: This list box lists all the fields in your list. Place a check mark next to the field or fields that you want to subtotal.
- Replace Current Subtotals: If this box is checked, any existing subtotal formulas are removed and replaced with the new subtotals.
- Page Break Between Groups: If this box is checked, Excel inserts a manual page break after each subtotal.
- Summary Below Data: If this box is checked, the subtotals are placed below the data (the default). Otherwise, the subtotal formulas are placed above the totals.
- Remove All: This button removes all subtotal formulas in the list.
When you click OK, Excel analyzes the list and inserts formulas as specified - and creates an outline for you. The formulas all use the SUBTOTAL worksheet function.