Achieve best results with Excel co-authoring (no limitations)

Excel co-authoring is everyday business. Most spreadsheets in finance, accounting, HR, or project management are viewed and edited by different people from your office. And often things don't work out as planned even though Office 365 offers several features for collaboration like co-authoring, sharing, and comments. Here's what you need to consider to achieve better co-authoring in Excel.

Find in this post

• What is co-authoring in Excel?
• What do you need for co-authoring?
• How to use co-authoring successfully in business
• Co-Authoring limitations - What Excel cannot do for you?
• Add additional co-authoring power to your Excel workbook

What is co-authoring in Excel?

Microsoft defines its co-authoring feature like this: "Co-authoring means you and any number of people from your organization can open and work on the same Excel workbook."

And Excel co-authoring makes a big difference in business, mainly when co-working in a heavily used Excel spreadsheet. In a world without co-authoring, you'll open your file from the shared folder only to find it locked because someone else is using it. If you make your changes now, you cannot save them unless you keep a copy. Of course, that is no option. If you open the Excel workbook with a share link, you can edit it no matter how many people are working on it at the same time.

Here is what Excel co-authoring can do for you:

• Open a file simultaneously in editing mode
• See each other's changes in a matter of seconds (in different colors depending on your Excel version.)
• Excel will save all changes made nonetheless, but they are harder to track.
• Comments and notifications (with @mentions) are advisable.

Co-authoring in Excel is a feature mainly for real-time or live collaboration. The ideal scenario for Excel co-authoring is a Microsoft Teams Meeting. Everyone can open an Excel workbook simultaneously and show or edit content. You'll see changes made in different colors.

Another scenario is sharing a link to your Excel file and asking everyone on your list to input or review values. If opened via the link, changes are highlighted in the same way, which is a big comfort win. It is most annoying when you try to work in a spreadsheet and find it locked for editing because someone else is working on it (or forgot to close it and went out for lunch). So co-authoring is worth the while if only for this. Let's dive into day-to-day requirements to make co-authoring work.

What do you need for Excel co-authoring? [technical]

When you are using the co-authoring feature for Excel collaboration, there are a few technical requirements to keep in mind:

• You need an Office 365 subscription
• You need to save your Excel workbook in OneDrive, OneDrive for Business, or SharePoint Online library
• Your Excel files need to have xlsx, .xlsm, or .xlsb as a data format
• You need an Excel version supporting co-authoring and the latest Excel version installed. (It's easy to check if co-authoring is supported; you'll see a Share button on the top right of your screen.)

If it is still not working, here is a troubleshooting list from Microsoft support to check. However, to benefit from co-authoring in Excel collaboration with multi-users, you need to use it in the right way.

How to use Excel co-authoring successfully in everyday business [process]

Don't "over-do" the Excel workbook

Sometimes a shared Excel workbook is suddenly marked as locked. There can be different reasons for this. If you access the file in too many different ways, the co-authoring feature might not work. This is something to consider as many of us access the same file from various sources like OneDrive explorer, a share link in Teams, or email. At one point, this can be too much, and the file is marked as locked. Also, if someone adds a feature that is not supported, the co-authoring feature will not work either, and the file appears locked. By the way, one of these features is change tracking. Check here for more details.

Think twice about AutoSave

Another small issue that can have a considerable impact is AutoSave. To make changes visible for everyone simultaneously, you need to switch on AutoSave (in the left upper corner of your screen). At first, that doesn't seem like a big issue. However, if you are working in a strategic Excel file with AutoSave "on," you cannot reverse changes (made by accident or on purpose) after saving. An alternative to AutoSave is constant refreshing or a comprehensive back-up policy. Jan Karel Peterse has put together interesting arguments on the AutoSave feature that are worth considering. Please note, his blogpost refers to Shared Workbooks, the predecessor of co-authoring. This feature is still available yet not recommended by Microsoft. Microsoft has replaced Shared workbooks with co-authoring and no longer recommends them because they have some severe limitations. However, business users love shared workbooks for their change tracking.  

Remember your process discipline

Co-authoring an Excel workbook usually fails when you use it outside its feature range. Often workflows and communication rules outside Excel are needed to make it work in business practice. A basic one is not to save copies and share copies from the master file; another could be to use @mentions in comments when someone updated the data.

These best practices and considerations also touch the productivity aspect of co-authoring. What about the other productivity aspects of Excel co-authoring? Let's have a look.

Co-authoring limitations - What Excel cannot do for you

If you loved shared workbooks, co-authoring an Excel workbook would seem a step backward. Unfortunately, there is no mix and match, and co-authoring has great aspects. Excel co-authoring allows for multiple editing. That's its main purpose, and it fills that role nicely. However, the data management aspect is missing, and that's what many businesses need.

That's clear when you watch a YouTube Excel tutorial or look at screenshots in Microsoft support pages. There, Excel spreadsheets usually contain only a minimum of data. However, a typical business spreadsheet doesn't look like that. It is either a table or an Excel dashboard with several worksheets and many rows and columns. Even with live change tracking on, there is a high chance you would miss Emma's change on worksheet2_e202 and what she did. So here is what businesses assume nevertheless, and try to fit in co-authoring.

If you find ways to solve these topics outside Excel, your experience will be far more reliable and relaxed.

1. Don't expect too much change tracking: For once, you don't get transparent change tracking. There is a version tracking in Excel co-authoring, but you can always only jump back to an entire previous version. You cannot review changes and take them back change by change.
2. Don't rely on Excel co-authoring in your business process: Sometimes, your Excel workbook is locked, and you don't know why. Viewing Microsoft's troubleshooting list can be a massive communication effort to get it back on track.
3. Don't use comments as chats: You can easily overlook a comment in a spreadsheet. So it can happen that you'll miss a co-worker's comment and realize you are missing data just when you are preparing your presentation.
4. Don't use your Excel workbook outside a core team: Microsoft suggests classroom size as a good number of co-authors. Since you can only share an Excel spreadsheet as a whole, the number of co-authors might be even lower. User rights have to be equal, and troubleshooting (e.g., when a file is locked) only works in direct communication.
5. Never stop checking your figures. When everyone inputs their changes, references can be broken, and formulas don't work anymore. So you can't avoid communication outside the spreadsheet or at least with @mentions in a comment when someone has updated their figures. Then you can scan for errors. However, that only works if nothing occurred by accident.

‍

Add extra co-authoring power to your Excel workbook

So, the secret of successful Excel co-authoring is to know its limitations, understand your needs, and plan your processes accordingly.

If you don't want to find solutions outside Excel and rock solid Excel sharing, change tracking, version history, and user-friendliness are features you really miss, go to airrange.io. Airrange offers Excel collaboration and Excel sharing based on all Excel files in OneDrive and SharePoint. Try now free.

‍

This article has been also published on medium

‍