Home > Formulas > Subtotal Feature

Build and Use Automatic Excel Subtotals

Use Excel's built-in Subtotal feature to automatically add subtotals and grand totals to a worksheet list of data, show and hide details. Subtotal pros and cons. Fix problem of duplicate grand totals.

Note: For help with subtotal formulas, go to the Microsoft Excel SUBTOTAL function page.

Video: Excel Subtotal Feature

In this short video, you'll see the steps for creating subtotals with the Microsoft Excel Subtotal command. Also, discover how to prevent the problem of duplicate grand totals. The written instructions are below the video, and the full video transcript is at the end of this page.

Video Timeline

• 0:00 Introduction
• 0:31 Sorted List
• 0:51 First Subtotal
• 1:44 Second Subtotal
• 2:18 Remove Subtotals
• 2:27 Duplicate Grand Totals
• 2:53 Apply Subtotals
• 3:24 Prevent Duplicates
• 4:29 Get the Sample File

Set Up Worksheet List

First, before you try to use the Subtotal feature in Excel, check that your worksheet list is set up correctly. To prevent problems, and to make the setup easier, follow these data setup guidelines:

• Organize the data in rows and columns
  • Each row has information about one record, such as a sales order, or inventory transaction.
  • Each column has one type of data, such as dates, currency, or text
• Put headings in the first row of the list
  • Use short, descriptive headings
  • Make each heading unique
• Do not leave any completely blank rows or completely blank columns in the list
• Leave blank rows and blank columns around the list, to separate it from any other data on the worksheet

Worksheet List Example

Here's an example of a worksheet list, set up correctly, and ready for the next steps.

There are unique headings in the top row, one type of data in each column, one sales order in each row, and blank cells all around the list.

Do Not Format As Table

WARNING: DO NOT format the worksheet list as a named Excel Table.

Named Excel tables are helpful for sorting and filtering data, but they are not compatible with the Subtotals feature.

In the screen shot below, you can see the Format as Table command, which is on the Home tab of the Excel Ribbon. Do not use that command for your worksheet list.

Excel will not allow you to create subtotals, within an Excel table.

If you select a cell in a formatted table, the Excel Subtotal feature is NOT available in the commands on the Excel Ribbon.

Sort Data Columns

After you set up the worksheet list, where you want to use the Subtotals feature, the next step will be to sort the data.

To sort the list correctly, make the following decisions about the subtotal structure:

• Which columns will have subtotals?
• Will there will be one level of subtotals, or multiple levels of subtotals?

You can sort the data by one column, or by multiple columns, depending on where you want to base the subtotals.

Sort for Two Subtotals

In this example, there will be 2 levels of subtotals, so the data is sorted by those two columns

1. Category
2. Products within each Category

To sort the data by two columns, follow these steps:

• Select all the cells in the list, including the heading cells.
  • Note: This is not required, but I recommend it, to be sure that all of the data is sorted correctly.
• On the Excel Ribbon, click the Data tab.
• In the Sort & Filter group, click Sort button, to open the Sort dialog box.

Sort Dialog Box

When the Sort dialog box opens, follow these steps for sorting data in the list:

• Click the arrow for the Sort by drop down list
• Select the first column you want to sort - Category, in this example.
  • In this example, Category will be the first column sorted.
  • Note: If the drop down is showing Column letters instead of headings, add a check mark to My data has headers.
• Next, from the Sort On drop down, select the option that you want.
  • For this example, leave the default setting of Values.
  • The other options are Cell Color, Font Color, and Conditional Formatting Icon
• Next, from the Order drop down, select one of the options - A to Z, Z to A or Custom List.
  • For this example, select A to Z

Second Sort

• Next, to sort by the Product column, click the Add Level button
• Then, select options from its drop down boxes - sort on Values, and order A to Z
• Finally, to apply the sort order to the worksheet list, click the OK button.

Sorted List on Worksheet

In the screen shot below, you can see the sorted list on the worksheet.

• In column B, the categories are listed A to Z
• In column C, the products within each category are sorted A to Z

