When Undo first appeared in Excel, you could only undo the most recent operation. Today, Excel offers an Undo stack that will let you undo the previous 100 operations. But how do you actually go back to a certain point in time?
USING KEYBOARD SHORTCUTS
Xerox debuted the Ctrl+Z shortcut for Undo in 1974. When Excel was released for Apple computers in 1985, it used Cmd+Z for Undo. Later, Windows 3.1 introduced Alt+Backspace as the shortcut for Undo. Today, you can undo with Ctrl+Z or Alt+Backspace in Windows or Cmd+Z on a Mac.
When I’m performing one of my live Power Excel seminars, I always ask attendees about the shortcut they use for Undo. Almost universally, the answer is Ctrl+Z. Only once has anyone mentioned Alt+Backspace, so Ctrl+Z/Cmd+Z is clearly the norm. In fact, the second most-popular answer is from people who don’t know Ctrl+Z but make a curved motion with their hand, indicating the Undo icon in the Quick Access Toolbar. That icon, along with its associated drop-down menu, is the key to undoing back to a certain point in time.
If you look closely at the Undo icon in the Quick Access Toolbar, you’ll see a drop-down menu next to the icon. Opening that menu will give you a list of up to the last 100 operations that you have done in Excel.
REDO
I always mention the Undo drop-down menu, but recently someone asked how to actually use it. “Let’s say that I know I made a mistake roughly 20 steps ago. I open the Undo menu, go back about 20 steps, and undo to that point. But what if I went back too far? How can I undo the Undo?”
The Redo command (Ctrl+Y or Shift+Alt+Backspace in Windows; Cmd+Y on a Mac) is mostly grayed out in the Quick Access Toolbar. If you undo 20 actions, however, those 20 actions will be listed in reverse order in the Redo menu. You can undo all or part of the most recent undo by selecting from the drop-down menu next to the Redo command.
The problem with Redo is that you can’t undo several steps, make a correction, and then redo the other steps. For example, let’s say you type 1, 2, 3, 4, five, 6, 7, 8, 9, 10. You can use Undo back to the point where you typed “five.” But as soon as you correct your mistake and type the number 5, the Redo history of typing 6, 7, 8, 9, 10 is lost. In this simple example, rather than using Undo, it would be simpler to click in the cell containing the mistake and correct it.
There are also instances where you can use the Repeat command instead of Redo. It appears as the third icon from the left in Figure 1, though it isn’t usually on the Quick Access Toolbar. Say that you have changed the font color of a cell to a particular shade of red. If you select other cells and press F4 or Ctrl+Y (or Cmd+Y on a Mac), Excel will apply the last operation to the newly selected cells.
GOING BEYOND 100 LEVELS
What if you need to go back beyond the 100 levels of Undo? Go to File, Info, Manage Workbooks. Up to five AutoRecover versions will be available. You can open an AutoRecover version and switch between the current and AutoRecover versions to figure out what might have changed.
UNDO CHALLENGES
There are a couple of areas where Undo runs into difficulty. For instance, the Undo command won’t let you undo a save. With Excel 2003 or earlier, in fact, the Undo stack would clear entirely after every save. Starting with Excel 2007, you can at least undo operations that happened before the save.
If you frequently use VBA Macros, you’ll notice that if a macro makes any change to the worksheet, the entire Undo stack is lost.
The Undo feature also can become irritating if you have multiple workbooks open. Say you’re working in IncomeStatement.xlsx and BalanceSheet.xlsx at the same time. You make some changes in the income statement file and then Ctrl+Tab to switch to the balance sheet. If you press Ctrl+Z to use Undo, Excel will switch back to the income statement and undo the last change there. A passionate request at Excel.UserVoice.com is pleading with Microsoft to maintain a separate Undo stack for each workbook. (As I write this, the idea has 800 votes, making it one of the leading requests on the site.)
Try this experiment: Open two workbooks. Type 1, 1, 1 in the first workbook. Type 2, 2, 2 in the second workbook. Keep alternating between the two workbooks, typing 1s or 2s until you have 100 items in the Undo drop-down menu. Close the second workbook. Excel removes all of the actions that happened to the second workbook from the Undo stack. You’re left with an Undo stack of 50 items, all of which are things that you typed in the first workbook.
MEMORY IMPLICATIONS
The first worksheets only offered one level of Undo because of available memory. At that time, someone noticed that the spreadsheet was storing the contents of the spreadsheet and the Undo version of the spreadsheet. One person reported that their IBM PC’s available memory dropped from 166K to 60K because of Undo and suggested disabling Undo. Given that the IBM PC had 512K of memory, you can appreciate how losing 100K of available memory was a serious issue.
Today, your PC typically has 4GB-16GB of memory, so giving up some memory to Undo isn’t a concern. But if you frequently perform operations such as deleting column K with 515,000 rows of data, you potentially will have a memory performance hit in Excel. Imagine 10 operations that delete 500K cells each. Excel has to store those 5 million values until you perform another 100 operations.
There is one setting in Excel Options to disable Undo for Data Model operations.
And if you find you really want to live with only 10 or 20 levels of Undo in order to free up memory, Microsoft explains how to modify the undo limit by editing the Microsoft Windows registry.
SF SAYS
Having 100 levels of Undo isn’t so useful if you have to manually redo 99 operations.
July 2019
