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.

set up worksheet 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.

do not format as table

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.

Subtotal command not available for Excel table cell

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.

sort the data by two columns

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

sorted list on worksheet

;

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 command on Excel Ribbon Data tab

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.

apply the first subtotals

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.

subtotals and grand total

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.
  • 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.

subtotals and grand total

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

apply the second subtotals

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

apply the second subtotals

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.

apply the second subtotals

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  

remove subtotals

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

duplicate grand totals

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!

Video Transcript

Here is the full transcript for the Excel Subtotals video, shown above.

Excel Subtotals With Duplicate Grand Totals

In Excel, you can use the Subtotal feature to get subtotals and grand totals based on different columns, and then pick which columns you want to total.

This will only work if you've got a list that is not formatted as a table. So if you've used this command format as table, the subtotal command won't be available.

We're going to see how to create the sub totals and then a problem that can occur with duplicate grand totals.

Sorted List

I've got my list and I have it sorted by the two columns that I want to sub total.

I want a subtotal for each category and then a subtotal for each product within those categories.

And we're going to then total the cases and the total price.

First Subtotal

To create the subtotal, I'm going to the Data tab and click subtotal.

  • The first column that I want to get subtotals for is category, so I'll select that.
  • I want a sum and I want to total the cases and total price.
  • You might see a check mark here when you first opened this box and I don't want to replace any current subtotals. It doesn't matter right now cause I don't have any.
  • I also want my summary below the data rather than at the top.
  • I don't want any page breaks.

So click OK, and there are my first sub totals, one for each category and a grand total.

And I can see grouping at the side, so I could go just to the grand total or show everything again.

Second Subtotal

Next I'm going to add a subtotal for each product.

  • So going back to subtotal I'll select product.
  • Again, I want a sum and the same two columns and in this case I want to make really sure that I'm not replacing because I want to add another layer of subtotals.
  • Click OK.

We've got our product subtotals and those category subtotals are still there.

And at the bottom there's a single grand total that shows the overall amounts.

Remove Subtotals

Once you're finished with the subtotals, you can remove them.

Just go back to subtotal and click Remove All.

Duplicate Grand Totals

Sometimes when you use that subtotal feature, you might end up seeing more than one grand total at the bottom. The reason for that is errors in a column that you're subtotalling.

If I put NA here, so =NA(), press Enter.

Now we've got an error here which flows through to the total price.

Apply Subtotals

We'll see what happens now, when we do that same subtotalling.

  • So I'll click anywhere in this list, Subtotal.
  • First on Category.
  • Right now we have one grand total, it's showing N/A, but there's just one.
  • I'll subtotal again on product,
  • same two columns checked.
  • I don't want to replace.
  • When I click OK, I now have at the bottom two grand totals.

So because of that NA error, it's created a second grand total.

Prevent Duplicates

So to prevent that, just make sure there aren't any errors in any of the columns that you're using for totals.

  • You can use the IFERROR function to prevent errors. Just show an empty string or a zero instead of allowing it to show the error.
  • Or if you can't do that, you could right-click here, and hide that, and just ignore it.

Get the Sample File

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com.

Get the Sample File

Click here to get the sample file for Excel Subtotals. The zipped file is in xlsx format, and does not contain macros.

More Tutorials

Pivot Tables

SUBTOTAL Filtered List

SUBTOTAL Function

Functions List

30 Functions in 30 Days

Last updated: September 23, 2022 3:56 PM