Ever had that heart-stopping moment where Excel crashes or you accidentally close your file without saving? It can feel like hours of work just vanished. But don’t panic! There are ways to recover those lost Excel files, especially if you act quickly.
There are also times when you might overwrite important data or make a mistake you can’t undo. In such cases, you might need to retrieve a previous version of your Excel file.
Luckily, Excel has some great features, like Autosave and Autorecover, that help you recover unsaved files or previous versions. Let’s explore the different ways you can get back your lost work.
Recover an unsaved excel file
If your Excel program crashed, or you closed the window without saving, you still have a chance to get the file back. Excel’s autosave feature saves your files at regular intervals. You can use this to find and recover unsaved work. Here’s how it’s done:
First, open Microsoft Excel and click on the ‘File’ tab.
Next, go to the ‘Open’ section on the left panel, scroll down, and click the ‘Recover Unsaved Workbooks’ button at the bottom.
This will open the ‘UnsavedFiles’ folder, where Excel saves your unsaved workbooks. If you have multiple files, use the date and time to identify the one you’re looking for. Select the file and then click the ‘Open’ button.
Alternatively, you can go directly to the ‘UnsavedFiles’ folder using File Explorer.
Open File Explorer and either navigate manually to the following location, or copy the path into the address bar and press Enter:
C:\Users\User_profile\AppData\Local\Microsoft\Office\UnsavedFiles
Remember to replace User_profile with your actual user profile name.
Once the file is open in Excel, you’ll see a prompt to save it. Click ‘Save As’ to save the file to your desired location.
Recover excel files using document recovery feature
If you made changes to a previously saved workbook, and Excel crashed or closed before you could save it again, you can use the Document Recovery feature to get those changes back. This will only work for files that have been saved at least once.
Open any existing Excel workbook or the Excel application. If it opens to the Excel backstage window, select the ‘Show Recovered Files’ option under the ‘Recovered’ section on the left panel.
When Excel opens, you will see a ‘Document Recovery’ panel on the left side, showing all the unsaved files. Pick the file you want to recover.
After the file opens, save it with a different name.
Restore previous version/overwritten excel files using manage versions
Excel only lets you undo changes before you close the app. But sometimes, you need to undo changes or recover overwritten files after you’ve already reopened the file. That’s where the ‘Manage Versions’ feature comes in, as it automatically saves different versions of your file. If you can’t simply undo your work, this is how you can restore a previous version of the file.
Keep in mind that restoring to a previous version means you’ll lose any data you added after that version. There are two ways to restore a previous version: through the Manage Versions option, or through Excel file properties.
Method 1: Using manage versions
First, open the Excel workbook for which you need to restore an older version and then click the ‘File’ tab. Then, click on ‘Info’ on the left-hand pane.
Then, scroll down the Info page, and you’ll see all the previous versions of the file next to the ‘Manage Versions’ button.
If AutoRecover is enabled (it is by default), Excel automatically saves multiple versions at certain time intervals, as long as you have made changes between those intervals. Each version has its date and time, which makes it easy to identify the one you need.
Click on the version you want to restore from the list, and it will open in a new Excel window.
When the autosaved version opens, you will see a bar at the top that says ‘AUTORECOVERED VERSION’. Click ‘Restore’ on this bar to revert to the selected version.
Then, click ‘OK’ on the warning message that appears to overwrite the current file.
After restoring it, save the file with a different name.
Method 2: Using file properties
Alternatively, you can restore an autosaved file using file properties:
First, open File Explorer and find the folder where your Excel files are stored. Right-click on the Excel file you wish to recover, and select ‘Properties’. You can also right-click, choose ‘More Options,’ and then select ‘Restore Previous Versions’.
Next, click the ‘Previous Versions’ tab in the dialog box, select the file version you want, and then click the ‘Restore’ button at the bottom.
Recover auto-recovered and unsaved files from autorecover folder
As mentioned before, AutoRecover automatically saves unsaved and auto-recovered files into a hidden AutoRecover folder. This makes it easy to recover overwritten or accidentally changed Excel files. Here’s how to get to these files.
First, open the Excel program and go to the ‘File’ tab. From the backstage view on the left panel, select ‘Options’.
In the ‘Excel Options’ dialog box, click on the ‘Save’ section on the left. Then, find ‘AutoRecover file location:’ under the ‘Save workbooks’ section and copy the path from the field.
Next, open File Explorer and paste the copied path into the address bar, and then press Enter:
C:\Users\rajst\AppData\Roaming\Microsoft\Excel\
This will open the Excel folder, which contains unsaved and auto-recovered files, or folders named after your Excel files. Check these folders to see if your files are there.
Below is an example of an unsaved Excel file located within one of the folders.
Recover excel files from recycle bin
If you accidentally deleted an important Excel workbook, you can easily recover it from the Recycle Bin. Here’s how:
Open the Recycle Bin on your desktop and find the file you want to restore. Right-click on the file and select the ‘Restore’ option to put it back in its original location.
Recovering overwritten data in excel using onedrive
Cloud services like OneDrive can also help recover previous versions of an Excel file, lost or damaged files. If you save your Excel files on the cloud, you can download previous versions of overwritten or lost files from there. Let’s see how to recover files from OneDrive:
You need to be logged into the OneDrive app with automatic backup enabled, or you need to have backed up your Excel files manually.
Open OneDrive by clicking the OneDrive icon in your taskbar and selecting the ‘View online’ button. Or go directly to https://onedrive.live.com.
This opens the OneDrive website in your browser. Go to ‘My files’, right-click the file you wish to recover, and choose ‘Version History’ from the menu.
This will open a new tab, and you’ll see the current version as well as previous versions in the left panel. You can preview each version by clicking on its date and time.
Click on the version you need and then select either ‘Restore’ to overwrite the current version, or ‘Download’ to save a copy of the selected version.
Configure autosave and autorecover options
The methods above only work if AutoRecover and Auto-save are enabled and set up properly. The AutoRecover feature saves open workbooks at a specific interval, usually every 10 minutes. Changes will only be saved if they happen between those intervals. By default, both these features are enabled. If not, you’ll need to enable and configure them:
Follow these steps to enable and configure AutoRecover:
First, launch Excel and open any existing workbook. Then, go to the ‘File’ tab to get to the Excel backstage view.
Next, click ‘Options’ on the left-hand side.
This opens the ‘Excel Options’ dialog box. Now, select the ‘Save’ tab in the left-hand menu.
Now, make sure the checkboxes next to these options are checked:
-
Save AutoRecover information every _ minutes: By default, it is set to 10 minutes. This will save open workbooks every 10 minutes. You can change the time from 1 minute upwards. Use the arrows in the time box to adjust the time period.
-
Keep the last autosaved version if I close without saving: This helps recover recent files if Excel crashes or closes without saving. When you reopen Excel, an auto-recovered version will be available. This option will only apply to workbooks that have been saved at least once.
In the ‘AutoRecover file location’ field, you can change where auto-recovered files are saved.
If you are working with large datasets, many formulas and charts, set a longer time interval to avoid crashes and unnecessary recalculations.
Create a backup copy of your workbook
AutoRecover and Auto-Save help retrieve unsaved files and automatically save changes. Auto-Backup creates a backup copy of the file, which can help restore previous versions. When enabled, this feature will automatically create a backup whenever you save your file.
This backup copy protects your work if you accidentally saved changes you want to undo, or if you lose your original file.
For example, saving an Excel file after enabling the backup option will create a backup file named ‘Backup of….xlk’ in the same location. If you then make changes to the original file and save it again, the current saved data will be in the original workbook, and the previous saved data will be in the backup file. If you save again, the changes will also be saved in the backup copy.
Follow these instructions to backup an Excel workbook and how to retrieve it:
Go to the ‘File’ tab and choose the ‘Save As’ option from the left panel.
Next, click ‘Browse’ to open the ‘Save As’ dialog box.
In the ‘Save As’ dialog box, go to the folder where you want to save the file and its backup and click the arrow next to the ‘Tools’ button. Then, choose ‘General Options…’ from the menu.
Then, check the ‘Always create backup’ box in the ‘General Options’ dialog box, and click ‘OK’.
You can also set passwords to open and modify the backup Excel file.
Auto-backup settings are specific to the current workbook. If you enable auto-backup while saving (Save As), it will only back up that workbook. If you want to backup another, repeat the steps above.
Rename the file if needed and click the ‘Save’ button.
When you click save, two files will be saved: the original file, and the backup with the prefix ‘Backup of’ (and a ‘.xlk’ extension). When the backup is created for the first time, both files will have the same data.
After the backup, if you change and save the original file (Book 1), it will not update in the backup file (Backup Book 1). If you save again, it will also update the backup file. This means that the backup file is always one save behind the original file.
Open backup excel file
To open the backup file, simply double-click it. You might see a prompt saying that the file format and extension don’t match; click ‘Yes’ to open it.
Alternatively, you can go to the ‘File’ tab, and click ‘Open’. Click ‘Browse’ and navigate to the file location.
From the drop-down list, choose either ‘Backup files (.xlk;.bak)’ or ‘All Files (.)’. Then, select the backup file and click ‘Open’.
If you see a “Do you want to open this anyway” prompt, click ‘Yes’ on it.
You can also rename the file extension of the backup file from ‘.xlk’ to ‘.xlsx’ to open it normally.
Repair corrupt excel files
Sometimes, recovered files can be corrupt or damaged, particularly if they have large amounts of data or complex calculations. In these cases, you can use Excel’s built-in option to repair those files. Here’s how:
First, open Excel and go to the ‘File’ tab. In the backstage view, select ‘Open’ (or press Ctrl+O) and click the ‘Browse’ button.
When the Open dialog box appears, select the corrupted file, and click the small down arrow next to the ‘Open’ button.
Then, select ‘Open and Repair’ from the menu.
Save a time-stamped copy of the excel workbook
Using the auto-backup method, Excel updates a single backup copy each time you save. This means you’ll only have the original file, plus one previous version in the backup file. But, if you need to go further back, how can you access multiple previous versions?
You can use VBA code to create time-stamped multiple backup versions of your workbook whenever you save or close it. This way, you can go back to any earlier version of your file if something goes wrong. Here’s how you can do this:
Open the workbook, go to the ‘Developer’ tab, and click the ‘Visual Basic’ button.
This opens the ‘Microsoft Visual Basic for Applications’ window. Double click on the ‘ThisWorkbook’ object in the project explorer (left-hand navigation panel).
To create timestamped copies when saving, copy and paste the following code into the ‘ThisWorkbook’ code window:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Dim BackUpPath As String
BackUpPath = "C:\Users\rajst\Documents\Project"
ThisWorkbook.SaveCopyAs BackUpPath & Format(Now, "dd-mm-yyyy hh:mm:ss") & " " & ActiveWorkbook.Name
Application.ScreenUpdating = True
End Sub
Replace C:\Users\rajst\Documents\Project with your desired backup location.
To create a backup before closing the workbook (instead of when saving), use the following code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim BackUpPath As String
BackUpPath = "C:\Users\rajst\Documents\Project"
ThisWorkbook.SaveCopyAs BackUpPath & Format(Now, "dd-mm-yyyy hh-mm-ss") & " " & ActiveWorkbook.Name
End Sub
Then, click ‘File’ and select ‘Save…’, or press Ctrl+S.
Based on the code, it will automatically create a separate backup copy of the workbook when you save or close it. The timestamps in the file name will help distinguish between different backups.