Excel Subtotals

Use Excel's built-in Subtotal feature to automatically add subtotals and grand totals to a list of data. See how to solve problem if duplicate grand totals are showing. Note: Excel Subtotal feature is NOT available for data in a named Excel Table.

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 subtotalswith the Subtotal command, and 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

Sort the Data

Before applying subtotals, the data must be sorted by the column, or multiple columns, on which you want to base the subtotals.

In this example, Category and Product will be subtotalled, so the data is sorted by those two columns.

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

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

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

• Click the Add Level button, to add the first sorting level.
• From the Sort by drop down, select the first column you want to sort. In this example, Category will be the first column sorted.
  • Note: If the dropdown 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. We're sorting on the values in the Category column, so leave the default setting of Values.
• 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
• 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, click the OK button.

Apply the First Subtotal

After the data is sorted, follow the steps below, to apply the first subtotal.

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

• First, select any cell in the list that 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 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, select the summary function that you want to use when totaling the columns.
  • The Sum function was selected for the calculation in this example.
  • You can select multiple summary functions, if needed, from this list of common functions
• Then, in the "Add Subtotal To: list, add a check mark to all the columns in which you want a subtotal.
  • Cases and TotalPrice, in this example
• Next, remove the check mark from "Replace current subtotals"
  • Do this in most cases, unless there are existing subtotals that you want to remove.
• Check or uncheck the final two options, based on your preferences:
  • Page break between groups
  • Summary below data
• Click the OK button, to apply the Subtotals, and to close the dialog box.

First Subtotal and Grouping

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

At the top left, three grouping buttons have been added, so you can view specific parts of the data:

1 - Grand Total only

2 - Grand Total and Subtotals

3 - All data and totals

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

Apply the Second Subtotal

Next, repeat the previous steps to apply the second subtotal. In this example, the Product column will be subtotalled second.

Be sure to remove the check mark from "Replace current subtotals", so the Category subtotals are not removed.

After the second subtotals are applied, the data will show a subtotal after each change in the Category column, and each change in the Product column, and there will be a single Grand Total at the bottom of the data.

Another grouping button is added at the top left of the worksheet.

Remove Subtotals

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

1. Select a cell in the list, and on the Excel Ribbon, click the Data tab, then click Subtotals.
2. Click the Remove All button, to remove the Subtotals.  

Duplicate Grand Totals

With some data, a second row of Grand Total values might appear, if you add a second layer of subtotals. This occurs if there are errors in the columns that are being totaled.

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. For example:

=IFERROR(E2*D2,"")

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

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