Are you using Excel in your team? We probably all do. Almost every business does its planning, data collection, or analysis in Excel. Often entire teams or stakeholders from different units depend on one elaborate Excel workbook. And that's where many Excel problems originate. Read on to get a fast overview of typical issues in Excel and how to solve them fast.
Find in this post
- What are your major problems when your team works with Excel?
- You want input and your team takes forever
- Understanding the Excel workbook is difficult
- Everyone sees everything – data protection at risk
- Data inaccuracies due to Excel collaboration
- Missing transparency for change tracking and version control
- Next steps to improve the Excel collaboration for your business
What are your major problems when collaborating in Excel, and how can you solve them fast?
When collaborating in Excel, productivity, data security, and last but not least, decision-making can suffer. “Excel hell” however 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 and hands-on best practices to solve them.
Where are they? Your requests for updates in Excel have long response times
We have all experienced it; getting input for a strategic Excel spreadsheet 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 follow-up. The entire project probably costs you a couple of hours instead of 15 minutes. 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 of you could spend your time in a much better way, I believe.
Hacks 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. That's one of the reasons, why we have created Airrange. You can split-share Excel workbooks and select a sheet, a few columns or cells and send them out with a request for input. (Don't worry, your Excel data remain in OneDrive or SharePoint).
No one understands your complex Excel models
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.
Workaround to collaborate in heavy spreadsheets
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. Again a visit to Airrange might be worth it.
They all see everything – data protection 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.
Hack to collaborate with sensitive data
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. Did we mention that with Airrange you can actually select the data you want to share from a workbook?
This is wrong! Inaccuracies caused by someone during 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.
Quick way to avoid inaccurate data, outdated versions or broken data
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).
Who did it? 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. 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.
Take action! Improve Excel collaboration for good
The overall problem is not Excel. 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. So, you have three options to solve your collaboration issue with Excel once and for all.
A. 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.
B. 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.
C. Keep your Excel and add functionality :-)
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 and sharing features to your existing Excel workbooks in your Microsoft environment. It is free to try and offers a freemium model. Sign up and found out, if Airrange is for you. Go to airrange.io for more.
This post has also been published on Medium
