Co-authoring spreadsheets has been a capability of Microsoft Excel for the last 10 years or so. However, you may be wondering what the problems and limitations of sharing Excel spreadsheets are.
Co-authoring Excel spreadsheets is highly advantageous when you’re entering data and you have a team of people working on the spreadsheets simultaneously, however, there are some serious limitations and problems associated with sharing that you do need to consider when collaborating.
Security Risks For Your Organization
Co-authoring of spreadsheets most commonly occurs in workplaces where one employee will share a file with another. Generally, this is not a huge problem provided that the email they use to share the file is a company-owned email rather than a personal one.
In cases where a personal email is used problems can potentially arise if the file is shared with someone that subsequently has left the organization. This is because the person will still have access to the active file unless the owner of that file shuts down access which is something that is extremely easy to overlook.
I personally had that experience where I had somebody share a OneNote file with me when I was visiting a company for information exchange. Once I left that meeting they did not shut down the access and I still had access to that same file two years later.
To change the access click on the share button and select manage access
Select the ‘can edit’ button to alter the access settings of the file.
Additionally, problems can arise when the owner of the file, leaves a company without reassigning ownership of the file to remaining staff members. This is not something that is impossible to fix, however, you typically need to get members of your IT department to access the employee’s OneDrive and reassign ownership.
If this does not happen then you cannot change access settings for anyone else using the file and you typically will need to create a duplicate copy with a new owner.
Creation Of Duplicate Copies Of The File
If you have a large number of people accessing the file at different times one of the things that can occur is that some users can be locked out of the file which causes the creation of a duplicate file.
As soon as not everyone is in sync then problems start to arise very quickly. To prevent this you simply need to make sure that all team members are aware of the possibility of this occurring and ensure that they do not create a duplicate copy as that will cause problems later on.
Limitations Of File Functionality
There are a couple of limitations associated with the use of shared excel files which users need to be aware of before starting to create a shared file.
Pivot Tables
The most significant limitation which can affect the use of shared files for large datasets is that you cannot create or modify pivot tables, which are one of the most common ways to analyze data, once the file has been shared. This is one of the reasons why it is less common for things like sales data to be shared among personnel.
New Macros Cannot Be Created
The second limitation is that you cannot alter VBA code within the file, however, it is still possible to activate macros that have already been created before the file has been shared. This is usually not a huge problem in most cases because most people are not writing VBA code in shared files.
However, it is important to note that if you are using xactive controls ( things like buttons and drop-down lists on your worksheets) that feature is not available on the web version of excel which is more likely to be used when files are shared.
The File Type Used Is Limited
The co-authoring files is limited to three types of Excel files only which are.xlsx, .xlsm, and .xlsb. However, this is generally not a huge problem because you can generally convert things like .csv and .xls files fairly easily to suitable formats.
General Problems Associated With File Sharing
In addition to the limitations mentioned above, there are also general problems associated with file sharing which occur when many people are in the file simultaneously.
Changes Within Excel Spreadsheets Are Not Tracked
Any changes that are made within the spreadsheet are not tracked in the same way as they are in a shared Word document which can mean in some cases that you may not be aware of all the changes that have been made to the spreadsheet, particularly, if they’re subtle or there are many people working on the file.
Applying Filters Can Affect All Users
One of the problems associated with shared files is that whenever you apply filters to a dataset all users on that spreadsheet will see the filter applied so if they are working on the same spreadsheet it will stop other users from working on the file.
Autosaving Can Result In Loss Of Work
Autosave in certain circumstances can result in the loss of work on the spreadsheet. This is most likely to occur when more than one user is operating in the same space on the worksheet and making regular changes to it. In certain circumstances what can happen is if one user reverses the work of another and the document saves in-between time it can result in a loss of work that other users are not aware of.
How To Work On Co-Authored Excel Files Successfully
To successfully work on co-authored excel files without causing any problems is fairly easy to do provided that you follow the following easy and simple steps.
Step 1:– Avoid working in the same area of the workbook or even ideally on the same spreadsheet at the same time particularly when the structure of the spreadsheet is being altered i.e. columns and rows are being added.
Step 2:- Be clear about the roles that each person is playing in the file to ensure that you do not inadvertently alter someone else’s work or even worse create some sort of miscalculation due to there being too many cooks in the kitchen.
Step 3:- Regularly communicate with team members about what work has been completed on the fire to ensure that everyone is clear.
Step 4:- Ensure that everyone accesses the file using the same application. Avoid having people accessing files through OneDrive while others are accessing the file through teams for example
Step 5:- Ensure that only Those people that are actually working on the file have editing access to it to ensure that there are no unauthorized changes to the file.
Relevant Articles
Is Excel A Programming Language?