How to Backup Excel Workbooks [and Save Your Day]

Who hasn’t accidentally saved over their Excel workbook with changes, or was in a hurry to log-off the computer for the day and clicked “Don’t Save” when closing out of Excel before realizing that was the wrong button to click? How many hours of work have you lost, or can you remember what formulas were in your workbook before you mistakenly saved over it? Or do you live in an area where the power often goes out? An Excel backup would be very useful.

These are situations that has probably happened to 95% of Excel users. But, you have File History turned on, right? No? Well, at least your IT department creates nightly backups. But a lot of help that is since you just created the workbook this morning and worked on it all day, so no backup until tonight. Or, what if you are on your home computer with no IT department to manage the backing up of your system?

It’s good that Microsoft has built-in some backup functions in Excel. How do they work? Let’s see.

Excel AutoRecover

AutoRecover is an setting that Microsoft added to Excel. Excel’s built-in AutoRecover options can be accessed in Excel 2016 under File…Options…Save. It defaults to saving the AutoRecover information every 10 minutes, and can be changed to every 1 to 120 minutes.

As well, you have the option of saving the AutoRecover information for a workbook if you close it without saving.

In addition, the location the AutoRecover file is saved to can be changed. Also, you can turn off AutoRecover for each workbook.

The image below shows the relevant information and settings for Excel’s AutoRecover.

excel backup
Excel AutoRecover Options

AutoRecover Timing

If AutoRecover is enabled, the timer starts as soon as Excel is opened. When the set time is reached, Excel checks all open workbooks to determine if any of them have changed. If they have changed, Excel then starts another timer to check if Excel is idle. Every time you make a change to the file, that idle timer is reset.

The idle timer is 30 seconds, but it can be changed via the registry.

There are variations to recover an AutoRecover of a workbook, depending on whether the file was never saved, or if the file was saved at least once. The processes follow below.

File Was Never Saved

If you have never saved the current workbook, and close it without saving, or lose the power to your computer, it could still be possible to recover the file depending on the AutoRecover time settings. If you have the time set to the default 10 minutes and close the workbook without saving before 10 minutes (or even longer depending when Excel was last idle), nothing will be able to be recovered.

To recover the AutoRecover for a file that wasn’t ever saved, go to File…Info…Manage Workbooks…Recover Unsaved Workbooks as shown below.

excel backup autorecovery unsaved workbook
Recovering an AutoRecover of an Unsaved Workbook

File Was Saved at Least Once

If your workbook was saved at least once, and you close it without saving, the process to recover an AutoRecover file is relatively the same.

Go to File…Info…Manage Workbooks and then select the AutoRecover file want to open as shown below.

excel backup autorecover file previously saved workbook
Recovery of an AutoRecover File of a Previously Saved Workbook

Excel AutoSave

A new feature introduce in Excel 2016 and that is only available in Office 2016 with an Office 365 subscription is AutoSave. AutoSave will automatically save your Excel workbook in the background every few seconds as long as it is saved to OneDrive or SharePoint.

Your changes are saved to the cloud, and other uses of the same workbook can see your changes.

excel backup autosave office 365 subscription
AutoSave in Excel 2016 with Office 365 Subscription

To restore a previous version, click on the file name at the top of the Excel window, and then click See all versions. You can then pick which version you want to restore. When you click the version you want to restore, it will open in a new window. If you click the Restore button, the old version window will close and the selected version will become active.

With AutoSave enabled, there is no longer an ability to Save or Save As the file. The only option is Save a Copy.

If you are working on a file that is not saved to OneDrive or SharePoint, AutoSave is disabled for that file. As well, if you are working on a workbook saved in the older Excel file format of .xls, AutoSave will be disabled.

Turning Off AutoSave

The default setting for workbooks saved to OneDrive or SharePoint is to have AutoSave enabled. AutoSave can be turned off, and that setting will stay for that individual file every time you open it. Likewise, if you turn AutoSave back on, it will stay on for that file.

Even if AutoSave is turned off, AutoRecover will still work if it is enabled. Also, if AutoSave is turned on, AutoRecover is off.

AutoSave can’t be turned off as a global setting; it can only be turned off on a workbook by workbook basis.

The Problems With AutoRecover and AutoSave

Having an Excel backup of your workbook is necessary. AutoRecover seems like a good concept – in theory. However, since it relies on waiting for Excel to be idle for 30 once the desired backup time is reached, it could end up never saving a large amount of changes that have been made to a workbook.

As a result, a large amount of your work could be lost if you are busy entering data or formulas, and don’t leave Excel idle long enough to trigger the idle timer.

AutoSave also is a good concept. But it is limited to Excel 2016 that is subscribed to via Office 365. If you have an older version of Excel, or purchased it outright – no AutoSave feature for you.

In addition, AutoSave only works on newer versions of Excel files, and only if they are saved to OneDrive or SharePoint. This does limit those who prefer to save the files to a network location or even on a local hard drive.

Better Backup Free for Excel and Better Backup Pro for Excel

There are two add-in solutions for backing up your Excel workbooks. With the Better Backup Free and Better Backup Pro Excel add-ins, you can ensure that backups of your Excel workbooks are done.

Both versions can automatically save backup copies of your active workbook, and append text and time to your backups. Click here to see a comparison of the two versions.

These add-ins aren’t limited to the number of backups made, won’t delete any backups that are created, and give you flexibility in where and when you backup your Excel workbooks. Check them out.

Leave a Reply

%d bloggers like this: