Hello My Friends,
This week’s #SolutionsSaturday is based on a post from Malte Hartmann who had asked about Microsoft’s new Co-Authoring for Excel. This new feature allows users to share and sync their macro-enabled Excel workbook with other user’s simultaneously. In this week’s post I will go over a brief summary of this new feature so you can see if it is right for you.
It seems that Microsoft has finally gotten around to a feature that has been highly requested for so many years. With the severe limitations of Excel’s ‘Shared Workbook Feature’ and Google’s own Google Sheets already having this ability for many years now, this option was well overdue in my option. A few months ago I had a chance to check this feature out on a webcast with a representative from Microsoft Directly who displayed some of the features.
Certainly Microsoft does deliver on the promise of syncing changes from multiple users around the world. When workbooks have been uploaded to OneDrive or Sharepoint, they are then able to add other users who can make simultaneous changes to a workbook.
Once a user makes a change and saves their workbook (or Autosave is on) then those changes become available for all the other shared workbook of the users.
You have the ability to see other peoples selected cells (just as you can with Google sheets) in multiple colors, which is a nice touch so you can see what cells other users are currently on.
For the most part, this feature worked as expected in the demo after they were able to get all users properly online and synced.
The first big red flag that will stop many from using this feature is that it is only available for Office 365, Office 2016 or Office Online. This feature will not work with any older versions of Excel and I really do not believe they will adding this feature to support older versions. Many of us, including myself, actually prefer older versions (I prefer 2010) so this feature is only supported in the newest versions of Excel
Only One Drive and Sharepoint are supported, so no other folder sharing options such as DropBox, Google Drive (Now called Backup & Sync) or any other folder sharing applications are supported
Saving is required before any of your changes are sent to other users. With the AutoSave feature this is not so much an issue, however many users may not want to have the AutoSave feature on if there is a slight delay in waiting for the application to save
If just one person opens the file with an older version, that does not support co-authoring, then all other users get a ‘locked’ error on their side, even if everyone else is using the newest version of Excel
One ‘feature’ I saw during the demonstration that may put off a lot of people from using it is that ALL changes made by a user are synced with all others. Now you might be thinking this is a good thing, but it actually could be a bit of a pain. For example of one user runs a filter on a table, ALL users then get the same filter, even if they not use or apply the filter themselves. So theoretically one person can be entering data in a table, they click save, and its automatically filtered, and if they clear the filter, it then clears for everyone else. I felt this could cause some issues and I am not sure I understand why Microsoft felt it was important to include filters as well in the syn.
There are still some bugs and issues according to Microsoft’s website on this feature including “Refresh Recommended” and “Upload Failed” messages that some users are getting when certain features are used by the user, but not yet supported with Excel’s Co-Authoring feature.
If you have not seen it yet, I have also created a Global Excel File Syncing option that of course I am a bit biased to as well, so I will go over a few points on my solution. I created a video on this, however it was part of the larger Employee Manager and I may do another video on this using just a simple table so that is much easier to understand. (Please let me know if you would like to see it)
I will start with the few negatives of my solution.
It does require some VBA skills although when I simplify the training it should be easy to copy and paste. My solution does also require a shared folder, and all users must have access to this shared folder
The positives though, I believe are many, as follows:
- Any version of Excel could and should work with this, even Excel 2003 theoretically, however I have not tested it
- Saving of the application is not required for users to sync. Changes on all users are synced as soon as the change is made.
- Users can do their own data filtering, completely independent of the other user (only changes to actual cells is synced
- Its super-fast, within seconds and uses super tiny text files that get deleted so the data flow and speed are extremely fast, even with a slow connection
- Any type of shared file system can be used (DropBox, OneDrive, Google Drive, Mega Drive, pCloud and any other that allows multiple users to share files)
- Only the back end data is changes, so this means users doing work on a friend end will have no interruption in their flow of work.
- Theoretically an entire change history can be tracked. It would be easy to put all of the small text files into an Archive Folder (instead of deleting them), then pull that data in a new application so you know who changed what and when. This could be critical for accounting applications.
So that is my solution. If you would like to check out that video, please feel free to view the training here on YouTube: https://youtu.be/3E6ExWYscHQ
Also let me know if you want me to do the same thing, on a simpler 1 table scale as well and I can do it.