Synchronization issues with multiple users editing excel spreadsheet model (.xlsb)

Question

Hi,

We are experiencing issues with a spreadsheet model we have created in Excel (.xlsb file extension), where we are getting synchronization issues when multiple users are editing the spreadsheet model simultaneously. Normal editing of values in cells does not generate any issues, but updating pivot tables, insert/delete rows/columns and copy/paste of formulas do. 

We suspected that different excel versions could be the root cause, but we have tested with multiple users editing the spreadsheet with the same version of excel and the problem persists.

Any ideas for how this can be solved or potential work arounds would be highly appreciated.

Kind regards,

Eyvind

Answer 1

Hi Eyvind,

Sorry for the trouble caused at your end.

Per your description, we understand that users in your organization are encountering co-authoring problems when working on an Excel file with .xlsb format. Since you’re not having problem while editing the cells normally and only problem happens when refreshing the PivotTable, Insert/Delete Columns or Rows, and Copy/Paste of formula’s, we’ve tested at our end by performing the same actions in .xlsb format where we can’t reproduce any problem at our end.

To narrow down the problem, we would like you to test the following scenarios with other users in your organization:

a. Edit the problematic Excel workbook in Excel Online and check whether you encounter any problem.

b. Create a new Excel workbook (.xlsb format) and check whether you encounter the same problem when you co-author via Excel application.

c. Save the problematic Excel workbook (.xlsb format) as .xlsx format and upload it to your SharePoint Document library to test.

Please share with us the test results and in addition, can you please provide the screenshot of the error message encountered at your end?

Appreciate your understanding.

Best Regards,

Chitrahaas

Answer 2

Hi Eyvind,

Please share the test results with us when you have time and we will continue to follow up on the thread.

Thanks,

Chitrahaas

Answer 3

Hi Chitrahaas,

Thank you for Your quick response to my question.

The issue is related to co-Authoring, so you have understood our problem correctly. We will test your proposed solutions in the beginning of NeXT week and get respond to this thread With the results.

Kind regards,

Eyvind

Answer 4

Hi Eyvind,

You can feel free to share your test results when it's done completely and in your free time.

Thanks,

Chitrahaas

Answer 5

Hi Chitrahaas,

Thanks again for Your quick response and follow-up on this question.

We have tried the soultions you proposed with the following results:

a) Seems to solve the synchronization issue, but editing the spreadsheet in Excel online is not as effective as the desktop app. Thus, for the use of this spreadsheet, we will need to find a different solution

b) Works fine with files that are smaller in size and has less complexity. The problematic spreadsheet has a file size of 3,5 Mb, With multiple "If" and "Index" formulas calculating across 1000 rows

c) The file is already uploaded and stored on Sharepoint.

We will proceed With testing a work-around where we split the spreadsheet in two - one input file for entering data and performing calculations, and one file for viewing the output data in pivot tables.

Do you have any other suggestions for solutions or work arounds that might work?

Kind regards,

Eyvind Pedersen