Key Takeaways:
- Excel offers built-in features like Track Changes and Version History to see who made edits and what changes were made to a spreadsheet.
- Third-party add-ins like XLTools and xltrail provide more advanced version control capabilities for Excel workbooks.
- Storing Excel files in cloud services like SharePoint or OneDrive enables version history and collaborative editing features.
- For local file sharing, using a version control system like Git can help track changes, but has limitations with Excel’s binary format.
- Choosing the right approach depends on your specific needs, such as collaboration requirements, data sensitivity, and IT policies.
Table of Contents
Introduction
Collaboration is a common practice when working with Excel spreadsheets, especially in team environments or when multiple stakeholders are involved. However, keeping track of who made what changes and when can be challenging, leading to potential data integrity issues or version conflicts. Fortunately, Excel offers built-in features and third-party tools to help you monitor and review changes made to your workbooks.
Built-in Excel Features
Track Changes
Excel’s Track Changes feature allows you to see all the modifications made to a workbook, including cell edits, formatting changes, and insertions or deletions of data. To enable Track Changes, go to the “Review” tab and click the “Track Changes” button. Once enabled, Excel will highlight any changes made to the workbook, and you can review them in the “Changes” pane.
Pros:
- Easy to enable and use
- Highlights changes in the workbook itself
- Provides details on who made the change and when
Cons:
- Limited to shared workbooks only
- Change history is periodically deleted
- Doesn’t provide a comprehensive version history
Version History (for Cloud-based Files)
If you’re storing your Excel files in the cloud, either on OneDrive or SharePoint, you can take advantage of the Version History feature. This feature keeps a record of all the changes made to the file, allowing you to view previous versions and see what was modified.
To access Version History, open the workbook in Excel Online, click the “Info” button, and then click the “Version History” button. You’ll see a list of all the edits made to the workbook, along with details like the specific cells that were changed and the date and time of the change.
Pros:
- Maintains a comprehensive version history
- Allows you to restore previous versions
- Supports collaborative editing
Cons:
- Only available for cloud-based files (OneDrive or SharePoint)
- Requires enabling the feature by the workbook owner
Third-Party Add-ins
While Excel’s built-in features are useful, they may not provide the level of version control and change tracking required for more complex or sensitive projects. In such cases, third-party add-ins can offer more advanced capabilities.
XLTools
XLTools is a popular add-in that includes a Version Control feature for Excel workbooks. With XLTools, you can:
- Track changes made to a workbook
- Review the change log to see who modified the file and when
- Recover and roll back to any previous version
- Compare versions and highlight modified cells
- Store revision history in a Git repository on your local PC
Pros:
- Comprehensive version control capabilities
- Supports local Git repositories
- Highlights changes at the cell level
Cons:
- Requires purchasing a license
- Limited to Windows platform only
xltrail
xltrail is another add-in that provides version control for Excel workbooks. It breaks down your workbook into sheets, VBA code, and Power Queries, and versions each piece separately. With xltrail, you can:
- Commit and annotate changes to different workbook components
- View version history and diffs
- Use a web interface for version management
Pros:
- Granular version control for workbook components
- Web-based interface for easy access
- Open-source option available (Git XL)
Cons:
- Doesn’t support merge and conflict detection on spreadsheets
- Limited to linear history (no branching)
Using Version Control Systems
While Excel files are binary files, you can still store them in a version control system like Git. However, this approach has some limitations:
- Git can’t effectively diff and merge Excel files since they’re not text-based
- You’ll only see that the file has changed, but not what specific changes were made
- Formulas and formatting may be lost if you try to restore from a text-based format like CSV
To overcome these limitations, you can use tools like Git XL (open-source) or xltrail, which are designed specifically for versioning Excel workbooks.
Choosing the Right Approach
The approach you choose for tracking changes and maintaining version control in Excel will depend on several factors:
- Collaboration requirements: If you’re working on a spreadsheet with multiple people, you’ll need a solution that supports collaborative editing and change tracking.
- Data sensitivity: If you’re dealing with confidential or sensitive data, you may need to keep your files on a local network or self-hosted server, ruling out cloud-based solutions.
- IT policies: Your organization’s IT policies may dictate which tools or services you can use, especially when it comes to cloud storage or third-party add-ins.
- Complexity of the workbook: Simple spreadsheets may only require basic change tracking, while complex workbooks with macros, Power Queries, and advanced formulas may benefit from more granular version control.
FAQ
Q: Can I use Track Changes in Excel Online?
Yes, Excel Online has a “Track Changes” feature that allows you to see and review the changes made to a workbook by different users. To use it, open the workbook in Excel Online, click the “Review” tab, and then click the “Track Changes” button.
To enable Version History in SharePoint, you need to enable Major and Minor versioning in the Library or List settings. Go to the document library or list where you want to enable versioning, click the “Library” or “List” settings, and then select the appropriate versioning options.
Q: Can I use Git to version control Excel files?
While you can store Excel files in a Git repository, Git has limitations when it comes to diffing and merging binary files like Excel workbooks. You may not be able to see what specific changes were made, and you could lose formulas or formatting when restoring from a text-based format like CSV.
Q: Do I need to purchase a license for XLTools or xltrail?
Yes, both XLTools and xltrail are commercial products that require purchasing a license to use their full functionality. However, xltrail has an open-source alternative called Git XL that you can use for free.
Q: Can I use Version History or Track Changes for locally stored Excel files?
No, the Version History feature is only available for Excel files stored in the cloud (OneDrive or SharePoint). Track Changes is limited to shared workbooks, which typically require a network or cloud-based storage solution.
