Collaboration in Microsoft Excel – Web App versus 2013 Desktop

A client wanted to know:

“If you store an Excel file in a document library (via Office 365 SharePoint Online), can multiple people open and view/edit data in a worksheet at the same time, and will updates appear in “real” time?”

I knew the ability to collaborate has been available since Excel 2010, but the answer was not simple, more like a “yes, but…”

And the reason for this is that yes, simultaneous editing is available if you’re using Excel Web Apps, (and has been since 2010 – see Diego Oppenheimer’s Office Blog post from January 2010 here), but:

• only basic features and functions are available
• an internet connection is required

Which means that it’s inevitable – at some point you will need to open the file in a desktop version of the Excel software.

Now, if you open the Excel file in a desktop program (such as Excel 2013), you can use the “Share Workbook” button* to allow multiple users to share a workbook, which is collaborative, but this is not the same as working in the same file at the same time with changes updating in real time.

* Step by step instructions from Microsoft Office Support available here.

What it is: each user’s changes are saved as a copy of the file, and then the owner of the file must resolve conflicting changes by merging the copies into one.

On top of that there are a slew of features that shared workbooks don’t support (scroll to the bottom of this page here to see the complete list).

My initial reaction was to recommend setting up a hybrid solution – where the Excel file is opened in Excel Web App to allow multiple people to make basic data entry updates to the same worksheet in the same workbook and then have the Excel file opened in a desktop version of Excel to allow others to make more complex changes to the same workbook using “Share Workbook”.

Seemed like it would work, until I tested it and hit this warning screen:

 Summary:  The Workbook Sharing button should not be left on 24/7. The feature is meant to be used in specific situations where a file’s “owner” wants to gather feedback from a select group of people, but ultimately wants to control the final result by reviewing everyone’s contributions (via comparing original with each submission) and then merging the approved changes into the original source file.

Real collaboration is available using the Excel Web App. Any changes beyond basic data entry should be performed by one person at a time with the document “checked out” (which is possible as the file is stored in a SharePoint document library). And if that’s not an option, maybe it’s time to ponder if your organization really needs people making changes in the same Excel file simultaneously… If so, consider the ROI on breaking your one workbook with multiple worksheets into multiple workbooks, each with their own worksheet…

Agree? Disagree? Share your thoughts with me…