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.
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.
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:
When the Sort dialog box opens, follow these steps for sorting data in the list:
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.
When the Subtotal dialog box opens, follow these steps, to apply the subtotals:
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.
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.
If you no longer need the subtotals, follow these steps to remove them.
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
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.
Here is the full transcript for the Excel Subtotals video, shown above.
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.
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.
To create the subtotal, I'm going to the Data tab and click subtotal.
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.
Next I'm going to add a subtotal for each product.
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.
Once you're finished with the subtotals, you can remove them.
Just go back to subtotal and click Remove All.
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.
We'll see what happens now, when we do that same subtotalling.
So because of that NA error, it's created a second grand total.
So to prevent that, just make sure there aren't any errors in any of the columns that you're using for totals.
For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com.
Click here to get the sample file for Excel Subtotals. The zipped file is in xlsx format, and does not contain macros.
Last updated: June 1, 2022 7:44 PM