Written by Allen Wyatt (last updated September 15, 2018)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Brenda often works with multiple workbooks open, at the same time. She'll make some changes in one workbook, then start editing a different one. The undo stack seems to apply across workbooks, but when she's working on any given workbook, she would like to undo only things that she did within that particular workbook. Brenda wonders if there is a way for Excel to keep separate undo stacks for each workbook.
This is a yes-and-no answer. The "no" portion is that you cannot do this in any given instance of Excel. If you have multiple workbooks open, the undo stack works across all workbooks because of cross-workbook references you may have created. In such circumstances, you would want the undo stack to function across workbooks.
As for the "yes" portion, there are two ways you can work around this. First, let's say you have five workbooks open and you've been making edits in all of them. However, you want to undo work in only a single one of those workbooks. You can, at this point, close the other four workbooks. Now you can use the undo stack and it will affect only the one remaining open workbook; those you closed will not be affected. You can then, later, re-open the workbooks you previously closed.
The second approach has to do with how you open and work with your workbooks. Normally, opening a workbook opens it within any existing instance of Excel. If, however, you open workbooks in a separate Excel instance, then those in the one instance are "walled off" (so to speak) from those in other instances. This has to do with how Windows allocates memory to applications such as Excel.
The traditional way to open a new instance of Excel is to make sure you use the Start menu (in Windows) to launch Excel each time you want a new instance. (In other words, don't just double-click on a workbook and don't use the Open command within Excel itself.) You can also create a new instance by holding down the Alt key as you start Excel; this causes the program to ask if you want to start a new session of Excel.
The immediate benefit is that you will have separate undo stacks, one for each instance of Excel that you've started. One potential glitch in this approach is that if there are workbooks that are automatically opened when Excel starts (such as the Personal workbook that holds your common macros), then you may see a warning or error message as you start a new instance of Excel. In most cases this won't cause any problem—at least it doesn't with Personal.xls. There may be other behavior differences you notice, as well, as you copy information from one instance of Excel to another.
The bottom line is that this will take some testing on your part. In doing so, you may find that the benefit of having separate undo stacks is outweighed by the drawbacks inherent in using multiple instances of Excel.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2082) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Excel provides two really helpful shortcuts you can use to fill a range of cells, either horizontally or vertically. ...
Discover MoreAfter months or years of naming things (such as cell ranges), you may find your workbook cluttered with a bunch of names ...
Discover MoreHow you add special characters to Excel can differ from how you add them in other Office programs, such as Word. This tip ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-11-14 10:56:25
Michael Disney
Thanks for this information!
2023-09-13 19:15:34
LAMI
Why Office Word has different UNDO Stack for every file but excel does not? This is totally nuts, makes me go crazy.
2023-03-17 11:49:38
Robert Liben
There are so many cool things in Excel, and so many bone-headed things that only a programmer would think of. Undo across multiple spreadsheets is definitely one of the bone-headed things, and most of the work-arounds described here are just impractical.
Microsoft: You could address this (and other) bad ideas by giving users the option to change them as a configuration option. Please!
2023-01-07 18:29:01
Kiwerry
You can call up the undo history by clicking the down arrow next to the "Undo" button (see (see Figure 1 below) ), which will at least show you which action you are about to undo. Unfortunately, it doesn't show the workbook or sheet involved; it only shows the cell address in some of the entries, but not where you pasted something or which cell(s) you cleared. However, if you have some idea of what it is that you would like to undo, seeing this history may be of some help.
A bigger disadvantage seems to be that you cannot select one of the entries in the list and just undo that one; as you move the cursor down the list it shows you how many actions you are going to do.
Despite these disadvantages it beats flying blind.
Figure 1. Undo History (Ver 365)
2022-05-09 22:57:36
R
Alt + Clicking works. But pasting behavior is annoying.
May be a good method for temporary data processing.
2021-10-28 17:37:45
LMH
This behavior is ridiculous. I would like to save the current results from one workbook, then alter the first workbook and save off those results again, often repeatedly. It's crazy to have to close the second workbook every time. Not only that, in my copy of Excel, I can no longer back up in the first workbook once the second one has been closed. Who writes this software???
2021-08-07 03:15:09
Peter
I have been struggling with the ability to open one instance. The help provided in Excel says to use the command line switch /x filename. If you leave the name part off, it seems to open a new instance. I tried the same in a desktop shortcut, but this goes looking for a file named /x.xlsx. If Excel is already open, the second instance is not opened.
Using shift-click just opens an additional window in the current instance.
I can't get Alt-click to perform as described, but I did find that if you activate excel from the Start menu then immediately hold down the Alt key, you get the option of a new instance. But not if you Alt-click.
Opening Excel with the Start menu without Alt just seems to open a window in the current instance - no new splash screen, no new app in Task Manager.
Is there a limit to the number of instances allowed? Excel seemed to have a conniption when I opened a third.
Also, how do I tell if an Excel window belongs to one instance or another? I can tell if there are two instances by looking at the apps in Task Manager and/or seeing the splash screen of the second instance as it loads. I also realise that the list of macros is unique to each to each instance and that I can see the associated workbooks in the Macro dialog and the VBA window. Is there another more intuitive way?
At the moment, I have four Excel windows open, two are named under the second instance, but the other two are not named.
2021-08-05 11:49:25
David B
I tried the second "yes" option by holding "Alt" while clicking Excel in the Start Menu. I did that and opened one workbook, I then did it again and opened a separate workbook. Interestingly, neither instance asked if I wanted a "new" instance. I made one modification in each workbook, but the "undo" was still stacked in both workbooks. What did I do wrong, or did the MS conglomerate decide that they are smarter than their users and "break" that "fix"?
2019-11-21 06:27:46
Willy Vanhaelen
You can very quickly open another instance of Excel by holding down the shift key and simply clicking the icon on the taskbar. This works with most Windows programs that allow to open multiple instances.
2019-11-20 13:05:47
Kimberly
Thank you! My organization upgraded to 2013 MS Office a while ago and I didn't realize 'stacked undo across multiple open workbooks' was a thing until I happen to accidentally notice it. I have two monitors and often have multiple workbooks open. I'm afraid to even think about how many spreadsheets I may have mess up by "undoing" inadvertently on more than 1 at a time before I realized it.
Your suggesion holding the ALT key while opening excel worked for me, although it's frustrating to always need to open a new excel session before opening an existing document. I imagine, once I get used to it, it won't feel like a big deal. Thanks again!
2019-05-15 12:59:45
Tereza
I have MS Office 356 and I am not able to run separate instances. Also, I need to copy between workbooks, therefore, this won't be a solution. If anyone finds some other way to go around this problem I would really appreciate it as it is essential for me to be able to separate the undo stack. It's so frustrating.
Thanks!
2019-03-22 16:09:23
Mel D
I'm used to working with multiple notebooks in different instances for this very reason. To me the undo stack is NOT the lesser of the evils of working in separate instances. However, I’m now in a corporately managed environment and I guess because Excel is hosted on the network, I CAN’T open separate instances so THIS IS MADDENING. At least if you have multiple instances open and you try to move/copy data it’s CLEAR something went wrong. With undo stacked you might not realize that you’ve undone something on another sheet until it’s too late to fix it. Essentially I can now only work on one spreadsheet at time for fear that doing or undoing something will irreparably screw another sheet up.
2018-09-22 09:14:29
Alex B
Most people I have seen using multiple instances just seem to get themselves into trouble, when they find they can't move/copy sheets between workbooks; copy data between workbooks and find they only have the external copy options (not formulas, values etc); try to do a lookup or other formula to a sheet in another instance and can't. Often not realising they are working across instances.
2018-09-17 12:24:07
Neil
Thanks Allen, very helpful! This has been one of the most frustrating things about working with Excel for me. I imagine I am out of luck only wanting to undo actions in a certain tab in a workbook when other tabs have been edited in the interim.
2018-09-17 10:44:33
I have an issue with multiple instances of excel opening all the time, but I don't want multiple instances opening. We haven't been able to figure out why I do end up with multiple instances when I open workbooks double clicking on the workbook name in windows explorer, opening a pinned workbook from the start bar, or opening a workbook being sent to me via email. Any thoughts on what could be causing this or things to check?
2018-09-15 22:32:49
Alex B
Most people I have seen using multiple instances just seem to get themselves into trouble, when they find they can't move/copy sheets between workbooks; copy data between workbooks and find they only have the external copy options (not formulas, values etc); try to do a lookup or other formula to a sheet in another instance and can't. Often not realising they are working across instances.
2018-09-15 08:19:07
Willy Vanhaelen
You can also launch a second or next instance of Excel by holding down Shift and clicking the Ecel button on the taskbar.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments