Excel Hacks Ebook [Electronic resources]

Hack 33 Add More Levels of Undo to Excel for Windows

We all are familiar with Excel's fabulous Undo feature, which enables a user to undo his mistakes. Unfortunately, the default level for this is a mere 16 changes. With the hack in this section, you can change the registry so that you can undo up to 100 mistakes.

When you use Excel's Undo feature, and you reach undo number 16, the first undo is replaced by the 17^th, and so on. Also, as soon as you save your workbook, the Undo Stack is wiped out, and the Undo History is lost. This is because when you press Save, you're actually telling Excel that you are happy with the changes you made, so it decides for you that your Undo History is no longer required.

You might have discovered that at times having only your last 16 changes retained in the Undo Stack is not enough. Instead of living with this, you can change this by editing the registry, something that works only in Windows. To do this, begin by quitting Excel completely. Select Start Run, and in the Open box type Regedit.exe. Click OK. When regedit starts, expand the folder for HKEY_CURRENT_USER. Then expand the Software folder underneath it, then the Microsoft folder, the Office folder, and the 10.0 folder. (This last folder varies for different versions. 10.0 is for Excel 2002.) Expand the Excel folder, and finally, open the Options folder.

Select Edit New DWORD Value, enter the word UndoHistory, and press Enter. Double-click the UndoHistory you just created, and enter a value greater than 16 and less than 100.

Hopefully, 100 undos will be enough for even the most demanding users, though the issue with save clearing the undo stack persists.