It is always a terrible tragedy when someone loses something important that they have been working on because they did not properly save their document. This happens more often to Excel and Word users than you think!
Fortunately, in the last two releases, Microsoft has added many features to help reduce the chances of data loss due to crashes, power outages, accidental deletions, and more. One feature is called AutoRecover and the other, lesser known feature, is called AutoBackup.
In this article, I’ll show you how to configure both settings to keep your data as secure as possible. I’d also like to mention that the latest versions of Office allow you to save documents directly to OneDrive, which is a good option because you can still access your work even if your hard drive fails.
Automatic recovery is enabled by default and basically automatically saves the document to temporary storage at a specified interval. To view the autosave settings, click File and then Options.
Click “Save” in the menu on the left and you will see an “Autosave” option under the “Save Books” section.
By default, autosave information is saved every 10 minutes. Apart from the interval, Excel must be idle for 30 seconds before the data is saved. In addition, the autosave information is saved in the location specified here for the autosave file. So how does it work? Basically, let’s say you have a worksheet like the one below and you saved it.
Now, suppose I add the following data to an Excel spreadsheet and wait about 10 minutes without saving the document. Since the auto-restore interval is set to 10 minutes, you need to wait at least the same for the data to be backed up.
In my case, I simulated an Excel crash by opening Task Manager and interrupting the Excel process. After that, I opened Excel again and immediately saw the option “Show recovered files”.
If you click on this, it will open a list of Excel files that can be recovered. In my case, it had an original file that only had data in A1 and A2 formats, and also had an auto-recover file that included data that I had not saved to disk.
Clicking on any of the items in the list will open another instance of Excel showing the data in that particular file. Overall, this is a really useful feature for when you haven’t saved all of your work, but Excel exits unexpectedly.
The main disadvantage of this feature is that the autosave data is stored on the same hard drive as your file, so if something happens to your hard drive, everything is gone. You can change the location in the settings above to a separate hard drive or even a network location, which I highly recommend.
Note that autosave information is also automatically deleted by Excel in the following cases:
- You save the file manually or using the File – Save As command.
- You close the file or exit Excel (whether you save the file or not).
- You completely disable AutoRecover or just for the book
Basically, whenever you save a file, it gets rid of the autosave data. Also, if you manually log out of Excel and don’t save the data, it will delete the autosave data. Just keep this in mind when using this function. If you use Excel heavily, I would suggest setting the automatic recovery interval to about 2 or 3 minutes instead of 10.
Another feature that few people know about is AutoBackup. To use it, you need to save the file first to go to the Save dialog box. If you already have a saved Excel document, go to File, then Save As and choose a location. When you do this, the Save As dialog box opens.
In the dialog box, click the Tools button to the left of the Save button. You will see several options, one of which is General. When you click on it, a small window with additional options will appear.
Go ahead and check the Always create a backup box. Click OK, and now when you save the file, the Excel backup file will also be created with .XLK extension. Please note that the first time you do this, the files will be exactly the same. If you edit the original file and then save it again, the backup file will remain the same (original data). However, the third save will add information up to the second save to the backup file.
Typically, the backup file is always one version below the current version. Therefore, if you made multiple changes, saved the file, and then wanted to revert to a version that did not have those changes, you can simply open the backup file.
The file is saved in the same location as the original file and there seems to be no way to change this. This feature does not enhance the security of your document, but it can help in certain circumstances.
Hopefully, enabling these backup and restore features in Excel will save you from losing important data. It’s always a good idea to use a cloud storage (like OneDrive) as an additional safety net in case your computer crashes. If you have any questions, do not hesitate to comment. Enjoy!