Apply First Subtotal

After the list is sorted, you're ready to create the subtotals, for the Category and Product columns.

• Note: In this example, Category column is subtotalled first, because it is the primary interest.

Follow the steps below, to apply the first subtotal.

• First, select any cell in the list that you want to subtotal
  • You don't have to select a cell in column you want to subtotal
• Next, on the Excel Ribbon, click the Data tab
• Then, in the Outline group, click the Subtotal button.
  • If the Subtotal command is not visible, click the drop down arrow for Outline
  • Then, in the drop down list of options, click Subtotal

Subtotal Dialog Box

When the Subtotal dialog box opens, follow these steps, to apply the first level of subtotals:

• In the "At each change in" box, select the first column that you want to base the subtotals on
  • Choose the Category field, in this example, to add subtotal there.
• Next, in the Use Function box, click the arrow to see a list of common functions that you can use when totalling the columns.
  • For this example, select the Sum function
  • Other summary functions are: Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, Varp
    • Note: For subtotals, Count is the same as the worksheet COUNTA function
• Then, in the "Add Subtotal To: section, add a check mark to all the columns in which you want a subtotal.
  • For this example, check the Cases and TotalPrice columns
• Next, remove the check mark from "Replace current subtotals"
  • Remove this in most cases, unless there are existing subtotals that you specifically want to get rid of.
• Check or uncheck the final two options, based on your preferences:
  • Page break between groups
  • Summary below data
• Finally, click the OK button, to apply the Subtotals, and to close the dialog box.

Subtotal With Outline Grouping

After the Subtotal dialog box closes, the data shows a subtotal after each change in the Category column, and there is a Grand Total at the bottom of the data.

At the top left of the worksheet, three grouping buttons (outline symbols) have been added, so you can view specific parts of the data.

Show and Hide Levels

Click the following outline level buttons, to show some or all of the data:

1. Grand Total only
2. Grand Total and Subtotals
3. All data detail rows and totals

Plus and Minus Buttons

You can also click the + (plus) and - (minus) buttons (outline symbols) in the grouping bar at the left of the worksheet, to show or hide sections of the data, based on outline group settings.

• Click + (plus) button to expand (show) the data in a specific section
• Click - (minus) button to collapse (hide) the data in a specific section

Note: Excel will show a maximum of 8 outline buttons for all of the subtotal grouping.

Subtotal Formulas

In the Total rows, formulas have been automatically added to the subtotal columns.

Each formula uses the SUBTOTAL Function, which has the following syntax:

SUBTOTAL(function_num,ref1,[ref2],...)

• function_num: (required) number that specifies which function to use for the subtotal.
  • See a list of the number options on the Subtotal function page
• ref1: (required) first range of cells that you want to subtotal
• ref2: (optional) additional ranges of cells to subtotal, from 2 to 254

In the screen shot below, you can see the formula in cell D8:

• =SUBTOTAL(9,D2:D7)
• This formula uses function_num 9, which is Sum
• It calculates the total for the numbers in cells D2:D7.

Apply Second Subtotal

Next, repeat the previous steps to apply the second subtotal, with the following change:

• In the "At each change in", choose the Product column

WARNING: Be sure to remove the check mark from "Replace current subtotals". Otherwise, the Category subtotals will be removed.

List with Two Subtotals

After the second subtotals are applied, the data will show:

• subtotals after each change in the Category column
• subtotals after each change in the Product column
• single row with Grand Total amounts at the bottom of the data

Also, another grouping button is added at the top left of the worksheet.

Multiple Summary Functions

Instead of using only one summary function for a column, you can set up multiple summary function subtotals, if you need them.

The key to success is this:

1. First, set up all the subtotals for the main subtotal column - one for each summary function
2. After that, set up all the subtotals for the next column - one or more summary functions

In the screen shot below:

• there are 2 subtotals for the Category column - Sum and Average
• there is 1 subtotal for the Product column - Sum
• there are 5 grouping buttons at the top left of the worksheet

Note: Excel will show a maximum of 8 outline buttons for all of the subtotal grouping.

