Advantages of Excel’s Undo, Repeat and AutoRecover Timesavers

In this article

I have previously presented topics and features of Excel dealing with;

• Queries
• Linking and Embedding
• What if assumptions
• Worksheet viewing options
• Excel Tables
• Functions
• Charting
• Formatting

In this post I will be covering some features that will help while working anywhere within Excel.  These techniques will hopefully save you some time or could make Excel easier to use.

Overcoming Mistakes

I write all my posts in MS Word.  I am not a great typist.  I can type very fast, but my accuracy suffers because of my speed.  I am forever using the “Undo Typing” and “Repeat Tying” features in Word.  I can remember, back in antiquity, my typewriter and having to use White Out for corrections.

Who does not make mistakes?  I cannot count the number of times I have made formatting errors on a cell, copied a value into the wrong cell or deleted something I should not have.  Being comfortable with Excel, means that I tend to be in a hurry and my haste produces a lot of mistakes.

Thank goodness the Undo and Repeat Typing features of Word are also present in Excel.

Excel keeps a log or history of all changes you make in a worksheet.  Using the log, Excel can “undo” the last change you have made.  Just click the “Undo Typing” arrow on the Quick Access Toolbar as show in the above picture.  For those who like to use keyboard shortcuts, activate the undo feature by using [ Ctrl + z ].  The keyboard shortcut is the quickest way to undo a mistake, but it has its disadvantage.  Ctrl + z will undo the latest change, but what if you are not sure of the latest change in Excel’s history?  You could undo the wrong change.

To make sure of the latest change, use the “Undo Typing” option on the Quick Access Toolbar.  To see the latest change, hover over the “Undo Typing” arrow.  Hovering will display a message stating exactly what change will be undone.

Next to the Undo Typing option is the Repeat Typing option.  When you undo a mistake, you may change your mind and want to undo the undo.  This is a second chance at correcting mistakes.  Repeat Typing is undoing a previous undo.  The keyboard shortcut for the repeat is [ Ctrl + y ].  Hovering over the repeat will display a message stating exactly what repeat will be in effect.

Excel’s Change Log

Maybe the latest change is not the change you wish to undo?  You can access the change log that will list changes you have made on the worksheet.  To see the list of changes in the log, click the upside down triangle next to the “Undo Typing” option.  A list of changes will be displayed, see the example below.

 

The list will help you locate the change you may need to correct.  The list is ordered from the most recent at the top to the oldest change at the bottom.  Excel’s change log will hold up to 100 changes that were made on a worksheet.  Notice that cell formatting changes are not very detailed.  In the above list there are three instances of column width changes.  The change log does not tell you exactly where the column width changes were made.  In the list, a change in a cell value is very detailed.  The list shows that I entered 7500 into cell G7.

If you know the changes that you have made and can recognize them in the change log, you can make more than one change at a time.  In the example above let us rollback the top four changes.

Select all four changes in the list.  Point to the “Font” change and notice that all four changes have been selected.

At the bottom of the list there is a message stating, “Undo 4 Actions”, as a confirmation of your selection.  To actually make the selected changes, just click on the “Font” change in the selection.  The worksheet has gone back four changes.

What if you decide that the previous four changes were not necessary?  You want to undo the undoing.  Sounds like a case for the Repeat Typing option I mentioned previously.  In the case of multiple changes by selecting more than one change in the change log, you cannot use the “Repeat Typing” option.  Be very careful when you use multiple changes.  Once those changes have been made, they cannot be unmade.

The “Repeat Typing” option can be used in another way.  In my first post, I explained the usage of the Format Painter.  That feature allows you to copy the cell formatting of one cell on to other cells.  “Repeat Typing” can simulate that feature, here is an example:

1. Click on a cell and access Home –> Font –> Bottom Border to a bottom border in that cell
2. Click on a different cell and press [ Ctrl + y ] to activate the “Repeat Typing” option
3. The new cell now has a bottom border as well

You can do this with other cell formatting options such as; bold, italic, fill colors, font colors, etc.  This is a nice little trick, but be careful.  Make sure that you do not change anything until after you make your “Repeat Typing”.  This is a one-shot change.  If you make a cell change that contains multiple changes, say you bold, underline and change the cell to a blue font.  If you do the [ Ctrl + y ], the only change would be the blue font, not the bold and underline.

AutoRecover

Excel 2010 has a better “AutoRecover” feature that can be used when a major emergency has happened, like a power failure when you were almost finished with your worksheet.  You can take advantage of the feature in another way.

As you were working, you realized that 40 minutes ago you had a perfectly functioning set of financial ratios and values.  In those 40 minutes you have made some major changes to some of the ratios and it is late into the evening.  You have saved and re-saved the worksheet a couple of times since then.  What to do?

“AutoRecover” contains a history of the auto saved versions of your worksheet.  You could access that history and open the version from 40 minutes ago.  Your “neck” has been saved.  To access the “AutoRecover” history, select File –> Info.

In the picture above, there are four  versions ranging from 6:62 PM to 7:51 PM.  To recover the version you need, click on that version and it will be open in another window.  Above the Name Box and Formula Bar you will see a warning message; “Autosaved Version” with a Restore button.  Click the Restore button and your previous work is back.  Make sure to save that version.

“AutoRecover” is an automatic feature in Excel.  The default time for saving a backup copy is 10 minutes.  The backup files are in a folder;   C:Userscurrent userAppDataRoamingMicrosoftExcel .  Both the time of backup and the backup folder location can be changed.  To make changes;  File –> Options –> Save.

Learning to use the Undo, Repeat and AutoRecover features in Excel can help you save time.  They will also give you more confidence in using Excel.  Knowing that you have control or your changes will allow you to test out changes in cell formatting and entering values.  Now, you see another side of Excel’s “what if”.  What if you make a mistake or need to access an earlier version of your worksheet.   Excel has time saving feature that can answer those “what ifs”.

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.