Excel workbooks – shared or collaboration mode to add or modify data simultaneously

Microsoft Excel workbooks can be setup as “shared” and placed on a network server for multiple users to add or modify data simultaneously.

How to allow multiple users to edit a workbook simultaneously?

Microsoft Excel workbooks can be shared after the owner of the workbook, enters and formats the data that need to be present in the shared excel workbook. When this task is finished,

  • click Share Workbook in the Changes group on the Review tab,
  • select your needed options and
  • finally save the workbook on a network share (not a web server), that is available to the intended users.

Note that you can you can prevent other users from turning off Change Tracking in the workbook by clicking “Protect and Share Workbook” in the Changes group on the Review tab.

When an excel workbook is shared, each time an user saves the shared workbook, he is prompted with the changes that other users have saved since the last time that he saved the shared workbook.

If you want to keep the shared workbook open to monitor progress, Excel can even update you with the changes automatically, at timed intervals that you specify, with or without saving the workbook yourself.

What happens when other users make changes to the excel workbook, in the same cells that you work on?

When you save changes to a shared workbook, another person who is editing the workbook might have saved changes to the same cells. In this case, the changes conflict, and you are prompted with a conflict resolution dialog box so that you can choose which changes to keep.

When can excel workbooks be shared?

Excel workbooks can be shared when:

  • you want multiple users to be able to edit the data in one workbook simultaneously, and you are comfortable with the original data in the workbook being modified, including edits, additions, and deletions.
  • You have a network share available on which to store the workbook and to which users have access.
  • You want to keep a record of the changes that are made in the workbook.
  • You do not expect to change the following features, which cannot be modified after a workbook is shared: merged cells, conditional formats, data validation, charts, pictures, objects (including drawing objects), hyperlinks, scenarios, outlines, subtotals, data tables, Pivot Table reports, workbook and worksheet protection, and macros.

One comment on “Excel workbooks – shared or collaboration mode to add or modify data simultaneously

  1. Sharing workbooks often leads to merging of changed worksheets. Sadly, only 12 workbooks can be merged at 1 time. If on-line access is not convenient, workbook sharing gets very challenging with just Excel. Adding Distributed Spreaddsheet, an add-in for Excel, can remove virtually all the limitations Excel imposes on sharing and merging of workbooks.

    Worth a look.

Leave a Reply

Your email address will not be published. Required fields are marked *