Creating Subtotals

Excel's ability to create automatic subtotals is a handy feature that can save you a great deal of time.

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 on the Data tab, in the Outline group, choose Subtotal:

Outline group in Excel 2016

This dialog box offers the following choices:

Subtotal in Excel 2016
  • 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.

Ask a Question

We use your email only for the communication between you and our team.

Your email Please enter your email Your email

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.