Do you use Excel’s Subtotal command to automatically create subtotals in a list? I prefer pivot tables, but still use Subtotals in a few macros for clients. Last week, one of them asked why there were duplicate Grand Totals after applying two levels of subtotals.
Why Are There Two Grand Totals?
That was a good question, and when I created subtotals on different data, there was only one Grand Total, as expected. Google was no help in solving the mystery, so I took a closer look at the two data sets, to see if I could spot a difference.
I had also been asked to check on an error in one of the columns, so I decided to tackle that problem first – it seemed easier!
No Error = No Duplicate Grand Totals
The error was in one of the columns that was being subtotaled, and carried down to the grand totals. Fortunately, the formula problem was easy to solve – it was caused when a formula tried to subtract a cell that contained text.
Then a miracle happened – when I ran the subtotal command again, there were no duplicate grand totals! So, it seemed that the error was causing the duplicates.
To test my theory, I created an error in my test data. Next, I applied subtotals, and subtotaled the column with the error. Woohoo! The table had 2 grand totals, instead of one.
Prevent Duplicate Grand Totals
After that exhausting round of testing (3 or 4 tests), I’m convinced that those mysterious duplicate grand totals are caused by errors in any column that is being subtotaled.
- To prevent duplicate grand totals, fix those errors, if possible, or use IFERROR or IF and ISERROR to override error results.
- If you can’t change the formulas, just hide the rows with duplicate grand totals, or ignore them. (Or tell your client, “It’s not a bug, it’s a feature.”)
Video: Subtotals With Duplicate Grand Totals
To see how to apply two subtotals to a list, and see the effect that errors have, watch this short video tutorial. The timeline is listed below the video.
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
Download the Sample File
Visit the Excel Subtotals page on my Contextures website to download the sample file. The zipped file is in xlsx format, and does not contain macros.
____________________
