How to Minimize the Risk of Losing Your Work in Excel

Most spreadsheet users would love to have a dollar for every time something has gone awry with an Excel spreadsheet. Sometimes it’s a power outage, or a computer crash, or simply closing the wrong document without saving. Fortunately in all versions of Excel you can hedge against this risk, but anyone using Excel 2010 and later will have even more options available.

The first risk management step will take us into Excel’s Options dialog box:

1. Choose File (or click the Office button in Excel 2007)

2. Choose Options.

3. Choose Save.

4. Change the Save AutoRecover Information Setting to every 1 minute.

5. Click OK.

The default for Save AutoRecover Information is every 10 minutes. You may have noticed that Excel sometimes resurrects copies of any documents that you had open when Excel closed unexpectedly. The reality is Excel doesn’t save AutoRecover versions of your spreadsheets every 10 minutes, nor will it save a copy every 1 minute. However, I have found that ratcheting this setting down does increase the frequency with which these back-up copies are made.

You can access these AutoRecover files in a couple of ways. In all versions of Excel a Document Recovery task pane will appear when you launch Excel after a crash. In Excel 2010 and later you can sometimes recover spreadsheets that you closed without saving. To do so, look for the Recover Unsaved Workbooks command at the bottom of the Open (or Recent) menu. You can also choose File, Info, and then choose Manage Workbook (or Manage Versions). From there you’ll see a Recover Unsaved Workbooks command. This puts you into a folder that contains any documents you closed without saving over the last couple of days. Bear in mind that you must have worked on the document long enough for Excel to have created an AutoRecover version, so this feature isn’t fool-proof.

As you work in Excel, time stamps will appear on the Info screen next to the Manage Workbooks (or Manage Versions) button. You’ll see up to five AutoRecover versions appear in this space. These enable you to go back to how a workbook looked earlier in your work session. Bear in mind that these versions are deleted when you close the document. Any unsaved documents tend to be available for two or three days before they’re automatically deleted.

Excel’s Save As dialog box also offers a hidden feature that can create an automatic back-up of key documents. Any changes to the AutoRecover settings affect all future documents, but you’ll have to change this next setting on a workbook-by-workbook basis. In all versions of Excel click Tools in the Save As dialog box. It generally appears to the left of the Save button, but can sometimes appear elsewhere in the Save As dialog box in older versions of Excel. Choose General Options, click Always Create Backup, and then click OK. Save the workbook in the usual fashion. Going forward when you save your spreadsheet the previous saved version will be renamed with a Copy Of prefix and a .XLK file extension. All backups reside in the same folder as the original document. If you compulsively press Ctrl-S to save your work every two minutes then your backup will only be two minutes old, but this is another way that you can have a hedge against disaster in your spreadsheets.

Source: http://www.cpapracticeadvisor.com/news/12404787/how-to-minimize-the-risk-of-losing-your-work-in-excel

DAVID RINGSTGROM, THE EXCEL ADVISOR ON MAR 26, 2018