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]
228 comments
-
Michael
commented
Thanks to this "feature" I'm now running around telling frustrated users that they can't use their own personal laptop because of security issues that BYO devices cause.
-
Corlin
commented
This must be the most unintuitive undo implementation ever. After so many years with Excel it still happens weekly that I press CTRL+Z and I get totally confused when I end up in a different workbook, and it takes 10 seconds until I realize, oh yeah, it was this strange undo implementation.
I have read all the comments on this issue, and I think the message is clear, we are so many users suffering from this daily. And there are so many great suggestions of possible fixes - so yes the code is probably not trivial to change, but from ALL the suggestions posted here, at least one must be possible to implement without devastating effort!
PLEASE, PLEASE, PLEASE!!!!
-
Anonymous
commented
Great2021MS, very good idea!! I will do my part in spreading the word to my colleagues and to reddit.
Jon Catt, that is a wonderful work around in general! I gave it a go and it was easy and works.
The downfall is noted in the link you provided:
"There are some known issues in opening each Excel spreadsheet in its own instance. For example, see <link>You cannot paste any attributes into a workbook in another instance of Excel."I did test this with two files, and indeed the paste options are truncated. This is troublesome for some of my work as I need to be able to paste values & source formatting, column widths, and formulas.
-
stephane roy
commented
Yeah I'm going to agree here that undoing in another workbook without warning the user is unintuitive, bad design, and leads to unknowing data/work loss.
-
Jon Catt
commented
In case it helps anyone, MS have provided a Registry Hack to force Excel to open a new instance by default provided you use the standard Excel shortcut each time. Each instance of course has it's own undo stack.
https://docs.microsoft.com/en-us/office/troubleshoot/excel/force-excel-to-open-new-instanceNote, this is untested by myself currently.
Process would be, start new instance, drag Excel doc into that instance or use the Open/Recent menu.
-
Rob
commented
Bravo to Great2021MS!
-
Anonymous
commented
Steve K stated that; "Even though we don’t have plans to address this soon, we’ll continue to monitor the votes."
I think it's accurate to state that each and every post here represents tens if not hundreds of affected staff members whose day-job does not include chasing a resolution to this issue.
If that's that case, that MS will only take action due to quantity of Votes, and not because it's a flawed "feature" which should be much higher on their road-map to address after all these years, then perhaps we all can send a polite request to our user-bases, including a link to this site, and ask that they Vote to increase the pressure here.
-
Adding my hearty Amen to the comment by "Great2021MS"!
And for the near term, why not simply add the document/sheet name to the item in the undo listing? that simple tidbit of information would solve so much of this problem, looking at it now the undo stack is essentially a cryptological puzzle that is practically impossible to interpret confidently, that might be fun on a puzzle solving level, but im not paid to spend my work time solving recreational puzzles. When i work with several documents open at a time, and find i need to use the undo function, since ive been jumping around between documents its usually impossible to know what sheet the undo item is referring to and so i have to carefully go in and review and close the documents by process of elimination to be able to know what the undo is actually referring to..
if it wasnt bad enough, why record all those zoom and expand collapse actions in the undo stack? those essentially useless entries do little more than throw on loads of more hay hiding the needle... -
Great2021MS
commented
99% of your users don't use cross-sheet formulas and ABSOLUTELY DON'T EXPECT your actual behavior which is MISSLEADING and DANGEROUS. (even if the other workbook become the active window, the undo is already done, and this annoying pop-up stuff looks like a bug for most users that don't understand what just happened, who just lost some data/edit)
I understand that for the 1% of power-users you need to maintain a consistant undo state between worksheets BECAUSE Excel MODIFIES other open sheets references.
But even this is already broken: Because if some user opens the WorkbookB and adds a line, wihtout opening the WorkbookA, this latter WONT BE UPDATED. Thus making it inconsistent with your own logic. WORSE: if you then open WorkbookA, and cancel the added line in WorkbookB, it will break your WorkbookA formula! AWESOME!
If you would have implemented this correctly, you would have stored backreferences in WorkbookB, and forced to reopen the linked files.
So, how to deal with this stuff now it's already messed up?
That's quite simple. Your problem is that an edit in WorkbookB (add a line) will also edit the WorkbookA (change the formula).
You just have to MARK your programmatically-induced edits in other workbooks with a flag (in your example, the undo step of the edition of the formula in WorkbookA).
The user can undo any step that is not marked, using a per-workbook scheme (99% of the users / time).
When it hits a marked step (1% of the time / users), it will PROMPT him that it will undo a step on the other worbook X.
Bonus for you: if the other workbook was closed in the meantime, the prompt will propose to reopen it to make the change, thus keeping at least the consistency through a work session.
With this simple implementation, you have 100% users happy (the 99% that use naively your soft, that are no more bothered with this crazy undo behavior; and the 1% power-users that quite wont notice the difference)
Have a great 2021 year.
-
ExcelIsAnnoying
commented
As a retired "coder", I can attest that such an undertaking would be highly significant.
Add me to the list of those who want separated stacks. (undo per workbook only)As for linked workbooks, I totally get the logic there.
However, not everyone uses linked workbooks and even those who do, may not data changed on "some other open workbook" while working on the focused workbook.I use Excel 2013 for some stuff and of course, this stack PROBLEM affects it.
If I don't need some of the features that 2013 offers, I will do some working with my old and reliable, Excel 2003.
Excel 2003 does not suffer this cross-workbook undo problem.Microsoft should always remember, that for every person who complains about something, there are thousands more who have the same complaint, but just haven't made it public.
In an Enterprise environment, the vast majority won't as they engage the IT department.Again, as a person who has spent a great part of his career as a coder, I am aware of the challenges involved, especially with several teams and many coders working on specific modules. NOT EASY!
But NOT IMPOSSIBLE!
The powers that be should seriously consider addressing this and not just for "future" products, but "fixes" to previous versions (all versions) that suffer this problem.
Perhaps if this is taken on, it could be considered to have an option, even a "button" on the menu that a person can choose if they want Undo to be global or local only. Something that could be switched on the fly.
And yes, the workaround is to save "other" workbooks and close out the other Excel sessions so you are left with only the one you are working on and want undo.
BUT THAT'S A SERIOUS P.I.T.A. (and that's not bread) :)
-
Bill S
commented
All other MS applicaitons keep track of "undos" separately for each document. It is really unfortunate that Excel does not allow for this. The default behavior should be separate Undo stacks per Workbook.
-
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.
