Collaborate on Excel Workbooks - The Definitive Guide

With the global spread of COVID-19, there’s a strong push to work from home for literally everybody who can. By nature, most employees who work in an office are part of that crowd. All of a sudden, remote collaboration is a thing and creates new challenges for employees who have been going to a physical office for all their life.

They now have to get used to chat and video conferencing, and sure enough, more Excel spreadsheets are sent around by email while IT is working out how to make the shared drive accessible from remote or considering to introduce a service like Dropbox. But are these options really the best solution if you work with multiple people on the same Excel workbook?

Over the years, Microsoft has come up with various solutions for the collaboration issue and 3rd party vendors are also trying to solve the problem which has led to a broad set of options. Most of the solutions work in one of the following ways:

• File locking: Only ever one user works on the same workbook at the same time
• Real time collaboration: multiple users can work on the same workbook at the same time

This guide will show you how each option works and point out the strengths and limitations of the different approaches.

Table of Contents

1. Shared Workbook (legacy)
2. Co-authoring
3. xltrail
4. Shared Network Drive
5. SharePoint
6. Dropbox
7. Box

Shared Workbook (legacy)

Shared Workbooks have been around for a long while. However, with Excel 2016/Office 365, while still usable, they are now officially legacy and have been replaced by co-authoring (see next section).

It’s still worth to see how they work and what the limitations are which eventually caused Microsoft to abandon them.

How does it work

If you are using the latest version of Excel you need to add the required buttons to the Quick Access Toolbar (or to a custom group in the ribbon). Right-click on the Quick Access Toolbar and select Customize Quick Access Toolbar. In the pop-up, select Commands Not in the Ribbon and add the following buttons:

• Share Workbook (Legacy)
• Track Changes (Legacy)
• Protect Sharing (Legacy)
• Compare and Merge Workbooks (Legacy)

Up to Excel 2013, these buttons are available out of the box under the Changes group on the Review tab in the ribbon.

To turn a workbook into a shared workbook, click on Share Workbook and make sure to check the box at the top if you are on the latest version of Excel:

After making it a shared workbook, Excel will mark it as Shared after the file name in the title bar.

You should save your shared workbook on a shared network drive so everybody can access it at the same time, but you could also send around copies by email and then manually merge in changes from these copies later on (via the Compare and Merge Workbooks button). Note that for this to work, the workbook needs to be shared before sending around copies.

The behavior of a shared workbook differs from a standard workbook on a network drive as follows: If a standard workbook is being opened by 2 people at the same time, then the second person will get a File in Use pop-up warning and has the options to either open it read-only or receive a notification when the file is no longer in use (see also below in section Shared Network Drive).

On the other hand with shared workbooks, multiple users will be able to open the same workbook at the same time. Changes from the other users will by synced periodically or when hitting save, based on the settings under Update changes on the Advanced tab:

You want to make sure to also click on the Protect Sharing button in the Quick Access Toolbar and activate the Sharing with track changes checkbox so that it records the history, see below.

Resolving conflicts and History

When multiple users create conflicting changes (e.g. by editing the same cell), then one of the users will get a Resolve Conflicts pop-up when it updates the changes. Let’s assume that user 1 changes a cell from A to B while user 2 changes the same cell from A to C, then this is the pop up that user 1 gets:

While it’s easy to click on Accept Mine, this will cause the other person’s change to revert back with the pop up Your workbook has been updated with changes made by others. However, this subtle message might bring loads of other changes at the same time and hence it might not be noticed at all by user 2 that his/her change has just been reverted. While user 2 could review every single change cell by cell, it’s a very tedious task.

If you enabled track changes, you can always look at the history (via Track Changes (Legacy) > Highlight Changes and checking: List changes on a new sheet), but it might quickly become hard to follow:

Limitations

Shared workbooks have many limitations, the most important are:

