Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Co-Authoring
Deze pagina in het Nederlands

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: 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: 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 (7/20/2017 7:17:10 PM)

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 (7/21/2017 5:12:28 PM)

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 (8/18/2017 7:53:49 AM)

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

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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