152

Microsoft Excel's undo / redo feature behaves unlike any other program I know. The undo stack seems to be global across all open files, so that undoing sometimes switches to another file and undoes something you didn't want to undo. And if an edit you want to undo was before an edit in another file, you have no choice but to undo the other file.

I am not the first to complain about this - see "Excel's undo madness", about halfway down.

Besides "edit only one file at a time", is there a way to make Excel's Undo apply to the current file only?

I'm using Excel 2003 if it makes a difference.

Hugh Allen
  • 9,820
  • 6
  • 34
  • 42
  • 2
    Please see my comment below. Basically MS has no choice. MS does this for Excel only due to cross-workbook references. The only way they can maintain integrity of formula relationships in that context is to maintain a single undo history. Further, to make it optional is extremely complicated: how does a user choose whether to have a single history or multiple histories? When would a user choose that? They could make it automatic basing on formula dependencies, but that can easily become impossible to manage logically and performance wise. Remember, they need to deliver a product that WORKS. – Mr. TA Apr 19 '16 at 15:48
  • @Mr.TA that is interesting; perhaps you should make it an answer. – Hugh Allen Apr 20 '16 at 02:06
  • 6
    Definitely a really irritating feature, it doesn't help that once you get it wrong it forgets what you were trying to copy and paste. – Adamantus Feb 08 '17 at 13:01
  • 15
    The fact this is still present in `Excel 2016` makes me want to die. – interesting-name-here May 04 '17 at 12:52
  • 1
    can't tell you how many times I have botched a file I forgot was open by holding down ctrl-z and expecting it to return me to initial state of the current document - only to discover I just undid hours of work on something that was minimized – But those new buttons though.. Jan 04 '18 at 02:51
  • 5
    @Mr.TA Even though it might be difficult to implement, I still think it would be worth the effort for Microsoft to modify Excel's behaviour. Naive implementation: A flag in each workbook to track whether it contains any external references to other workbooks. The flags starts out `false`. When a reference is made to another workbook, the flag is changed to `true` and stays that way. For workbooks where the flag is `false` (i.e. in the majority of cases), undo operations affect that workbook only. For workbooks where the flag is `true`, undo retains the current behaviour (cross-workbook). – benrwb Jan 29 '19 at 14:51
  • @benrwb I don't disagree, no software is ever anywhere close to perfect, Excel included (despite being a fantastic product as it stands today). Could MS make it work? Definitely; the question, how much time and money they'll have to spend to get there. – Mr. TA Jan 30 '19 at 14:17
  • 1
    It's 2019 and this is still a thing. It actually makes me want to move to America, get a job at M$ and work my way up the managerial chain until I'm high enough in the right department to force them to fix it. And then fire whoever thought it was acceptable to leave it that way. – Clonkex Jul 25 '19 at 00:38
  • 7
    I came here because this undo is the most retarded thing ever because I am always sick of it undoing changes ACROSS SHEETS instead of only the sheet I am working on. I've hated this for years, and Excel 2019 is no different, still crap. – superuser Mar 04 '20 at 00:36
  • Oh, they COULD fix this MS undo-insanity. Give each document its own stack. Each stack notes when any dependency IS AFFECTED. Undo all you want in the same file, UNTIL you hit a dependency, at which point it pops up a dialog box. "To undo this action, you have to undo the following changes to other file(s): [list] Do you wish to continue?". (Similarly "To redo this action, ...".) (How does Libre Office handle this?) – A876 Oct 01 '20 at 19:07
  • ((There are plans to unify Undo to a single stack across every application, including drawing programs the operating system, across reboots. Hold down repeat-Undo and all of your files go backward and eventually disappear, messages get unsent; Windows uninstalls itself, unformats the hard drive, ejects added-in drives boards and memory, and then the computer packs itself into its box, returns itself to Dell, disassembles itself, reduces itself to raw materials, ..., until it reverts the Big Bang.)) – A876 Oct 01 '20 at 19:31
  • 2
    Still a problem 10 years later... in 2021 Excel 365 – serge Nov 03 '21 at 10:53

5 Answers5

53

