Use Excel's built-in Subtotal command to automatically add subtotals and grand totals to a list of data.
You can watch the steps for creating subtotals, and preventing duplicate grand totals, in the Subtotal video, show below. The written instructions are below the video.
Before applying subtotals, the data must be sorted by the columns on which you want to base the subtotals. In this example, Category and Product will be subtotaled, so the data is sorted by those two columns.
After the data is sorted, follow these steps to apply the first subtotal. In this example, the Category column will be subtotalled first.
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, grouping buttons are 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 + and - buttons in the grouping bar, to show or hide sections of the data.
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 Grand Total 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.
Click here to get the sample file for Excel Subtotals. The zipped file is in xlsx format, and does not contain macros.
Last updated: July 11, 2021 7:35 PM