In the past, I’ve written about a duplicate grand total problem in Excel’s automatic Subtotals feature. Yesterday I noticed another problem, and it was nasty surprise!
Excel Subtotal Screen Shots
This week, I was updating the Excel Subtotals Feature page on my Contextures site, and getting a few new screen shots.
First, I added a couple of Subtotals to a worksheet list, then I made a couple of small changes to the worksheet.
Here’s the Undo list, showing those changes.
Next, I took a screen shot of the Subtotal dialog box, showing the Remove All button.
Remove All Button Impact
To get ready for the next screen shot, I clicked the Remove All button.
Sometimes, Excel asks you to confirm an action, like the message you get when you try to delete a worksheet.
However, for the Remove All button, there wasn’t a confirmation message.
- The Subtotal dialog box closed automatically
- All the subtotals were removed from the worksheet list
Try to Undo the Remove All
Next, I decided to put the subtotals back, so I could get a different screen shot.
And that’s when I got the nasty surprise – the entire Undo stack had been wiped out!
The Undo command on my Quick Access Toolbar was dimmed out – there was nothing left in the list.
So, my only choice was to rebuild the Subtotals from scratch.
Be Prepared Before Remove All
So, here’s my advice to you, if you’re using the Subtotal feature.
Before you click the Remove All button, to remove all the subtotals, do this:
- Save your workbook
- Make a backup copy of your workbook
After that, you can remove the subtotals.
Just remember that there’s no way to undo that action, and get your Subtotals back.
Video: Excel Subtotals Feature
In this short video, you’ll see how to create automatic subtotals with the Microsoft Excel Subtotal command.
Also, there’s a demo of duplicate grand totals problem, and the steps to prevent that.
The written instructions and sample file are on the Excel Subtotals Feature page on my Contextures site.
______________
Excel Subtotal Feature Problems
______________
Not that it’s a great reason, but I wonder if that’s because removing the subtotals literally deletes those entire rows of the worksheet (i.e. a “big” operation)?
Thanks, David, and that makes sense. A confirmation message, warning about the side effects, would be nice though!
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, and you’ll find that all subtotal rows AT AND ABOVE the column D value are kept with all subtotal rows BELOW that point 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.
Yikes! You were right about them deleting worksheet rows.
Thanks for testing this, and reporting the results.
I’ve added your notes to the Excel Subtotals page on my Contextures site, so people will be aware of this problem.
https://www.contextures.com/exceldatasubtotals.html