Search for ‘spreadsheet hell’ and you’ll see that you are not alone – collaboration and control in Excel is not a solved problem. In fact it is a disaster.
You love Excel. We love Excel. The world loves Excel. But like many relationships it comes with frustrations and heartache.
No tool is perfect – but 750 million users can’t be wrong!
There are big issues that have come from bad testing, bad controls, and bad visibility in Excel spreadsheets. Never forget JP Morgan’s legendary $6 billion trading loss, which was apparently due to an Excel error.
Collaborating on Excel workbooks is difficult – it helps you work faster and more efficiently, but it’s also tricky. Files can break easily, and human error can be hard to track or fix.
That’s why version control for Excel makes so much sense. Version control is the practice of tracking and managing changes to documents. It is the standard practice in software development and is growing in popularity in data management.
We review the 5 ways folks are trying to implement version control in Excel
1 Use Something Else
Go on any developer forum and ask about Excel, you’ll get the same response: there are better tools that already have version control. It doesn’t matter if your workforce already knows Excel and all your internal processes revolve around Excel, they will be sure that you should use something better.
Sure there are great tools out there to solve lots of data problems, but telling people to just stop using a tool that is so useful, seems difficult to achieve.
2 Use a database
Microsoft Access, SQL Server, MYSQL… There are many, many databases out there and they are all powerful beasts that can eat up and manipulate large amounts of data. Databases have a huge role in the management of enterprise data and all serious organisations use some form of database.
The beauty of Excel is that you can get what you want quickly and don’t have to ask an IT person – or even worse a DBA – to deal with your question. You can manipulate the data and get the results you need.
3 Use a system based on git
Git is software for tracking changes in any set of files, usually used for coordinating work among programmers collaboratively developing source code during software development. It is a powerful and complex tool that is ubiquitous in software development and its adoption has led to a revolution in how teams collaborate on projects.
There are a number of systems based on Git that are available on the market. Git is great for code, it tracks the changes in text (code), but that is very limited when it comes to complex data in Excel. It is not designed for that level of granularity. Git-based approaches just track immutable files, they do not store the differences between files. They cannot handle time-travel queries, such as “give me data for the range between the years 2016-2018” or “give me the value of these outputs on the 6th September 2018”. Without time-travel, they are limited.
4 Microsoft Excel or Microsoft Sharepoint
Seems sensible to use the Microsoft products to do the job and this might suit some users that want to stay in the bubble, but there are always costs!
Firstly Excel, the shared Workbooks feature for Excel version control has many limitations:
- 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.
- If you turn off the Shared Workbook feature at any point, you will lose your entire change history.
Sharepoint – we’ve never met a happy customer, so please shout if you are out there – is difficult to set up and is not a version control system. It is mostly used for file sharing or at best a collaborative content management system. Yes, you check-in and check-out files (like pulling and pushing in version control), but you can’t see who made the changes, don’t have a full audit log, can’t search across all the Excel workbooks, you can’t check-out the same file at the same time as somebody else, and you don’t have traceability.
5 Version Control for Excel
Excel does a superb job of analysing data and it is loved by people around the world. Integrating Excel with a proper version control system like VersionXL can give you the best of all worlds. The freedom to get on with your work and the comfort of control.
Excel bashing is easy, and it has real flaws, but there isn’t a comparable alternative. Revision Control, which is at the heart of VersionXL, is the enabler of collaboration – you can access, share, and work on different versions of the same workbook at the same time in a controlled way. You can also audit all changes with a full commit history.
There are three primary reasons for using a version control tool like VersionXL:
- Safety: save the state of your work at any moment (hence the name, version control). That means you can feel free to experiment as you develop; if something doesn’t work out, you can always return to an earlier saved state where things were okay.
- Backup: VersionXL lets you synchronize your work from your computer to a computer somewhere off in cyberspace. That way, there’s an offsite copy in case anything bad happens to your computer (or to you).
- Collaboration: VersionXL has a feature that lets you synchronize over the Internet and lets other people in your organization synchronize to that same computer; and the synchronization works both ways. So other people can synchronize with you, and multiple people can work together on a project.
VersionXL software keeps track of every modification in a special kind of database. If a mistake is made, you can turn back the clock and compare earlier versions to help fix the mistake while minimizing disruption. You get:
- A complete long-term change history of every file. This means every change made by many individuals over the years. VersionXL is based on immutable technology, so it can keep everything. Changes include the creation and deletion of files as well as edits to their contents. This history could also include the author, date and written notes on the purpose of each change. This is an incredible tool for an audit and regulation filled world.
- Branching and merging. Having team members work concurrently is a no-brainer, but even individuals working on their own can benefit from the ability to work on independent streams of changes. Creating a “branch” keeps multiple streams of work independent from each other while also providing the facility to merge that work back together.
Traceability. Having the annotated history at your fingertips when you or an auditor or a regulator is trying to understand what is going on is an amazing resource. You know who has your data, who made the changes, and why.