• It’s officially legacy, i.e. Microsoft will not put any more work into them
• They need to be stored on a shared network drive for optimal functionality
• You can’t edit a shared workbook with Excel Online
• You can’t use a workbook that contains VBA or Tables
• Once shared, you can’t use a whole bunch of items or actions (they will be disabled) like: Creating/changing charts, deleting sheets, changing/deleting array formulas, inserting/deleting blocks of cells, adding/changing conditional formats etc. For the full list, see the docs under Additional information > Unsupported features.
• Changes that you make to cell contents are tracked, but other changes, such as formatting changes, are not tracked.
• The history of tracked changes is stored within the workbook which means that the longer the history, the bigger the Excel file. To keep the Excel file in a manageable state, Excel defaults to 30 days of history (this can be changed to a very high number though).

Conclusion

Shared workbooks are nice because they are supported by most versions of Excel and work on your own infrastructure with your shared network drive. They certainly have their use case, e.g. it’s great if various department heads need to fill in numbers on an accounting sheet.

It’s pretty useless though for spreadsheets that are still being developed and heavily refactored as the restrictions (like not being able to add charts or delete sheets) are simply too limiting.

Also, the change history gets quickly very noisy as it’s just a long list of actions without the possibility to group a set of changes into logical steps with an explanation of what happened (e.g. “added a new column for next year”).

Co-authoring

Co-authoring is Microsoft’s answer to Google Sheets and (at least officially) the replacement for shared workbooks. When you co-author, multiple users can open and work on the same Excel workbook and see each other’s changes instantly. Like with Google Sheets, this functionality requires you to save your workbooks in the cloud (in the Microsoft world that means either on OneDrive, OneDrive for Business or SharePoint Online). Note that the on-premise version of SharePoint is not supported. This fact alone is probably stopping many companies from accepting co-authoring as the replacement for shared workbooks. Compared with Google Sheets, Excel co-authoring has one advantage though: It also works with the locally installed versions of Excel on your desktop, not just with Excel online. On the other hand, it doesn’t offer a version history like Google Sheets does. Finally, to be able to co-author, you need to have an Office 365 subscription.

How does it work

Save your Excel file on OneDrive or SharePoint online where others can access it or simply invite them by clicking the Share button on the top right of Excel:

You can now see who is also working on the Excel workbook:

You will see their cursors (if you have ever used Google Sheets, then you are already used to these concepts):

Resolving conflicts and History

Co-authoring always lets the last version win so you don’t have to resolve conflicts like with shared workbooks. On the other hand, you loose the detailed version history that shared workbooks offer. The only history you get is by clicking on the file name in Excel’s title bar (not the most obvious location):

Clicking on Version History will open a side bar with saved versions. Clicking on a version will open it in a new window with a restore button that allows you to undo your recent changes (but again: no way to see what changed in between versions):

AutoSave

A critical component to co-authoring a workbook is the AutoSave toggle on the top left of Excel. By default, it’s on for workbooks saved on OneDrive or SharePoint online:

This allows Excel to show the cursors and changes of the other users in real time.

It might force you to change existing habits though: You can’t just open an Excel file, play around, and close it again without saving. With AutoSave turned on, the Save As... is also replaced with a Save a Copy.

Here’s another example that makes AutoSave behave awkwardly: If you filter rows (let’s say to only show tasks in a list that affect you) then everybody else will get the same filters applied (Google Sheets offers Filter Views to get around that issue. Microsoft has accepted this as a feature request, but without providing a timeline yet).

To work around this, you can switch AutoSave off (this also brings back the Save as... option) and Excel will actually remember your choice when you open the file next time. This, however, will only sync your changes with the others when you manually save the file, making it behave similar to shared workbooks with the caveat that it’s much harder to keep track of what’s going on as track changes/resolve conflicts are not available.

Limitations

• Requires you to work online (or you risk that your changes will be overwritten when you’re back online)
• Only the following formats are supported: .xlsx, .xlsm, .xlsb
• Files need to be stored in the cloud, i.e. on OneDrive, OneDrive for Business or SharePoint Online. SharePoint On-Premise is not supported.
• Requires an Office 365 subscription.
• Requires the latest version of Excel for Office 365.
• No conflict resolution. In general, the change that was saved last, wins.
• Track changes is not possible.
• Co-authoring with AutoSave might require a change in the workflow as you can’t close a workbook without saving anymore.

Conclusion

