Co-authoring in Office 365. A great feature with an unexpected side-effect

If you are on Office 365 and have access to either OneDrive for Business or Sharepoint a new feature will come available soon (and already is available to everyone on Office insider). This feature is called Co-authoring.
The feature enables you to seemlessly work on an Office document (Excel workbook, Word document, Powerpoint presentation) together with a colleague. All parties editing such a document will see the updates made by the others. And it works on many different devices (yes, on Excel desktop too!)

Side-effect?

Yes. Side-effect. Let me limit myself to Excel here.
To enable co-authoring, a new feature has been added to Office called Autosave (I know, back in the day Excel had an Autosave add-in, but that has been deprecated some versions ago). This autosave is needed to do the synchronisation between the different people authoring the file. To ensure everyone is on the same page so to speak.
So what is that side-effect you are talking about you may ask? It is this little dialog:

Do you want to save changes

That's right.

Why should I care, part 1

Consider this process:
  • You are working on an intricate model
  • You've had an idea to make some changes and open the file
  • You've made plenty of changes, but suddenly change your mind
  • No problem, just close the file and click "Don't save".
Well. Not when that file is on OneDrive or Sharepoint. Autosave is on by default for all files there, to ensure Co-Auth works. And it saves quite regularly. This means that your changes are constantly saved and there is no quick way out.

Why should I care, part 2

Consider another one:
  • You've designed (and published) a nice company dashboard
  • You've shared it across the company
  • Joe changes the filters
  • Autosave kicks in
  • Now the entire company enjoys Joe's view of the dashboard.
So for both of the above, you may want to turn off the new Autosave option. Here is how.

Turning Autosave off

Luckily it is easy to turn off Autosave and it is remembered for the file (after -yes- saving it). Simply pull the switch at the top of the Excel window, on the left-hand side:

Excel window caption indicating Autosave is on

Making it look like this:

Excel window caption indicating Autosave is off

From now on, your file behaves as it always did and you can edit at hearts content and close without save will leave the file unscathed. But of course, co-authoring no longer works for the file.

Undoing changes

Now what if I forgot to turn autosave off and made some changes I am unable to undo (like deleting a worksheet)? Your way back is the new clock icon on the top-right corner of the Excel window:

The Activity button below Excel's close box

Clicking that icon opens the Activity task-pane:

The Activity task pane

If you recall when you made that last fatal change, click the "Open version" link just prior to that fatal moment. Excel will open the version and display a new bar:

Restoring a previous version

You can now click Restore to go back to the version you just opened. But you'll have to close the opened version (the one that got saved) first, otherwise you'll get all sorts of errors. This gets harder if the file is also opened by a co-worker.
I pressed Restore and Excel presented me with this bar:

Upload failed warning

Since I want to go back to the original, I chose "Discard changes", which gave me:

Discard changes message

I click Yes. Pfew, that is one heck of a lot of confusing and alarming dialogs and bars to go through just to get my original workbook back!
So there you have it. An awesome feature has been added to Excel, but if you have a habit of fiddling with files, expecting to be able to back out without saving changes you are warned: check the Autosave switch in the top-left corner before doing anything!

 


Comments

All comments about this page:


Comment by: Charles Hall (20-7-2017 19:17:10) deeplink to this comment

I am curious - is there a restriction on what types of workbooks can be opened in collaborate mode? Would workbooks that have macros work? I would not mind that it doesn't track changes in the vba code, but just wondering if users can still use the macros and share the workbook with someone else

Thanks


Comment by: Jan Karel Pieterse (21-7-2017 17:12:28) deeplink to this comment

Hi Charles,

According to this site: https://support.office.com/en-us/article/Document-collaboration-and-co-authoring-ee1509b4-1f6e-401e-b04a-782d26f564a4

"Co-authoring is only supported on modern file formats: .docx, .pptx, and .xlsx without VBA."


Comment by: Dmitri Kotchetov (18-8-2017 07:53:49) deeplink to this comment

It will work with xlsb files and xlsm files. It will work with files which contain VBA projects.


Comment by: Malone Dunlavy (6-4-2018 06:34:25) deeplink to this comment

While I LOVE the co-authoring, it does pose some unique challenges. One in particular I am trying to find a work around for:

I have an inventory file I have made that multiple users use. No one edits the same sheet at the same time and the visibility of the sheets needs to be different for each user. I have accomplished this with an onOpen macro that sets sheet visibility based on username. The problem is that if two people open the doc at the same time, whoever opened last sets the visibility for all users in the doc. I worked around it a little bit by having the onOpen disable autosave. then onClose, it saves the doc (or tries to). The issue is, someusers create new tabs with the processes they do in the doc, if they do that while multiple users are logged in, and they save, it makes it extraordinarily difficult for anyone else to save. :/ If I could just refresh the version onClose, and bypass all the error messages, that would work for me. :/ Any ideas?


Comment by: Jan Karel Pieterse (9-4-2018 08:44:09) deeplink to this comment

Hi Malone,

Problem is that AFAIK sheet visibility also gets synched across all people having the workbook open, just like filter settings and all. The best you can do I expect is trying to prevent people from selecting certain worksheets based on their logon.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].