Don’t forget to share this post

Collaborating on Excel workbooks can be a double-edged sword. While it can help you work faster and more efficiently, it can also be very tricky. Files can break easily, and innocent human error can be hazardous and very hard to track or fix.

This is a significant issue, primarily if you work with larger spreadsheets or ones that contain valuable information that you can't risk losing. In this guide, we'll start by taking a look at what Excel version control is. Then, we'll take a look at the version control features that Excel has to offer.

What is Version Control?

Version control, also called source control, is a software engineering system that allows you to track files' changes, usually software code. It is crucial for files and documents that undergo a lot of edits and changes. A version control system automates the process of requesting data input, reviewing the submitted changes, and merging it all back into your master files.

What is Excel Version Control?

Excel Version Control 2

Similarly, Excel version control allows you to compare Excel spreadsheets, identify differences, and merge the changes if needed. It helps you keep your spreadsheets well-organized and consistent by automatically handling the communication flows between different team members and the consolidation of the files.

Restore a Previous Excel Version History

What Are the Benefits of Excel Version Control?

Excel version control is essential, especially for Excel users who deal with large spreadsheets or ones that need constant updating as it allows you to:

  • Track Excel changes made to any cell values, formulas, or sheets.
  • Quickly identify changes and inputs made to your Excel files.
  • Review changes made to the spreadsheet and select which ones to accept or reject.
  • Keep track of the data submissions, who submitted them, and when.
  • Keep and restore earlier versions of the Excel spreadsheet.

How to Restore a Previous Excel Version?

Excel offers version history, which only works for files stored in OneDrive or SharePoint in Microsoft 365. Excel version history allows you to keep track of all the different versions of the spreadsheets saved over time. You can view these previous versions and restore any of them if needed.

To restore a previous version of an Excel file:

  1. 1. Open the Excel file.
  2. 2. Go to File > Info > Version History (In different Office versions, it may be "History" or "Browse Version History")
  3. 3. Select a previous version to view it by clicking "Open Version".
  4. 4. Select "Restore" to revert to the earlier version of the Excel file.

How to Turn On AutoSave in Excel?

How to Turn On Auto Save in Excel

To restore a previous version of the Excel file, you need to have had it saved. If you're an Excel user, you have experienced things like forgetting to save your file or accidentally pressing "Don't Save", making you unable to go back. This is why it's best to automate the process of saving versions of your file regularly.

The Excel AutoSave feature for Microsoft 365 subscribers allows you to save your spreadsheets in real-time when you save your Excel files in OneDrive or SharePoint. To turn it on, all you have to do is:

  1. 1. Toggle the AutoSave switch found at the top left corner of your file.
  2. 2. A prompt will ask you to name the workbook if you haven't already and select the cloud location to save it.
  3. 3. Press "Save".

With that, you don't even need to save your edits anymore, as Excel will save your spreadsheet continually.

How to Share your Excel Workbook?

How to Share your Excel Workbook

Excel's co-authoring feature allows you and your team members to work on the same Excel workbook simultaneously. You can see which cells they're working on and the changes they're making as they happen.

  1. 1. Press the "Share" button in the upper right corner.
  2. 2. Name your Excel workbook, if you haven't already, and the location to save it.
  3. 3. Press the button that says "Anyone with the link can edit" to access the link settings. By default, all collaborators will be able to edit the workbook. You can change this here and other settings like setting an expiration date, a password, or preventing downloading the file.
  4. 4. Enter the name or email of your collaborator and, if needed, a message. Then, press enter.
  5. 5. You can also press the "Copy link" button to copy the shareable link to your clipboard and press "More apps" for more options to share your file.

How to Share a copy of your Excel Workbook?

How to Share a copy of your Excel Workbook

Sometimes you may want to share a version of your workbook without giving your collaborators access to your master spreadsheet. In that case, it's best to share a copy of the workbook.

  1. 1. Press the "Share" button in the upper right corner.
  2. 2. Press the "Send a Copy" button
  3. 3. Select whether you would like to send the file as an Excel workbook or a PDF.
  4. 4. Press the "Email as Attachment" button or the share button for more sharing options.

You will need to share copies of your Excel workbook to be able to merge them back later with your main spreadsheet. Note that Excel doesn't offer the capabilities to share specific parts of your spreadsheet. This means that a copy of your entire workbook will be shared.

