Make Undo stacks track per workbook instead of globally
Undo stacks are currently global between all workbooks that are open at a given time, so pressing undo on one workbook might undo a change in a different workbook. As someone who frequently has multiple workbooks open at a time, it would be much more helpful to be able to step through the edits made on each individual workbook.
Justin
shared this idea
Thanks to everyone for the votes and discussion about having undo independently in each workbook. Even though this request has been here for a long time, we are listening and we realize that it can be frustrating if you press Undo while you’re in one workbook and it undoes something in another workbook. We’ve been considering the technical challenges to make Undo work “per workbook”, and want to share some details about it with you.
The undo process relies on the state of all open workbooks being exactly the same after an “undo” as they were before the undone action was taken. One example of how undo “per workbook” is problematic is with linked workbooks. Let’s say you have WorkbookA, with a formula that refers to WorkbookB, such as =SUMIFS. This formula will give the sum of values in WorkbookB in range A1:A10 that have “Yes” in the same rows of column B. You change the formula by switching last parameter from “Yes” to “No”. Then you insert a row in workbook B which causes the references in workbook A to adjust accordingly to refer to rows 2-11 rather than 1-10. The formula would be =SUMIFS. If you then undo the formula change in workbook A, the references would again refer to rows 1-10, which would give an unwanted result, since it would not take into account that you inserted the row.
Step-by-step, the formula would be as below:
=SUMIFS
Change “Yes” to “No”:
=SUMIFS
Insert row 1 in WorkbookB, and Excel adjusts the range reference accordingly:
=SUMIFS
Undo in WorkbookA – the range references would then be incorrect, because the inserted row would still exist in WorkbookB:
=SUMIFS
You would expect the formula to keep the references and only change “No” back to “Yes”, like this:
=SUMIFS
There are a number of other challenges and tradeoffs to consider between the current behavior and the requested behavior. We wanted to let you know it would be significant undertaking to make this change. Your votes and suggestions help us plan and prioritize, and we sincerely appreciate all the interest. Even though we don’t have plans to address this soon, we’ll continue to monitor the votes.
Thanks,
Steve K [Microsoft Excel]
217 comments
-
Anonymous
commented
can we have an option to just undo the current worksheet instead of all the opened worksheet.
-
WhyPutaRealNameOn APublicForum
commented
Understanding the complexity as explained in Steve Ks' explanation with =SUMIFS is great, but from the casual user point of view seems to present a complexity that is actually rather simple to approach from a design point of view.
The simple answer is to have the option for > 1 undo stacks. And you setup your configuration to use single stack or multi stack.
IF you are a user that works in a cross sheet environment - then you simply choose the SingleUndoStack option and experientially nothing changes.
IF you are not a multi-intersecting sheet user; or are willing to take the responsibility for watching your changes and managing them, then you could select the MultiUndoStack' option where you have one undo stack per sheet.
Technically - this actually easily managed. You have one undo stack for each sheet, and then for a SingleUndoStack experience you have a meta 'one stack to rule them all' behind the scenes that takes priority, and it tracks the single sheet stacks in the order that changes are made on the other individual sheet stacks. This would likely be the easiest approach. Because the control for multi/single stack undo is actually a switch that enables the meta-undo tracker.
I hazard the bulk of Excel users don't need cross sheet awareness, and this 'feature' burns more people than the lack would negatively impact. (BUT I do recognize that the power user would have a different idea of the scope of impact - and it is the business world that drives this specific decision).
The idea that it has to be a or nothing would seem to me to be a false binary. And it seems to not be accounting for what would actually be a quite simple option programmatically.
-
Rob
commented
1000% agree that, if nothing else, MS should work on bringing to the front whatever workbook is being affected by the undo, so at least it's *somewhat* clearer to the user what is happening.
-
Anonymous
commented
This is a serious usability issue. Simply dismissing it and saying "we won't have time" is extremely frustrating for users...
Like many who commented before, I suspect the code for Excel is a mess and correcting this problem would be complex, but it doesn't mean it's not necessary.
The current behavior isn't only frustrating, it's DANGEROUS for many, if not all, users.
Stealthily undoing unexpected actions in a sheet/workbook that isn't the one with focus can lead to disasters (bankruptcies and even deaths aren't farfetched depending on usecases) because the changes can affect files the user didn't even realize where still opened and will never suspect where affected.
Having the undo stack track globally rather than by workbook means many workbooks can be perverted while the user thinks he's working in 1 workbook.
I agree with S O. The workbook/sheet affected by the undo should at the very least get focus so users could have a clue something is wrong.
-
S O
commented
How about the workbook being undone should just pop up on top, at least I'll be like, "hey! I was just working on something else, why did this one come up now?" Is that too complicated?
-
Rob
commented
@David Separate instances is a good option for Windows users — if you know how to do it. Not so much for Mac. And it makes switching between open sheets more difficult and confusing. On a Mac, CMD+` switches between windows within an application, while CMD+TAB switches between applications. On Windows CTRL+TAB switches between all open windows in every application.
-
David commented
I agree that many codebases become very convoluted and difficult to maintain as time goes on, I have had enough of dealing with that in my career. But, there is a relatively simple solution for the average user. Upon request of the user, always start Excel in a new instance. Unlike the flag option that partially works now, with this option in Excel, every time Excel starts whether from File Manager when I click on a filename, when I select a file via a right-click on the Excel icon, or starting Excel itself, Excel starts a new instance. This causes searches and undo's to operate as expected, on each file separately. While it uses more resources, it would not be much as most users don't open a large number of files anyway.
-
Rob
commented
Anonymous from October 19, 2020 8:45 AM is right on many counts, not the least of which are...
1) Word and Excel are completely different development and maintenance teams. If you have any trouble believing this, just look at all the commands that have wildly different keyboard shortcuts in each app — for example, insert date is CMD+; in Excel, and CTRL+SHIFT+D in Word.
2) Nobody here, other than Steve K responding from the Excel development team, has any idea how much work it would be to make the change we're requesting. As someone with QA experience, I can tell you that an app just a few years old can have layers of convoluted code if there isn't a conscious effort to keep the code clean. Unraveling the undo stacks to clean them up and give them two paths of functionality is, I'm 100% sure, a major task.
Having said that, this never should have become a problem in the first place. Cross-workbook undo stacks should *never* have become the default. It seems likely that the Excel team was not thinking about the average user, but rather thinking only of power-users, when this way of handling stacks was initiated. And it seems very likely that behind the scenes the Excel codebase is a tremendous mess, given that Excel 2008 was 58MB, and the current version of Excel is 1.7GB. Microsoft most definitely didn't add 30x more functionality, or make it 30x better, in the process of making it 30x bigger. (I'd like to see Microsoft do the occasional ground-up overhaul of Office apps like Apple does with MacOS every once in a while.)
So I'm understanding of why this request is probably a much bigger deal that most of us here could comprehend. Without question, Microsoft still needs to fix this. But linked workbooks is not "just an excuse" (even without much coding experience, it's obvious to me that's a **** of a lot more complicated and intertwined with other code than a simple, standard undo within a single document), and how Microsoft Word works could not be less relevant to the discussion at hand.
-
Anonymous
commented
You can decide that it is lame if you want to, however the reality is that different development teams wrote the code for Excel and Word. You can see a number of features that function differently across all of the Office Products. Since the existing code is written this way we are stuck with it until the business case for fixing it makes sense to Microsoft.
Users are pretty arrogant when they act like they know more about what it will take to change the code to make it work the way they want it to, then the developers who are actually reading the code and have the skill to actually understand what the change involves.
Also, current versions of Word work the same way across multiple workbooks. I actually find it more annoying in Word then in Excel.
-
Anonymous
commented
The "linked" document (or cross referenced, whatever name you like) is just an excuse. A lame excuse. Words documents also have linked document but that work.
-
S O
commented
At the very least you should have the workbook that had the latest step "undone" should get activated and jump to the top so the user should be aware that something possibly unexpected happened.
Just leaving it as is, is unfair, ridiculous, and irresponsible.I actually do use cross-workbook references very frequently, However like one user commented a while back, if I close a workbook, suddenly the undo steps from that workbook are no longer there, so I find it reallllly hard to believe that no solution can be found.
And with my using cross-workbook references pretty much on a daily basis I still find it horrifically distorting and disrupting not to be clearly notified when something was undone not in the currently active workbook.
-
B
commented
Sorry, but I am going to be brutal here.
Cross workbook references in Excel the the most broken feature in the entire program. Their behaviour depends on which documents are open. If I have two documents called "Workbook 1", the reference will point to whichever of the two happens to be open at the time. The danger of operating this way cannot be overstated. Adding cross-workbook undo to this just exacerbates the issue, especially since the undo action is not well communicated to the user. Imagine an undo stack with cross-workbook references. Now imagine the user closes one document and opens another of the same name, before performing the undo...In my mind this is simply breaking more and more working features, in the name of maintaining safety with another one that already has such a fundamental design flaw that the greatest care must be taken to use it safely in the first place.
-
SEUMAS MACLAREN
commented
I think most agree it is not complex to provide basic separation of undo between workbooks. Appreciate the need for special cross-workbook advanced undo - so make it an option to turn on? Of course there are users with complex requirements for workbooks, and, if that functionality works now, then it seems to me that we should have a switch available for users to enable cross-workbooks undo to integrate undo stacks after a restart of Excel perhaps. Otherwise the default should be a simple segregated set of undo stacks; one per instance, with a maximum undo level of say, 500 changes. No one will argue with that. Even 100 would be fine and most will only ever use 50 or so. We don't need to reinvent wheels.
The general argument I would propose is: The specialist needs of a few users should not make the feature unusable to all basic users who want to open a couple of unrelated sheets during a phone call, and make a few edits, but undo some of those later. Undo MUST (by the rules of WYSIWYG) undo at the visible point one selects the undo action, else warn you about the changes. It's such a basic functionality to communicate with the user. Undo should also perhaps have an option, rather like the Review functionality on a Word document, not show, or register some format changes like zooming a sheet (as already requested by others) or changing text format
perhaps (although Track Changes does work a bit different from undo in Excel admittedly).Please consider providing user interface communication with the user using an edit-Undo dashboard that can float, or pop-up. At present, the user does not even have the option to see the undo-action list with the references to the worksheet and select the action required. It should (or could) flag the workbook name at the heading of each set of undo actions, all the way down that drop-down list.
Make any sense? -Hope so.
Cheers all for such a brilliant and strongly focussed campaign to get Microsoft Excel chiefs listening.
SM -
Anonymous
commented
This news is distressing.
The current behavior isn't "frustrating", it's scary and EXTREMELY DANGEROUS!!!
Stealthily undoing unexpected actions in a sheet/workbook that isn't the one with focus can lead to disasters (bankruptcies and even deaths aren't farfetched depending on usecases).
At the very least, even if MSFT really thinks the linked workbook scenario is significant and would be hurt by having the Undo stack track per workbook, the undo should move focus to the cell/region affected by the undo.
At this point yes it would still be frustrating but it could be managed. The current behavior is DANGEROUS.
How many people don't even know they've undone hours of maintenance correcting a bug ?
Even knowing of the danger I sometimes make the mistake and then I have to cross my fingers and hope I'll think to check every single nook and cranny (there are quite a number of them in Excel) for hidden problems I'd already solved but for which I might have undone the correction.
Honestly, even within a workbook, not moving focus when undoing is bad. -
Nick
commented
Maybe I'm just ignorant of these new-fangled computimachines, but it seems to me that this isn't too difficult to fix. After all, analysis of data is what computers do really well, so Excel should be able to analyze its own Undo Stack easily and choose a course of action if it finds a potential conflict.
The first piece of the fix is to have Excel talk to the User, warning him/her about any potential problem with an undo command, and giving the User the option to respond by clicking OK (Go Ahead With Undo), Cancel (Don't Do This Undo) or OK And Don't Warn Me Ever Again You Annoying Piece Of Junk.
The second part of the fix is to change how Excel handles undos.
Let's take a hypothetical Undo Stack (represented in the following table of which I'm hoping the formatting appears correctly for everyone). The following stack shows three actions which affect the four spreadsheets named W1, W2, W3 and W4. The X's represent which spreadsheet is affected by each action and two X's indicate that the action affects two spreadsheets through linking:
=============================================
Undo Stack 1
...............Spreadsheets
...............W1 W2 W3 W4
Action-1 ..X
Action-2 ........X ...X
Action-3 ..............X ...X
=============================================
If the User is in either spreadsheet W3 or W4 and clicks Undo, then Action-3 will be undone exactly as expected in both spreadsheets. But instead, if the User is in W1 and clicks Undo, Excel recognizes that Action-1 is the most recent action for that spreadsheet AND that this action does NOT involve any other spreadsheet. Therefore, Action-1 is undone (and moved to the Redo Stack) leaving Action-2 and Action-3 untouched. But instead, if the User is in W2 and clicks Undo, hoping to undo Action-2, then Excel will analyze the Undo Stack, recognize that Action-2 also affects W3 through the linking and it will then search the Stack to see if there are any later actions involving W3. Since there are, Excel will recognize that there may be a potential cascade effect from undoing Action-2 onto Action-3. Excel then pops up a warning dialog telling the User, "If you click OK, there could be unintended side effects on spreadsheets W3 and W4". This lets the User make the decision about whether he/she really wants to go ahead with the undo.Consider another scenario:
=============================================
Undo Stack 2
..............Spreadsheets
..............W1 W2 W3
Action-1 .X ...X
Action-2 .X
Action-3 .......X ....X
=============================================
If the User is in W1 and clicks Undo, then Action-2 is undone, leaving Action-1 and Action-3 untouched. If the User subsequently stays in W1 and clicks Undo again, then Excel analyzes the Undo Stack and sees that Action-1 affects W2 through linking, and W2 has a later action. Excel therefore throws the warning dialog up on the screen.And Excel would do the same type of analysis and warnings for the Redo Stack.
Since the vast majority of Excel users don't use cross-linked spreadsheets, they would never see this warning dialog because there would never be conflicts. And undo's would occur only in the spreadsheet the User was in at the moment. Power users, who like cross-linked spreadsheets, could select an option to disable this undo self-analysis feature. For them, Excel would function just as it does now.
-
Anonymous
commented
And please, please, please, please, please, ignore zoom view changes, or provide option to do so. I despise having to click back through all of my zoom and pan changes until the point of the actual last effective change made to the spreadsheet. It can be confusing, and is CLUNKY!
-
Anonymous
commented
I guess it comes down to whether the number of users who frequently use Excel in the way Steve K describes is greater than the number of those who don't. I'd be surprised if it was. Regardless, on my Mac I found a way (using the Terminal) to open a second instance of Excel, so that each workbook controls only its own undo stack.
If Microsoft can't accede to our request, it should at least make it easy for the users who wish to opt out to do so. -
Paul Carstairs
commented
Can't believe I'm the only person who realised they had been submitting work laden with errors as I was unknowingly 'undoing' and going back to previous, incorrect, versions of my work - without realising that's what was happening. "Frustrating" doesn't begin to describe the problem. As others have suggested, it needs to be far clearer when you're not undoing work on the spreadsheet that you're working on, but on previous (but currently open) spreadsheets.
-
Virgil Ierubino
commented
I would rather Undo within single workbooks and break formulas/references across others, than Undo universally.
-
Anonymous
commented
Thank you for the update. Perhaps instead there could be some UI enhancements to make it clear where an undo action has had effect? Something like bringing the window with the affected workbook to the front and flashing the modified cell/range? Or a photoshop-style view of the undo stack?