How to collaborate in Excel with shared workbooks

When you have a number of colleagues all working on the same set of data, it can be difficult to keep the data in sync; who’s got the latest version? did Bob overwrite my changes?!

Fortunately, there’s a feature in Excel which can help with collaborating on a single workbook and it’s called Shared Workbooks.

The way this works is as follows:

  • You mark an Excel workbook as a shared workbook and save it (in a shared location like a network drive is best).
  • Your colleagues can then make a copy of that workbook where they make their own changes.
  • Once everyone is done, you merge the data from the copies back to the original using built in Excel functionality

The following steps will walk you through the process:

  1. Open the workbook you would like to share with your colleagues
  2. Under the Review tab in the menu ribbon, click the Share Workbook button:
    Excel Share Workbook button
  3. At this point, you may see a warning message about privacy settings:Excel Privacy Settings Warning
    If you do, follow these steps, otherwise, go to step 4.

    1. Click on the File menu:
      Excel File Menu
    2. Select the Options menu item:
      Excel File Options
    3. In the dialog box that appears, select the Trust Center menu item on the left:
      Excel Trust Center Options
    4. Click on the Trust Center Settings… button then click the left menu Privacy Options on the dialog that appears:
      Excel Trust Center Privacy Options
    5. Clear the Remove personal information from file properties on save option:
      Excel Trust Center Privacy Options Remove Personal Information
    6. Click the OK on each of the 2 open dialog boxes to close them and save the setting change
  4. In the Share Workbook dialog box which opens, select the ‘Allow changes by more than one user at the same time. This also allows workbook merging.’ check box:
    Excel Share Workbook Dialog
  5. Click on the Advanced tab and check that the change tracking options are suitable for your needs, change them if not
  6. Click OK to save the settings changes and close the Share Workbook dialog box
  7. You now have 2 options for sharing the workbook with your colleagues:
    • Save the workbook on a network folder and email the location to your colleagues – this is the preferable option. Your colleagues will need to create a copy of the Excel workbook and make their changes in their copy
    • Send the workbook to your colleagues and they make their own copy – you colleagues can then make the changes required in their own copy and email it back to you to merge with the master copy when they are done
  8. Once everyone is done making their changes, you can merge the changes into the master workbook