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]
239 comments
-
Nick P.
commented
I occasionally use linked workbooks and more often use multiple standalone workbooks. I have NEVER needed the undo function to apply to anything but the active workbook.
-
Jesse Thompson
commented
How many people hate this behavior vs how many people use linked workbooks? I personally have never used a linked workbook and cannot stand this behavior. Only forced to use Excel through work. Other options out there now.
-
Anonymous
commented
This need to be addressed!
It is unacceptable to not be able to work in two different excel documents at the same time.
As it now works you can only undo in the last document you made edits in.1. Quick fix: By default start new instances of Excel unless the user explicitly express that they need the cross document undo stack.
2. Make it super clear whenever an undo action affects another document that the one that is active and has the focus.
3. Make it super clear that when you make a change in one document that this change will prevent you from undoing previous changes in other workbooks.
4. Make it possible to "split" an instance of Excel and bring over a document including the undo stack for it to a new Excel instance.
-
Nils Thommesen
commented
I am astonished that Microsoft is letting Excel and other products deteriorate in the way you do by not fixing issues like this. This is one incredibly annoying undo-implementation that is causing problems for a huge number of people, I believe. As many people in previous comments have told you, there are many ways you can fix this issue, but I believe the truth is that unless enough users move away from your product (e.g. by using Google Sheets instead), you are very likely *not* going to fix "old issues" like this. I also believe that the amount of time spent by your developers to fix issues like this, will remain too low to actually improve the product.
-
Anonymous
commented
All we need is an option to link or unlink workbooks - how hard can that be? As someone regularly using multiple workbooks with text table not figures, keeping on top of what changes are made in which workbook when undoing can be very trying in stressful situations. Get it sorted please.
-
Zeke Porter
commented
Just make sure you're all voting. Those numbers are what Microsoft are looking at.
-
Mats Samuelsson
commented
I had no problem with Undo stacks prior to Office 2016 and Office365.
I had a fully developed administrative application in Office 2010 and it worked excellently.
I have been forced to rewrite code in most of it since MS changed to SDI and introduced the ribbon but still it's not working properly. The Undo stack problems came at the same time, probably as they separated the interface. A once "Excellent" program has become a terrible inconvenience we regrettably have to maintain! -
Jon Catt
commented
Actually Ben, there are comments on this thread dating back to 2015, and indications that the "feature" dates back to at least Office 2003/XP. This is far from a new issue, and not only contained to O365 versions of the software. I wish it were, they could simply roll-back...!
-
Ben
commented
Ok we see your explanation as to why this is happening, but why WAS THIS NEVER A PROBLEM UNTIL OFFICE 365?!!! Nobody ever had this issue until your ******** *** subscription service.
-
Becket_9
commented
What I don't understand is that I have never had this problem before. But as I was given my fourth work computer since telecommuting started, I am now having the undo across multiple workbooks problems. And, my IT has never had anyone else in the agency and its sub-agencies has this problem. So what have we been doing right since the original update to 2016 that is suddenly wrong as of my computer change on 3/15/2021. My third computer was on 2/4/2021 and I didn't have the problem back then. So I am totally confused right now and seriously annoyed.
-
Anonymous
commented
Obviously, you should undo everything an operation does.
* If you undo in WB-A then undo all effects of that action.
* If you are in WB-A and press undo, and the next action to undone is n another WB, then tell the user about this fact, and ask them if they want to switch to the other WB and proceed with the undo.
* As long as there are no action in the undo stack that affects other WBs, then just do the undo in the active WB since that's what the user expects. -
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.
