How to Auto Backup Excel File (2 Easy Methods)

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.


Download Practice Workbook

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


2 Handy Approaches to Auto Backup Excel File

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 with 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 (5 Easy Methods)


Similar Readings


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"
ActiveWindow.Close
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

Read More: How to Backup Excel Files to a Flash Drive (4 Useful Methods)


Conclusion

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.

We, the Exceldemy Team, are always responsive to your queries.

Stay with us and keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo