How to Make Excel Collaboration Work Better for You [a fast guide]

Excel Collaboration
Angelika
·
September 14, 2021

Are you using Excel to collaborate with others? Indeed, most of us will raise our hands. Almost every business does its planning in Excel. Often entire teams or stakeholders from different units collaborate in one Excel workbook. And this Excel collaboration is where many Excel problems originate. Read on to get a fast overview of typical Excel collaboration problems outside the Excel feature world and common ways how to solve them.

Find in this post

What are the major problems when collaborating in Excel, and how do businesses solve them?

Business users love Excel for the flexibility and agility they gain. Hundreds of Excel sheets are created every day. Strategic Excel solutions are at the core of business decision making. However, when collaborating in Excel, productivity, data security, and last but not least, decision-making can suffer. Sometimes what happens then is described as “Excel hell.” That may be a bit drastic as most businesses seem to have found a way to work around it. Here are the most common Excel collaboration problems.

Your requests for data entry have long response times

We have all experienced it; getting input for a strategic Excel solution can be tedious to the point of exasperation. If you are managing Excel collaboration with a group of 20, five people may respond instantly, 10 need a reminder, five need a close and even more time-consuming follow-up. The entire project would probably take you a couple of hours. Imagine what the time spend would look like for a group of 50 or 300? As we are talking of strategic Excel solutions in Finance, Accounting, HR, or Project Management, probably a high paid manager or her or his equally busy assistant is doing this work. Both could spend their time in a much better way, I believe.

Common solution to reduce response times

To speed projects up, some companies have turned to rewards or penalties for not responding in time. That may work, but it still leaves a project that significantly contributes to a company’s well-being with an annoyed-annoyed-situation instead of a win-win situation.

Understanding the Excel workbook is difficult

Why are response times in Excel collaboration often so low? The answer is complexity. Have you ever tried to navigate and enter data into somebody’s Excel workbook merely relying on intuition? Then, you know, it’s probably not a good idea. Finding your way around an Excel solution with formulas, cell references, and tables is difficult. And often, this leads to broken cell references, formulas, and wrong results.

Workarounds for complex Excel workbooks used in Excel collaboration

To solve the complexity problem in Excel collaboration, most projects that require data entry provide either a webinar or a document with explanations. Sometimes that’s just like giving directions –it’s easy to find but describing it is hard and complicates everything unnecessarily. Also, watching or reading the explanations turns a 2-minutes-request for data entry into a lengthy, time-consuming affair. And completing the required task on mobile is out of the question anyway.

Everyone sees everything – data protection could be at risk

Yet finding your way around is only one side of the problem. You can only share Excel workbooks as a whole. At best, as a user who is keen on completing a task, you’d scroll a lot of useless information until you’d find what you need. In the worst case, while doing this, you’d see sensitive data, including all other user’s data input. That’s a big issue, and it’s made even more significant by the fact that most of us make a back-up copy of the Excel sheet to be on the save side. The sensitive data shared is then copied outside the owner’s reach, kept multiple times, and forgotten by everyone.

Common solutions for data protection in Excel collaboration

You can protect data from editing or hide them. Yet either you can still find the records, or you hide them for everyone. In most companies, the workaround for data protection is to strip down Excel workbooks and create an individual copy for every stakeholder or an empty template. These templates then have to be merged (and controlled) once filled in. Depending on the size of the group you manage in your project, it might not even be worth trying to merge these Excel workbooks automatically and double-check if everything is all right. Sometimes manual copy and paste of all the information by hand can be faster.

Data inaccuracies due to Excel collaboration

Excel is flexible, and the downside is, data input can go wrong.  Nothing is closer to Excel hell than to always scan for mistakes. Even the copy-paste of up-to-date figures from one Excel workbook with a different data format to the master sheet can lead to an inaccurate calculation. And sometimes you don’t even know it. The figures are not recognized as such and are not part of the final results. When a company’s liquidity suddenly goes down by a couple of weeks compared to the previous week’s forecast, that’s truly Excel hell for the Excel solution owner. Most Excel admins regularly check their results, test the figures against probability, and scan if their data still add up. And again, that takes quite some time.

Workarounds to avoid data inaccuracies

For strategic Excel solutions, you often collect data input outside the spreadsheet. Despite the co-authoring option. That’s because the Excel owners worry about inaccurate data due to input mistakes. The data might also be sensitive, and admins care to avoid unnecessary copies outside the master spreadsheet. Again, data is being submitted by email, in chats, other Excel files, and then you update the Excel workbook manually. This method sounds tedious, but actually, it can deliver the best results (viewed in retrospective).

Missing transparency for change tracking and version control

When you collect and update figures – either by co-authoring or manual input – it is hard to find out who changed what and when they did it. Are the figures representing the same period? Are you looking at the up-to-date figures marketing figures or still the ones of the last month. You can either find out by checking in old emails, chat streams, or making a phone call. If you still use Shared workbooks (or managed to bring it back to live), you might see who made changes if you dive deep. However, that is a thing of the past. Microsoft does no longer recommend shared workbooks for several reasons, and co-authoring doesn’t offer change tracking. There is not a workaround or a recommendable solution for this. So, when a change has been made and (auto)saved, it is there, and you can only reverse it by hand. If there are many different copies of your Excel workbook or input templates, changes to the structure and data are hard to enter into the process. In my experience, outdated versions will haunt you for a long time.

Next steps to improve Excel collaboration for your business

The overall problem is not Excel itself. It's the productivity loss we experience when fitting Excel collaboration into business processes. Excel’s core design is not to follow workflows but to enable us to do amazing calculations, data modeling, and data evaluation fast.

Define strict workflows and ensure everyone follows them

If one of the above Excel collaboration problems concerns you and your team, you could define strict workflows and policies on using your Excel solution. You probably have done so anyway. As described above, there are a lot of workarounds and solutions in place. If you are looking for a solution that helps you to increase productivity and spares your time (and headaches), here is what you could do.

Think about a standard or a legacy alternative

You could turn to a database solution, a standard system, or legacy software. However, as you are using Excel right now, you probably appreciate the flexibility and the low cost of your Excel solution. A standard solution is advisable when there will be no (or only long term) changes to the data structure. If you need changes, these need to be set up by admins or even coded. That means additional cost and, in most cases, a couple of weeks. Also, the cost of productivity loss needs to reflect the license cost of the database you are planning to use. And of course, there is simply the issue that a standard software or database cannot do all your Excel solution can do. And that’s probably why most systems have an “export to Excel” button.

Check out an Excel alternative

In the last months and years, a couple of Excel alternative systems have come to life. Most of them are still in beta mode. Most have a focus on one specific Excel topic like project management or finance solutions. Some require the complete transfer of your data from Excel and your existing Cloud environment. These aspects are worth considering.

If you don’t want to move your data outside Office 365 and still gain productivity, go and check out Excel collaboration with Airrange.io. Airrange is not an Excel alternative. It adds collaboration features to your existing Excel solutions in your Office 365 environment. It is still in beta, but you can sign up for early access or get more information on airrange.io.

This post has also been published on Medium