How to Auto Backup Excel File (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Simulations, research, visual analytics in charts, data processing, and database management are all done with Excel. It’s commonly utilized in business and in schools. Catastrophic deletions or modifications to Excel files might spoil your day if you don’t back them up. So, in this tutorial, we will show you how to auto-backup an Excel file.

How to Auto Backup Excel File: 2 Handy Approaches

We’ve included a sample data collection in the image below that will be used to demonstrate how to auto-backup in Excel. To begin, we’ll create an auto-backup file using Excel’s General Options. Additionally, we’ll use VBA programs to establish an automatic backup file later.

Sample Data

1. Use Excel’s General Options to Auto Backup an Excel File

We’ll use Excel’s basic feature of General Options to create an auto-backup file in the following section. We’ll color or change the two highlighted cells in our data set, then create an auto-backup file to collect the backup data.

Handy Approaches to Auto Backup Excel File

Step 1: Create an Auto Backup File

  • Firstly, click on the File tab.

Handy Approaches to Auto Backup Excel File

  • Then, select Save As.
  • Click-double the This PC.

Handy Approaches to Auto Backup Excel File

  • Click on the Tools.
  • From the list, choose the General Options.

Handy Approaches to Auto Backup Excel File

  • Click to mark checked the box named Always create backup.
  • Press Enter.

Handy Approaches to Auto Backup Excel File

  • Finally, click on the Save.
  • If a file already exists, choose Yes to Replace the existing file.

Handy Approaches to Auto Backup Excel File

  • As a result, a backup file with the name “Backup of” and the current worksheet name will be produced, as seen in the figure below.

Handy Approaches to Auto Backup Excel File

Step 2: Update Any Data in the Main worksheet

  • To make a change in an Excel file, highlight the two following cells first.
  • Save the file by clicking the Save icon or by pressing Ctrl + S.

Handy Approaches to Auto Backup Excel File

  • Again, color another cell or make any changes as you wish and save the file.

Handy Approaches to Auto Backup Excel File

Step 3: Open the Backup File

  • From the folder, open the backup (Backup of Backup Excel) file.

Handy Approaches to Auto Backup Excel File

  • As a consequence, it will show the result from the last edit before the previous saving.

Handy Approaches to Auto Backup Excel File

Notes. Now, whenever you save a modification to the current spreadsheet, the Excel backup file is changed to match the state of the spreadsheet before the latest save. The .XLK extension is used for the Excel backup file, which is saved in the same directory as the source. It’s worth noting that the first time you save, the files will be identical. But when you change and save the file for the third time, it will show the results from the second save. In general, the backup file is one version behind the current version.

Read More: How to Find Backup Files in Excel

2. Run a VBA Code to Auto Backup an Excel File

VBA has a highly efficient process for creating an automated backup file. You may retrieve your backup data from your last save by using VBA codes. Follow the outlined steps below to apply the VBA codes.

Step 1: Create a Folder

  • Firstly, create a folder (AutoBack) with three files: one folder (Final Backup), a VBA worksheet for writing VBA codes (Auto Backup Creator), and your current worksheet (Main Worksheet).

Handy Approaches to Auto Backup Excel File

Step 2: Create a Module

  • Open the Auto Backup Creator file.
  • Press Alt + F11 to open the VBA Macro.
  • Click on the Insert tab.
  • Then, select the Module option to create a new Module.

Sample Data

Step 3: Run the VBA codes

  • Paste the following VBA codes.
  • Finally, press  F5  to run the program.
Sub AutoBackup()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Insert the file path/location of your current workbook
Workbooks.Open Filename:="C:\Users\Admin\Desktop\Files\AutoBack\Main Worksheet.xlsx" _
, UpdateLinks:=3
'Insert the file path/location of your Backup file workbook
Workbooks("Main Worksheet.xlsx").SaveAs Filename:="C:\Users\Admin\Desktop\Files\AutoBack\Final Backup\Main Worksheet " & "(AutoBack) " & Format(Now(), "(yyyy-mm-dd hhmm)") & ".xlsx"
Workbooks("Auto Backup Creator.xlsm").Close SaveChanges:=True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Sample Data

Step 4: Check the Backup File

  • Go to the Final Backup folder.

Sample Data

  • As a result, you will get a created auto-backup file (Main Worksheet (AutoBack)) containing the date and time.

Sample Data

  • Finally, click on the Main Worksheet (AutoBack) file and see the backup results from your previous savings.

Sample Data

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


I hope this article has given you a tutorial about auto-backup Excel files. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support. Please contact us if you have any questions. Also, feel free to leave comments in the section below.

Related Articles

<< Go Back to Recover Excel File | Excel Files | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF