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.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.