Creating Subtotals

Excel 365 2016 2013 2010 2007
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 choose Data -> Subtotals:

Subtotal in Excel 2003

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.

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

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more