How to Automatically Save a Backup Copy of an Excel Workbook

Microsoft Excel Tips & Tricks
Microsoft Excel backup options, Excel AutoBackup to backup workbooks

How can you automatically create a backup copy of important Excel workbooks? Accidental deletions or changes to a file can ruin your day–if you don’t have a backup of your original file. Excel’s AutoBackup feature helps keep your data as safe as possible by automatically saving a previous version of your key spreadsheets to a backup file.

In this post, we’ll look at how to create an Excel backup copy of a workbook every time you save the file or follow the steps in the video below:

Microsoft Excel backup options, Excel AutoBackup

Follow these steps to set up an automatic backup for your key Excel workbooks:

  1. With the file open, choose File > Save As.
  2. Click “More options…” under the save location.
  3. Choose the Tools button then General Options in the Save As dialog window.
  4. Check the box next to “Always create backup
  5. Click OK.
  6. Click Save in the Save dialog window.

Now, every time you make a change in the original spreadsheet and save it, the Excel backup file is updated to reflect your spreadsheet before that last save. The Excel backup file is created with a .XLK extension and is stored in the same location as the original. Note that the first time you save, the files will be exactly the same. If you make edits to the original file and then save it again, the backup file will still remain the same (the original data). The third time you save it, however, the backup file will be updated with information up to the second save. Basically, the backup file is always one version behind the current version.

Working with your Excel Backup Files

Microsoft Excel backup options, Excel AutoBackup

The AutoBackup updates the backup copy automatically, and so, it’s up to date and one version behind the original and other users don’t even need to know about the file. The backup is identified by the .XLK extension and “Backup of“, and then the file name. If you’re not seeing the file extension from the Windows Explorer, choose the View tab in the Ribbon, and then select File name extensions. Although the extension makes it a little bit easier to locate the backup file, it should be easy for you to find the file because they are named differently. And then if you need to, you could actually open up this backup file which would reflect the changes prior to the last time the file was saved. If you open the Excel backup file, you may be prompted to confirm you want to open the file.

Keep in mind, this AutoBackup process is not a substitute for a regular system backup, whether it’s by your IT services, your files are saved in OneDrive or another cloud service, or you work with an automated backup service. However, with the Excel backup feature, you can feel a little bit more comfortable making changes and working actively with your Excel workbooks.

To be more productive with Microsoft Excel, check out these additional shortcuts, tips and techniques at TheSoftwarePro.com/Excel.

By Dawn Bjork, MCT, MOSM, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Software Consultant

Previous Post
Microsoft Word: How to Stop Annoying Automation in Word
Next Post
10 Handy Zoom Shortcuts

Related Posts

Menu