If you love Google Sheets’ real time editing experience but prefer to work with Microsoft Excel, co-authoring is for you. It’s great that it works on any device, including Windows, Mac, Office Online and Mobile apps.

It has similar use cases like we saw for shared workbooks, but then again it might require you to change your workflow as everything is synced in real time to all the other users.

xltrail

xltrail takes a different approach at collaboration by not interfering with Excel at all, neither on the application nor on the file level. Instead, it is a web based platform similar to SharePoint (see below) or GitHub.

How does it work

With xltrail, you download the latest version of the workbook from the web application, do your changes and upload the changed workbook again via drag and drop. A desktop integration will come soon, so you don’t even have to do the drag and drop anymore. Also, if you are a developer and use Git, you can integrate xltrail with Git which will then sync with every push.

Whenever you are done with an iteration of your changes, you simply drag and drop your workbook on the web based user interface, where you can add a message about what your changes are:

This will record a version history (“audit trail”) of who changed what, when and why:

From there you can not only download the historic file versions but you can also view the changes between any two versions to see what was changed by your co-workers across cells and VBA code (green means new or added while red means old or deleted):

If you need more context, you can always view the full sheet:

This way, you can see if it was really “just a single cell” that your colleague changed or if they inadvertently introduced some other changes. It makes it also easy to stay up to date with what happened to your spreadsheets while you were on a vaction, for example.

Limitations

• Currently, xltrail only tracks a limited set of content including cell formulas, VBA code, Power Queries etc. For the full list, see the docs.

Conclusion

xltrail is the only collaboration tool that makes the progress and changes between versions of the workbooks visible. This opens up completely new possibilities for peer reviewing and signing off changes. Other than that, it uses Git version control system behind the scenes (or integrates with it) which makes it a great candidate for an audit trail.

A big help for collaboration is also the ability to send around URLs via chat or email that point to a certain range of cells so it’s easy to make sure that everybody talks about the same thing.

Shared Network Drive

While a shared network drive works great together with shared workbooks as seen above, it is probably also one of the oldest ways of working on standard (non shared) workbooks with multiple people via file locking.

How does it work

Not much to explain here: Save a workbook on a shared network drive and open it. If you are the first person to open it, all is good and you can edit the file at your heart’s content. If, however, the file has been opened by somebody else already, then you will get the following pop up:

You can either open it read-only or Excel can notify you when the other person has closed the file again and it’s your turn to edit it.

Limitations

• No built-in content versioning, but depending on how the shared network drive is setup, you might be able to right-click on the file and restore (a few) older versions by selecting Restore previous versions.
• If somebody forgets to close the file again after using it, it blocks all the other users

Conclusion

Working with a non-shared workbook on a shared network drive is quite possibly the most simple way of working on the same workbook with multiple people without having to accept any of the limitations as we face them with shared workbooks and co-authoring.

File locking ensures that changes by multiple users are done sequentially so we don’t risk that somebody overwrites changes from somebody else. File locking also means that we don’t have to resolve conflicting changes.

SharePoint

We have already seen how you can use co-authoring on files that are stored on SharePoint online. There is, however, another way of collaborating with multiple users on Excel files stored on SharePoint: The check-out/check-in workflow. Unlike co-authoring, SharePoint’s check-out/check-in feature requires you to edit the file one after another but importantly, it also works on SharePoint servers that are installed on premise, on your own infrastructure.

How does it work

In short, SharePoint’s check-out/check-in workflow goes as follows:

1. Check out the Excel file (which gives you the exclusive rights to edit)
2. Open the Excel file in Excel or Excel online
3. Edit the Excel file
4. Save the Excel file
5. Check in the Excel file and make a comment about what you changed

Checking out an Excel file from SharePoint means that nobody else can edit it during this time, i.e. the effect is the same as the file locking we saw with shared network drives in the previous section. If somebody forgets to check the file back in, it can be checked in by somebody who has Full Control permissions.

You start by checking out a file by clicking the 3 dots next to the file name and then going to More > Check out:

You can tell if an Excel file is checked out if it has an arrow in the lower right part of the file icon:

