Common Problems with Shared Excel Files & Solutions

You might get unexpected errors on a shared workbook, such as the “unreadable content”, or “[someone] is locking the document” errors. In this article, we address some of the solutions to this problem.

These errors might unshare the shared workbook with users. They’ll no longer be able to save their data to the shared Excel file. Therefore, each user can only save their workbook individually to their local computer, which will result in having different copies of the same Excel file on different computers.This scattered unsaved data might be a considerable risk to your business, and transferring it from the workbooks of each user to the original one might be risky due to human error.

These errors might be rising because of a corruption in your Excel file. A file with corruption might still be opening and functioning, but at some point the corruption might cause some problems. 

Before trying to get deep into solutions, we share a response from Microsoft, “Please keep in mind that it’s often quite difficult, if not impossible, to determine where corruption comes from. Corruption can exist in the “shell” of the workbook, or in certain areas, such as a PivotTable, styles, defined names, objects, or the calculation chain/formulas. Corruption can be caused by many different scenarios, for example, a network glitch while saving, a power surge, copying and pasting in corruption from another file, the list goes on. You can compare file corruption to getting a nail in your tire. The nail may be stuck in your tire for a long time without you even noticing, and then all of a sudden your tire goes flat, or the file becomes unreadable or displays strange symptoms.”

Shared Excel Files Solutions

Here, we list some of the potential solutions, but please keep in mind, none of them are guaranteed to work.

Cleaning up Excel Workbooks by Removing Inactive Users & Custom Views

Try cleaning up the workbook by removing inactive users and clear all the custom views, regularly. Both inactive users and custom views are common causes of errors in shared spreadsheets. These solutions are simple, quick, and suggested by Excel specialists in discussion websites. The following tutorials show how to do them.

To remove inactive users, follow the steps below,

1- Go to the Review tab from the ribbon.

2- Select the Share Workbook option in the Changes group.

3- In the Share Workbook dialogue box, go to the Editing tab.

4- In the “Who has this workbook open now.”, select the user you want to remove.5- Click on the Remove user button and press OK.

https://youtu.be/bis5cd7AOYE

You can also write a macro that automatically removes inactive users from a shared Excel file. A custom macro can ask users every few hours if they are active. If no response is received, then the inactive user is removed.

Creating a Clone of Existing Excel Workbook

By creating a fresh copy of your Excel file, you can reduce the chance of getting errors in a shared Excel file. Sometimes an Excel spreadsheet might include some corruption and raises errors while it’s shared. And fixing the corruption might take longer than migrating the data of the workbook to a new one. By migrating the data, the corruption won’t be transferred to the new file, and this might help in preventing some errors when the file is shared. 

In addition, if you created your spreadsheet with a different Excel version, we recommend creating another spreadsheet with the version of Excel available on your computer and migrate your data to the new spreadsheet. A file created by Excel 2016 is not exactly the same as a file created by Excel 2010, even though they both have the same file format.

Values can be transferred simply by copy-pasting, but Transferring formulas might be a little tricky. We recommend a custom macro to transfer formulas. If you’re not an expert in writing VBA code, please feel free to email us to get a free quote for Excel development.The macro can also transfer charts and other objects. The advantage of the macro is that it can automatically do the migration process; no need to manually migrate the data each time. A click of a button does the job.

Merging Data of Unshared Excel Files Into Original Workbook

The problems in a shared Excel spreadsheet might be unsolvable. Please note, many users reported different problems with shared workbooks. Just a quick Google search lists many open tickets regarding Excel shared files. And Microsoft is additionally pursuing a different sharing solution, which is Office 365, and OneDrive share. There might be a chance that their priority is not to fix the issues with the classic share.

In case you cannot fix problems with sharing Excel files, our Excel specialists offer custom macro to our clients, which recovers shared Excel files. Some errors in a shared workbook might prevent users from saving  their data in the shared file because the error causes the shared file to be suddenly unshared. In this case, probably the only solution is to manually copy data from the workbook of each user to the original file and reshare it. But this solution is time-consuming  if done manually. We came up with a custom macro solution that manually transfers data from each user’s spreadsheet to the original file. The macro is like a bot that replays the actions done by each user in the shared workbook.

Sharing Workbook in OneDrive and Office 365

Microsoft is promoting the new share functionality, which is available in Office 365 and OndeDrive. If the problems of classic share keep bugging you, you can probably switch to OneDrive share, which might solve some of the sharing issues.

You can host your Excel file in OneDrive or business OneDrive and share it with other users. Multiple users can work on the same workbook at the same time with co-authoring. However, each user is required to purchase an Office 365 license. For more info, please refer to this Microsoft article about co-authoring.

Some companies, due to their privacy policies, prefer hosting the files on their server. The drawback of this solution for such companies is that files are hosted on OneDrive, not their server. 

Do you need an Excel consultant in Canada? Contact us today!

Other solutions

Here, we list some other solutions that you can use  quickly.

• Maybe the folder in which your workbook is located is corrupt, and moving your file to a new folder might help. 
• Please make sure your Excel spreadsheets are within the Limitations of Excel
• Remove special characters from sheet names and cells. A special character can be a non-visible character.
• Your file might have some connections with other files. Security settings prevent the automatic updates of those connections.
• Run Excel as administrator to prevent some security issues.
• Install the Visual Basic component for Office 2010.
  • Instructions: Control Panel > Programs > select Microsoft Office 2010 and click on Change > Add or Remove Programs > at the bottom of the list click on the plus next to Office Shared Features > select Visual Basic for Applications > and right-click and select Run from My Computer > Continue. Reboot when it has finished.
• Excel cache or memory is full. Restart Excel.
• Install the 64-bit  version of Office 2016 (instead of 32 bit), which handles extensive data better, according to this Microsoft article.
• Disable Add-Ins, if any.
• Remove the temporary file created by shared workbooks.

Next Steps

1- Determine Excel Version

What version of Excel are you using? Please note that Microsoft Office 2010 has some issues for shared Workbooks, which are fixed in the later Excel versions. However, Office 2013, 2016 or 2019 has their issues for shared files, which might or might not be the same as other versions.

2- Outline Causes of Errors

Once the version of Microsoft Office is detected, let’s make a list of potential causes. When do you usually get errors in a shared workbook? What actions did you or other users perform that led to this error?

3- Apply Solutions

Once you determine the Excel version and potential causes, try the listed solutions that are relevant to your version of  Excel and the possible causes. Please note, some of the solutions might not work in your current version of Excel. And some of the solutions are not relevant to your case, for example, Visual Basic components might be already installed on your computer.

VBA/Macro Development

Some of the outlined solutions might require a custom macro. We understand that you or anyone in your team might not have the skills of VBA coding. We are specialized in VBA/Macro development and automating Excel workbooks. Please feel free to contact us for VBA/Macro Consultation.