How to Track Changes in Excel?

Screenshot 2021 04 13 at 08 19 09

While Excel's co-authoring feature allows you to see changes in real-time and automatically create versions of the spreadsheet, it doesn't provide the ability to track and review changes. However, it is still possible to use Excel's legacy Shared Workbook feature, which was replaced with the co-authoring feature due to its many limitations.

  1. 1. Go to Tools > Track Changes > Highlight changes.
  2. 2. Check the "Track changes while editing" box to enable tracking.
  3. 3. Select when you want to start highlighting changes, for whom, and for which cell range, if necessary. By leaving the boxes unchecked, you will be tracking all the changes in your workbook for all collaborators.
  4. 4. Select whether you want to enable onscreen highlighting, which outlines affected cells in different colors for different users. This is only useful for workbooks with few changes as it only provides a rudimentary glance at the changes.

Excel will now highlight changes for you and display a note with basic details regarding a change when you hover over it. Enabling tracking changes in Excel will prevent your workbook from using Autosave. If you have the feature turned on, it will be turned off by default.

How to Review Changes in Excel?

How to Review Accept Reject Changes in Excel

Now that you can identify Excel changes, you can also decide which ones to accept or reject. This is extremely useful if you're dealing with sensitive data and would like to review those updates separately before applying them to your main spreadsheet.

  1. 1. Go to Tools > Track Changes > Accept or Reject changes.
  2. 2. Select when you want to start tracking changes, for whom, and for which cell range, if necessary. If you leave the boxes unchecked, you will be reviewing all the changes in your workbook for all collaborators.
  3. 3. Press "Ok".
How to Accept Reject Changes in Excel

Now, dialog boxes will start showing up whenever changes are submitted. You are given a choice to accept or reject each change, or you can accept or reject them all at once.

How to Merge Versions of a Shared Excel Workbook?

How to Merge Versions of a Shared Excel Workbook

At this point, you shared a copy of your workbook, and you set up tracking and reviewing changes in your Excel workbook, but what happens if your collaborator submits changes? How do you apply those changes (or some of them) to your master workbook?

It's time to take a look at how to merge two different versions of an Excel workbook.

  1. 1. Go to Tools > Merge Workbooks.
  2. 2. Select the Excel file you want to merge with your current one. The workbook selected must be a copy made from the same shared workbook and must maintain change history for a sufficient amount of time to be able to merge them.
  3. 3. Press "Ok".

The changed data from the selected version will be applied to your current one. The changes will be highlighted for you to start reviewing as before.

What Are the Excel Version Control Limitations?

As previously mentioned, the Shared Workbooks feature for Excel version control has many limitations that make it a suboptimal solution, including:

  • The feature was discontinued by Excel and replaced with co-authoring, which doesn't offer spreadsheet version control features.
  • Many of Excel's items and actions are unsupported by the feature, including very basic and crucial ones like creating or inserting tables, inserting or deleting blocks of cells, deleting worksheets, inserting or changing hyperlinks, and much more.
  • Protecting worksheets or the workbook as well as assigning, changing, or removing passwords are also not supported by the feature. This makes it possible for collaborators to submit input to any part of the file.
  • It's not possible to only share a part of the spreadsheet or a specific sheet when sharing a copy of the file.
  • If you turn off the Shared Workbook feature at any point, you will lose your entire change history.

That's why Excel's Shared Workbook feature might be sufficient for personal use cases but is not recommended when dealing with larger teams or spreadsheets. In that case, you should go for a spreadsheet management platform like Layer.

What is Layer?

Layer is an Excel version control system that works on top of Excel files. It helps you keep and manage your spreadsheets all in one place. It also allows you to:

  • Share different sheets or even cell ranges of your spreadsheet with various collaborators without worrying about breaking any references outside the selection.
  • Automate your communication flows and keep track of your data submissions, contributors, and deadlines.
  • Review every single change made to the Excel file and select which ones to merge with your spreadsheet or discard.
  • Eliminate errors caused by directly inputting data into your spreadsheet or manually copying and pasting it from other spreadsheets to your master file. Layer will automatically merge your files and handle all changes accordingly.

Sign up for early access and schedule an onboarding call to get started with Layer right now!

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jun 23 2021, Updated Jul 28 2021