Now you can click the 3 dots again and then Open > Open in Excel or Open in Excel Online to edit the file.

Once you’re done, save it, go back to SharePoint and check the file back in by clicking the 3 dots, then More > Check in. Alternatively, if you want to throw away your changes, you can also select Discard check out instead. To simplify the check in workflow, you can also simply close your workbook and Excel will ask you if you want to check it back in.

When you check an Excel file back in, a pop up will ask you to comment the changes:

While commenting isn’t mandatory, it is highly recommended as this will allow you to create a nice version history that you can use as an audit log and to roll back to previous versions if something goes wrong. You get to the history by clickin the 3 dots, then Version history:

Limitations

• No content versioning, i.e. you can’t see what changed in between versions

Conclusion

Compared to a bare bone shared drive, SharePoint offers roughly the same features. However, the ability to create a meaningful version history with change comments makes it a lot easier to keep track of who changed the file and why.

Dropbox

Dropbox and Microsoft have been working together since 2014 and that’s why Excel and Dropbox integrate quite nicely nowadays. Dropbox offers two possibilities to collaborate: (a) co-authoring via Excel online (the recommended solution) and (b) working locally with your Desktop version of Excel. Here, collaboration is assisted with the blue round Dropbox badge that attaches itself to the right hand side of Excel.

Co-author with Office Online

To get started with Microsoft Online’s co-authoring, click on Open with... > Microsoft Excel Online:

Things work the same as described in the co-authoring section with the difference that changes get automatically saved to Dropbox instead of OneDrive/SharePoint and Office online will show you a direct link back to your Dropbox:

Collaborate locally with the help of the Dropbox badge

If you open an Excel file from your local Dropbox folder (or choose Open in Excel on dropbox.com), then the blue Dropbox badge will attach itself to Excel. If you have a file open and somebody else just made a change, it will warn you about it:

If, however, you make changes at the same time like somebody else, you will get a version conflict and Dropbox will save two separate files:

Now, you don’t have any other possibility than manually merging these two versions of the file. If you want to prevent this to happen, Dropbox recommends to drag the file outside of the Dropbox folder so only one person can work on it at the same time. Once you’re done, drag it back into the Dropbox folder. This essentially is how you can use the file locking mechanism with Dropbox.

Limitations

• Dropbox co-authoring only works with .xlsx files
• The co-authoring feature will only work if the person you’d like to collaborate with is a member of the shared folder in which the Excel file is stored.
• While Dropbox offers a version history, it only goes back 30 or 120 days (depending on your plan), which makes it unusable as an audit trail.
• Co-authoring is only supported in Excel online, not with the Desktop version of Excel.

Conclusion

Dropbox and Microsoft Online integrate well for co-authoring Excel files online. The blue Dropbox badge is an attempt to ease collaboration when using the Desktop Excel apps, but it’s only likely to work when only a few users commit a few changes or you will have to resolve a lot of version conflicts manually.

Box

Box has the same options to co-author an Excel file with Excel online as we saw with Dropbox, so we’re skipping this part here. The special thing about Box is that it also offers file locking. Let’s get right into the details!

How does it work

Box offers an informal file locking. This means that you see a lock icon in the file explorer or on the web, but it doesn’t stop you from editing the file if you wanted to. There are multiple ways to lock a file:

From the Web Application or the file system

You can right click on a file and lock the file from the context menu:

You can set the lock for a certain period of time or until you manually remove it again. Optionally, you can also prevent that the file can be downloaded while it’s locked. Once locked, a red lock appears:

And files in the Box Sync folder also get the lock symbol:

You can also directly lock/unlock a file in the Box Sync folder by right-clicking on it.

From the Box for Office add-in

If you have the Box for Office add-in installed and you open the file via Open > Box, then Box will lock the file automatically for you (and unlock it again when you close it):

This will also be shown directly in the ribbon:

Limitations

• File locking is only informal, file can still be edited
• The Box for Office add-in only works on Windows

Conclusion

If you like the way a standard Excel file behaves on a shared drive but prefer to use a cloud solution, then Box’ file locking might fit the bill. With Box, it is also possible to keep an unlimited number of versions, but only if you use Box Governance.