If you often work with large spreadsheet files, you know how frustrating it can be to lose data because of a stupid mistake or because your computer crashed while you were working with your data. Using Excel’s automatic backup feature, you can recover your lost spreadsheet data and continue working as if nothing happened.

A few of the most useful features found in the Microsoft Office suite are underused because they are hard to find. Excel’s automatic backup and recovery option is just such as feature. Read on to learn why you should use Excel’s automatic backup and how to recover lost spreadsheet data.
Why Use Excel’s Backup and Recovery Feature?

It may seem obvious why you should use a backup feature in any program but Excel is a bit different from other application such as Microsoft Word. Many Excel spreadsheets use formulas to compute new figures across its cells. In a large spreadsheet, it is not always easy to see which cells affect other cells. Making one small change can have a domino effect and ruin other calculations in the worksheet.

Suppose you make a change to a cell’s formula and save your work. Later you discover that you made a mistake and can’t remember the exact formula previously used or the exact cell where the formula resided. It would be convenient if you could revert back to the last save state to undo your mistake. Using Excel’s built in backup and recovery option, you can do just that.
Activate Excel’s Backup Feature

The option to activate Excel’s backup feature is somewhat hidden given how useful it is. Begin by clicking on the Excel Jewel in the top left hand corner of your spreadsheet and selecting Save from the menu.

You should now be looking at the Excel Save dialogue window. For our purposes here, we will save the file to the user’s desktop. Name your file in the File Name box. Before you click the Save button, click the arrow next to the button labeled Tools and select General Options from the menu.

Excel will open up a very small window with several options available. The only option we are interested in here is the one titled Always Create Backup. Check this option.

You may notice that there are three other options in this window. You can set a password for both opening (viewing) and modifying (editing) your Excel file. If you set any of these passwords, they will transfer to the backup file as well.

So, use these options with caution because if you forget your password, you won’t be able to gain access to either your main or backup file. You can also set the file to read only. Although Microsoft recommends that you use this option, you may wish to skip over it if you often share your spreadsheets with other people who need the ability to modify its contents.

Click the OK button and Excel will create a backup file in the same location as main file each time you save. Save your file once again to create the first backup file. If we go to the desktop where we saved the spreadsheet you will notice two new files, one with a strange XLK file extension.

The XLK file is your backed up spreadsheet that contains the contents of your file before the last save. If you need to use this backed up file because of a mistake you made or because of a computer crash, click on it and Excel will ask you to verify that you truly want to open up this file.

This step seems a bit strange given that Excel should know what the file is. Simply click on the Yes button and you can restore your spreadsheet to the way it was before the last save.

Using Excel’s hard to find backup and recovery option, you can force Excel to create a backup copy of your spreadsheet. This backup copy contains the data and formulas of your file before your last save.

You can open this file to remove any mistakes you made while editing your main Excel file. If you often find yourself editing large spreadsheets, this backup option is a must.