Bhubon Costa
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.
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.
Step 1: Create an Auto Backup File
- Firstly, click on the File tab.
- Then, select Save As.
- Click-double the This PC.
- Click on the Tools.
- From the list, choose the General Options.
- Click to mark checked the box named with Always Create backup.
- Press Enter.
- Finally, click on the Save.
- If a file already exists, choose Yes to Replace the existing 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.
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.
- Again, color another cell or make any changes as you wish and save the file.
Step 3: Open the Backup File
- From the folder, open the backup (Backup of Backup Excel) file.
- As a consequence, it will show the result from the last edit before the previous saving.
Read More: How to Find Backup Files in Excel (5 Easy Methods)
Similar Readings
- How to Recover File after Crash in Excel (4 Methods)
- Recover Corrupted Excel Files from USB (4 Quick Methods)
- How to Undo Changes in Excel after Save and Close (2 Easy Methods)
- Recover Corrupted Excel File (8 Possible Ways)
- How to Recover a Deleted Excel File (5 Effective Ways)
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).
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.
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
Step 4: Check the Backup File
- Go to the Final Backup folder.
- As a result, you will get a created auto backup file (Main Worksheet (AutoBack)) containing the date and time.
- Finally, click on the Main Worksheet (AutoBack) file and see the backup results from your previous savings.
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.