The way 'around' it is to open multiple copies of Excel, one per worksheet. This is really annoying, but there is no other way. I have special shortcuts for my main spreadsheets, just to open them explicitly. To do this:

  1. Make a NEW shortcut - right-click on your desktop, New, Short cut.
  2. Browse to the excel program ( C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE )
  3. Create the shortcut and name it
  4. Edit the shortcut, and put the filename, in quotes, after the program, in the shortcut. So you end up with a 'Target' such as:

"C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" "C:\Noiselezz\financial\shared financial\Sales Sheet.xlsb"

Sadly Microsoft really have little clue about how customers actually use their products. The Undo madness is one sign of this - they blindly continue with the most ridiculous method of undo known to humankind. Its not even consistent with other Office products.

Another madness is the DDE system - the best way to slow down a computer - sometimes it takes 12 seconds before the launch even starts, because of DDE. I keep on fixing the registry settings to remove the DDE rubbish, but every time MS update Office, they helpfully 'fix' them.

I have worked in MS, and seen how they think - don't waste your time expecting them to change - that was 10 years ago and they are the same today.

Cadoiz
  • 519
  • 3
  • 10
Jeremy Kenyon
  • 531
  • 4
  • 2
  • 8
    It might be useful to use the "/X" flag to force Excel to start a new process. http://stackoverflow.com/a/18291242/4689766 You could make a generic shortcut and drop worksheets on it to open them this way, or you could even go as far as to edit the file associations to add this or make it the default behavior. – GuitarPicker Sep 17 '15 at 15:46
  • 10
    Note that this breaks copy/paste... – Jens Dec 04 '15 at 08:40
  • 12
    @Jens: FUUUUUUUUUUUUuuuuuuuuuuuuuuuuuuuuu – THE JOATMON Mar 07 '16 at 17:36
32

This issue was discussed and answered on Microsoft Office for Developers Forums on April 14, 2011.

Question posted by Vershner:

I originally posted this in the IT Professionals forum but they told me to post it here because the issue was by design.

When I click undo in the quick access toolbar it undoes the last action in Excel, not the last action in the current worksheet. This is not useful, so I added an undo to the ribbon.

The ribbon is supposed to apply to the current sheet right? No. It still undoes the last action on a different sheet. This is completely stupid. How do I make it undo actions on the sheet I am viewing? I'm using Excel 2010.

Accepted answer posted by Bill Manville (Excel MVP):

I have made the suggestion to the product team that they consider it for the next version.

Below I am giving the useful snippets from the relevant page:

  • Undo works at Application level and will undo changes in the reverse order actions were made.

  • The behaviour of current versions of Excel will not be changed, I am pretty sure. But I will raise the suggestion with the product team for a future version. The question will be does anyone rely on the current behaviour and find it useful...?

Mehper C. Palavuzlar
  • 55,164
  • 49
  • 193
  • 250
  • 33
    Broken by design? That's unfortunate. I still can't see M$'s logic. +1 for the info. – Hugh Allen Jun 06 '11 at 09:46
  • 9
    I was mad about this BEFORE I found out it was by design - but now.... – Simon Feb 10 '14 at 22:49
  • 6
    MS does this for Excel only due to cross-workbook references. The only way they can maintain integrity of formula relationships in that context is to maintain a single undo history. Further, to make it optional is extremely complicated: how does a user choose whether to have a single history or multiple histories? When would a user choose that? They could make it automatic basing on formula dependencies, but that can easily become impossible to manage logically and performance wise. Remember, they need to deliver a product that WORKS. – Mr. TA Apr 19 '16 at 15:47
  • 9
    @Mr.TA: Libre/Apache Office has links between files/sheets and they do undo on a per file/sheet level... just saying (that it is doable, totally doable, if you're not a lazy ass -- which apparently seems to be the case with MS developers). – Erk Feb 07 '17 at 16:13
  • 7
    I can create cross-document links in Word, while also undoing in a single document. Clearly it _can_ be done. Yes, the link may get broken, but that's my fault. I would _greatly_ prefer that behaviour over this window-jumping undo-hell. – JMD Jan 19 '18 at 00:49
  • 1
    "The question will be does anyone rely on the current behaviour and find it useful" -- The question is, why cannot keep "the current behaviour" and add another more useful option? – zipper May 18 '21 at 15:19
  • 1
    Still a problem 10 years later... in 2021 Excel 365 – serge Nov 03 '21 at 10:56
12

Prior to Excel 2016, and some later versions:

If you are currently stuck with a mixed undo history, you can undo on current file only - provided you are willing to lose undo history of other open files.

Simply close other files that are open in Excel, saving them as necessary. This will clear their parts of the undo history, leaving only the actions for the current file in the history. Then you can undo these actions.

That said, I would suggest trying to run Excel as separate processes as described e.g. in this answer or comments. Not only does it take care of the stupid mixed undo history problem, it gives you totally separate Excel windows, one per each file (as opposed to one Excel window with all files open as sub-windows inside it).

ADTC
  • 2,954
  • 3
  • 30
  • 49
  • 5
    This is incorrect. At least in Excel 2016, closing a file completely wipes undo history. – Mr. TA Apr 19 '16 at 15:39
  • 2
    @Mr.TA I don't know anything about Excel 2016. Tested and working 100% fine in Excel 2010, so your blatant *"This is incorrect"* was **totally uncalled for**. When you close a file, only that particular file's actions are removed from the undo history. Actions from other files remain in the history. – ADTC Apr 22 '16 at 09:37
  • 2
    Closing the files you don't want to undo works in 2013 as well. – ScrappyDev May 03 '16 at 16:38
  • 3
    @ADTC: it's not uncalled for to me. I'm using 2016. And now I'm going to locate my asthma spray... – Erk Feb 07 '17 at 16:19
  • 2
    As of today (with all updates applied) this is working for me in Excel 2016. Perhaps they fixed it? – ijprest Sep 25 '17 at 07:48
  • I had tried this in the past and found what @Mr.TA found, but when I tested it today, it worked. But in the past, it was usually after hours of editing that the undo history got wiped for all workbooks after closing one of them. Is it possible that the undo history is just unstable and sometimes works after closing a workbook and sometimes doesn't? Also, there is no solution so far for editing multiple worksheets in a single workbook and undoing .... – vknowles Mar 08 '18 at 21:01
  • This worked for me (using Office 2019 -- desktop version of Office 365) – MD004 Feb 08 '19 at 18:50
  • Using Microsoft® Excel® for Office 365 MSO (16.0.12527.DOGLEAK) in 2020, I encountered a single undo stack between multiple spreadsheets (using no linkage whatsoever). I closed all but one spreadsheet, and the undo stack went away. It looks like "separate undo stacks" is gone forever (unless they someday add it back), and this workaround is probably gone too (unless there's a subtle trick that lets it work). "Separate processes" should work. (If "separate processes" stops working, next try virtual machines? or Libre Office.) – A876 Oct 01 '20 at 19:37
4

You can run each Excel document in a new instance.

Open Excel itself in a new instance

For Excel's main shortcut you could simply add a /x flag after the address in the shortcut's Target field:

"C:\Program Files\...\EXCEL.EXE" /x

It also works for pinned start menu shortcuts in Windows 10 (You need to access the shortcut file in Start Menu\Programs directory)

Open Excel documents in a new instance

This solution works but makes the documents' icon changed which some may find it unpleasant.

To run .xls, .xlsx, .xlsm, etc documents directly in a new instance, you could first create a .bat file with the following content:

@ECHO off

start "" "C:\Program Files\...\EXCEL.EXE" /x %1

Then you could simply associate each file format to your .bat file with Always use... checked.

Keep the ability to open files in the current instance

It's still necessary to open files in the current instance if you want to access the other files' content through formulas or VBA. In these cases, you could first open one file and then drag the other file(s) on the opened Excel window.

You could also use File > Open through the opened file.

Reza Nooralizadeh
  • 71
  • 4
2

If you want to edit two existing XLSX files with two separated histories, there is a work-around, which works - well, mostly...

The first XLSX file you open from its location. For the second XLSX file, you first start Excel 2016 from the start menu, and then you open the second XLSX file through this second Excel window.

Be sure to check through the Task Manager, that there are two separate Excel processes - as you can see it in the screenshot below. If somehow the two files A and B are grouped together under only one process "Microsoft Excel" ..then close all Excel processes and try again.

screenshot

Windows seems to be proud of its German nick name: WinDOOF

gloschtla
  • 111
  • 1
  • 10
  • 2
    If you know a method how to let Windows start one excel.exe file in two or more extra processes, please tell us how! – gloschtla Feb 13 '19 at 19:11