File versioning refers to the creation and management of multiple versions of a file, such as an Excel spreadsheet. Versioning can be useful for tracking changes over time, collaborating with others, or recovering older copies if needed. There are a few ways version histories can be saved in Excel:
- Excel’s AutoSave and AutoRecover features automatically save versions periodically as you work.
- The Save As command allows you to manually save new versions.
- Version histories can be tracked through OneDrive and SharePoint document libraries.
- Third party tools also exist to help manage and compare versions.
Having access to previous iterations can provide a helpful record of how a spreadsheet has evolved. This allows you to view, restore, or retrieve an older version if ever necessary.
AutoSave Versions
Excel automatically saves versions of a file at regular intervals while the file is open. This feature is called AutoSave and it allows you to restore an older version of your file if you need to undo recent changes or recover lost data.
AutoSave versions are stored temporarily on your computer and act as a rollback point to an earlier state of the file. The AutoRecover setting under File > Options controls how often AutoSave creates a version, with the default being every 10 minutes.
To restore to an AutoSaved version:
- With the file open in Excel, go to File > Info and click on Versions.
- This will display a list of AutoSaved versions, with time and date stamps.
- Select the version you want to restore and click Restore.
- The version will then open in a new Excel window, leaving your original file unchanged.
AutoSave can help recover changes within the past hour or so. But for older versions, you’ll need to use one of the other options covered next.
Manual Versions
One way to manually save different versions of an Excel file is by using the ‘Save As’ function. This allows you to save multiple iterations of the same file with different names. For example, you could save your original file as ‘Report.xlsx’, then save an updated version as ‘Report_v2.xlsx’.
When saving manual versions, it’s important to establish a naming convention that allows you to easily identify and organize the different iterations. Some common practices are appending the version number to the end of the filename (Report_v1.xlsx, Report_v2.xlsx), or adding the date (Report_March1.xlsx, Report_March15.xlsx).
The main limitation with manual versions is that it relies entirely on the user to deliberately save multiple versions. If you forget or neglect to use ‘Save As’, you could end up overwriting previous iterations that are then lost. There is also no simple way to view or compare changes between versions other than opening the files side-by-side.
According to https://www.valispace.com/how-to-do-version-control-in-microsoft-excel, manual versions “quickly become chaotic and near impossible to manage as you end up with many near identical copies.” So while saving manual versions is better than nothing, more robust version control features are required for complex documents with frequent changes.
Restore Previous Versions
Windows has a built-in feature to restore previous versions of files called System Restore. It works by taking automatic snapshots called “restore points” and saving them as shadow copies of your files. This allows you to go back to a previous version if your current file becomes corrupted or changes are made that you want to undo.
To access previous versions of an Excel file in Windows 10 and 11:
- Open File Explorer and navigate to the file location.
- Right-click on the file and select “Properties”.
- Click the “Previous Versions” tab.
- Select a restore point date and click “Restore” to open that version of the file.
This will retrieve an older version without overwriting your current file. You can access restore points up to one month old, depending on your Windows settings. It provides a simple way to recover from unwanted changes or accidental saves over your original Excel file.
OneDrive Version History
OneDrive automatically keeps a version history of files stored in it, allowing you to restore previous versions if needed. OneDrive will save versions of a file either over time or when you manually save edits, depending on your OneDrive settings.
To view the version history of an Excel file stored in OneDrive:
- Open the Excel file in OneDrive.
- Click the “File” tab and select “Info.”
- Click “Version History” on the right side.
- This will open the version history panel showing all previous saved versions of that Excel file.
To restore an older version:
- Open the version history and click the ellipses next to the version you want to restore.
- Select “Restore” to replace the current file with the previous version.
OneDrive’s version history allows recovering older copies of a file in case unwanted changes are made. It provides an automatic way to revert to a previous state of an Excel file stored in OneDrive. The number of versions retained depends on the OneDrive storage plan.
SharePoint Version History
SharePoint has built-in version control for files stored in SharePoint document libraries. This allows users to view previous versions of a file and restore an older version if needed.
To leverage SharePoint’s version history for Excel files:
- Store the Excel file in a SharePoint document library instead of locally on your computer or a network drive.
- Open the Excel file from the SharePoint document library to make edits and save it back to SharePoint.
- Each time the file is uploaded or edited and saved in SharePoint, a new version is created in the version history.
- To view version history, open the Excel file in SharePoint and click the “Version History” button in the ribbon.
- Here you can see all previous versions, view differences between versions, and restore an older version if needed.
The number of versions retained over time depends on the version settings for that SharePoint document library.
Storing Excel files in SharePoint leverages its built-in version control to easily retrieve older versions of the file when needed.
Third Party Tools
Dedicated version control tools like Git can also be used for Excel files. These tools are designed specifically for managing different versions of files and enable more advanced version control features like branching and merging.
Some benefits of using third party version control tools include:
- Ability to store file history remotely in a repository
- More seamless integration with collaborative workflows
- Advanced features like branching and merging
However, there are also some downsides compared to the built-in options in Excel and cloud services:
- Additional setup and learning curve
- Less user friendly interface
- Overkill for basic version control needs
Overall, dedicated version control systems provide more power and flexibility but also more complexity. They may be overkill for individual users who just need occasional version history for Excel files. But for teams collaboratively editing Excel files, they can enable more robust version control and collaboration workflows.
Which Option is Best?
There are a few key options for version control in Excel, each with their own pros and cons:
AutoSave Versions
AutoSave versions are enabled by default in modern versions of Excel, making this the easiest option. It allows you to restore previous versions of a file over the past 30 days. However, you have no control over when versions are saved, and can only restore previous versions, not compare versions side-by-side (xltools.net).
Manual Versions
Saving versions manually gives you full control, but takes more effort. You must remember to save versions with distinct names yourself. This also allows side-by-side comparison in Excel. But there is no tracking of changes between versions (valispace.com).
SharePoint or OneDrive Version History
Collaborating via SharePoint or OneDrive allows you to see the full version history of a file, compare versions, and restore previous versions. However, the version history can get cluttered when there are many edits by multiple users. And you must store your files in SharePoint/OneDrive (microsoft.com).
Third Party Tools
Dedicated version control systems like Git integrate deeply with Excel for advanced version tracking and collaboration features. However, this requires learning a new complex tool. Most third party options are paid as well.
For individual use, AutoSave or manual versions often suffice. For team collaboration, SharePoint/OneDrive version history is likely the easiest and most integrated option. If advanced version control is needed, third party tools provide the most flexibility at an increased cost.
Limitations
While retrieving previous versions of an Excel file can be useful in many cases, there are some limitations to be aware of:
Versions are only available if file saving and backup features were set up properly initially. For example, AutoSave must have been enabled and configured to save versions regularly. Manual versions also need to have been consciously saved by the user.
It is not possible to retrieve older versions if the latest version of the file has been overwritten or if the storage location holding previous versions was lost or deleted. For example, if an Excel file was saved to a hard drive that then crashed, the versions would be unrecoverable.
There are also limits on the number of versions retained depending on the storage method. For example, OneDrive and SharePoint only store a limited number of previous major versions of a file.
Overall, while version history can serve as a safety net, proper regular file backups are still recommended as the most reliable way to protect important Excel data from loss.
Conclusion
In summary, there are several options available for retrieving older versions of Excel files:
AutoSave and manual saving allow you to revert to recent previous versions. Restore Previous Versions can recover older backups on your PC. OneDrive and SharePoint have built-in version histories if you save your files there. Third party tools like Git can also track changes and save versions.
Having a proactive version control process is crucial for Excel files to avoid losing important data or changes. Any business relying on Excel spreadsheets should implement a version control system like the options discussed here. Saving files in OneDrive or SharePoint provides automatic protections and histories. For more control, using a dedicated version control system like Git is advisable for managing multiple collaborators and workflow approval processes.
The optimal version control solution depends on specific needs and workstyles. But some form of Excel backup is essential. By proactively tracking changes and accessing previous versions, teams can work confidently without worrying about maintaining data integrity.