Remove Subtotals

If you no longer need the subtotals, you can follow the steps below, to remove them.

Make Workbook Backup

Before you follow the steps to remove subtotals, do this:

• Save your workbook
• Make a backup copy of your workbook

Why? Because the Remove Subtotals command is quick, and heavy handed:

• There is no confirmation message for this action
• The Undo button does not work, after you click Remove All
• Everything that was previously in your Undo stack is wiped out

Remove Subtotals

Be sure to read the warning (above), before completing these steps:

1. Select any cell in the worksheet list
2. Next, on the Excel Ribbon, click the Data tab, then click Subtotals.
3. At the bottom left of the Subtotal dialog box, click the Remove All button
   • Subtotal dialog box closes automatically
   • All Subtotals are removed from the worksheet list
   • Undo button's list of items is wiped clean  

More Remove All Issues

Thanks to David Newell, who found additional problems that are caused by the Remove All command, in the Subtotal dialog box.

Note: For more from David, see his formula challenge solution, posted on my Contextures Blog.

After some additional testing, the story gets even uglier.  So try this at home. 

• Let's say you have column A with several rows each for values x, y, and z and a set of numbers in column B. 
• Insert subtotals for the numbers in column B with each change in column A. 
• Then go over to column D and put something on the same row as the FIRST subtotal. 
• Now use the feature to Remove All subtotals. 
• You'll get a warning asking if it's ok to delete the entire subtotal row. 
• Say OK and note how the subtotals and the value in column D are gone.

Here's where it gets fun. 

• Recreate the subtotals and put that value back in column D in the FIRST subtotal row. 
• Use the Remove All, only this time, click Cancel when you get the warning popup. 
• Every subtotal row will be removed except the first one -- i.e. it doesn't cancel everything, just that one row.

But we're not done yet. 

• Fully remove and recreate those subtotals once again. 
• This time put something in column D on the same row as the SECOND or THIRD subtotal. 
• Run the Remove All, clicking Cancel

Yyou'll find that:

• all subtotal rows AT AND ABOVE the column D value are kept
• all subtotal rows BELOW that point are deleted. 
• Only if the value in column D is on the Grand Total row does clicking Cancel prevent everything from happening. 
• And if there are column D entries for multiple subtotal rows, then the removals stop at the lowest point.

Fix Duplicate Grand Totals

With some data, a second row of Grand Total values might appear, if you add a second layer of subtotals.

This problem occurs if there are errors in the columns that are being totalled.

In the screen shot below, there are two Grand Total rows, because there is an error in the Total Price column

Prevent Duplicate Grand Totals

To prevent duplicate grand totals, use the IFERROR function, or IF and ISERROR functions, to handle the errors in the source data.

For example, this IFERROR function will show an empty string (""), if the multiplication result is an error:

• =IFERROR(E2*D2,"")

If you can't alter the formulas to prevent errors, another option is to hide the duplicate Grand Total rows, after creating the subtotals.

Subtotal Pros and Cons

When pivot tables were introduced, long ago, I thought that people would use those, instead of the Subtotal feature. But some people love Subtotals, and keep using them.

Subtotal Pros

What are the benefits of the Subtotal feature, that make people keep using it?

• Details – It’s easy to show or hide the data details, if you’re analyzing the totals
• Changes – While troubleshooting, you’re working with the live data, and can quickly change a record, to correct a total
• Habit – Like the SUBTOTAL function, it’s easier to use a familiar tool, than to learn a new one

Subtotal Cons

What are the drawbacks of the Subtotal feature, that make people avoid using it?

• No Tables – You can't format your data as an Excel Table, if you want to use Subtotal feature.
• Rigid – After a Subtotal level is added, you can't edit its settings, such as choosing a different summary function.
• Sledgehammer – The only way to remove a Subtotal is with the Remove button, that acts like a sledgehammer.
  • You can't remove just one level, its' all or nothing.
  • Clicking the Remove button also wipes out the Undo stack - an ugly surprise, if you weren't expecting that!