Use a shared workbook to collaborate

You can create a shared workbook and place it on a network location where several people can edit the contents simultaneously. For example, if the people in your work group each handle several projects and need to know the status of each other's projects, the group can use a shared workbook to track the status of the projects. All persons involved can then enter the information for their projects in the same workbook.

As the owner of the shared workbook, you can manage it by removing users from the shared workbook and resolving conflicting changes. When all changes have been incorporated, you can stop sharing the workbook.

What do you want to do?

Share a workbook

Edit a shared workbook

Remove a user from a shared workbook

Resolve conflicting changes in a shared workbook

Stop sharing a workbook

Share a workbook

1. Create a new workbook and enter any data that you want to provide, or open an existing workbook that you want to make available for multiuser editing.

   Note Not all features are supported in a shared workbook. If you want to include any of the following features, you should add them before you save the workbook as a shared workbook: merged cells, conditional formats, data validation, charts, pictures, objects including drawing objects, hyperlinks, scenarios, outlines, subtotals, data tables, PivotTable reports, workbook and worksheet protection, and macros. You cannot make changes to these features after you share the workbook.

   Features that are not supported in a shared workbook

   In a shared workbook, you cannot	But you may be able to do the following
   Create an Excel table	None
   Insert or delete blocks of cells	You can insert entire rows and columns.
   Delete worksheets	None
   Merge cells or split merged cells	None
   Add or change conditional formats	Existing conditional formats continue to appear as cell values change, but you can't change these formats or redefine the conditions.
   Add or change data validation	Cells continue to be validated when you type new values, but you can't change existing data validation settings.
   Create or change charts or PivotChart reports	You can view existing charts and reports.
   Insert or change pictures or other objects	You can view existing pictures and objects.
   Insert or change hyperlinks	Existing hyperlinks continue to work.
   Use drawing tools	You can view existing drawings and graphics.
   Assign, change, or remove passwords	Existing passwords remain in effect.
   Protect or unprotect worksheets or the workbook	Existing protection remains in effect.
   Create, change, or view scenarios	None
   Group or outline data	You can continue to use existing outlines.
   Insert automatic subtotals	You can view existing subtotals.
   Create data tables	You can view existing data tables.
   Create or change PivotTable reports	You can view existing reports.
   Write, record, change, view, or assign macros	You can run existing macros that don't access unavailable features. You can record shared workbook operations into a macro stored in another nonshared workbook.
   Add or change Microsoft Excel 4 dialog sheets	None
   Change or delete array formulas	Existing array formulas continue to calculate correctly.
   Use a data form to add new data	You can use a data form to find a record.
   Work with XML data, including: Import, refresh, and export XML data Add, rename, or delete XML maps Map cells to XML elements Use the XML Source task pane, XML toolbar, or XML commands on the Data menu	None

2. On the Review tab, in the Changes group, click Share Workbook.

   

3. On the Editing tab, select the Allow changes by more than one user at the same time. This also allows workbook merging check box.
4. On the Advanced tab, select the options that you want to use for tracking and updating changes, and then click OK.
5. Do one of the following:
   • If this is a new workbook, type a name in the File name box.
   • If this is an existing workbook, click OK to save the workbook, click Microsoft Office Button , and then click Save As.
6. In the Save in box, select a network location that is accessible to the intended users, and then click Save.

   Note You should use a shared network folder, not a Web server.

7. If the workbook contains links to other workbooks or documents, verify the links and update any links that are broken, and then click Save on the Quick Access Toolbar, or press CTRL+S.

   

   How to verify and update links to other workbooks or documents

   1. On the Data tab, in the Connections group, click Edit Links.

      

      Note The Edit Links to Files command is unavailable if your file does not contain linked information.

   2. Click Check Status to verify the status for all links in the list.

      Note This may take a while if there are many links, or if the source workbook for the links is on a network location, and the network is slow.

   3. Check the status in the Status column, click the link, and then take the action that is needed.

      If the status is	Take this action
      OK	No action is required. The link is working and up to date.
      Unknown	Click Check Status to update the status for all links in the list.
      N/A	The link uses Object Linking and Embedding (OLE) or Dynamic Data Exchange (DDE) . Note Microsoft Office Excel cannot check the status of these types of links.
      Error: Source not found	Click Change Source, and then select another workbook.
      Error: Worksheet not found	Click Change Source, and then select another worksheet. The source may have been moved or renamed.
      Warning: Values not updated	Click Update Values. The link was not updated when the workbook was opened.
      Warning: Click Open Source, and calculate the workbook by pressing F9	The workbook may be set to manual calculation. To set the workbook to automatic calculation, click Microsoft Office Button , and then click Excel Options. In the Formulas category, under Calculation options, click Automatically.
      Warning: Some names cannot be resolved until the source workbook is opened	Click Open Source, switch back to the destination workbook, and then click Check Status. If this does not resolve the problem, make sure that the name is not misspelled or missing. Switch to the source workbook, and then on the Formulas tab, in the Named Cells group, click Name Manager, and look for the name.
      Warning: Click Open Source	The link cannot be updated until the source is open.
      Source is open	The status of a link cannot be checked.
      Values updated from file name	No action is required. The values have been updated.
      Warning: Excel cannot determine the status of the link	The source may contain no worksheets or may be saved in an unsupported file format. Click Update Values.

Notes:

• All users with access to the network share have full access to the shared workbook unless you lock cells and protect the worksheet to restrict access. To protect a shared workbook, click Protect and Share Workbook in the Changes group on the Review tab. When you protect a shared workbook, you can set a password that all users must enter to open the workbook.
• To edit the shared workbook, all users must have one of the following installed on their computers: Microsoft Office Excel, Microsoft Excel 97 or later, or Microsoft Excel 98 or later for Macintosh.
• Not all Excel features are supported in a shared workbook. For more information, see the table "Features that are not supported in a shared workbook" above.

Edit a shared workbook

After you open a shared workbook, you can enter and change data as you do in a regular workbook.

1. Open the shared workbook.

   How to open a shared workbook

   1. Click Microsoft Office Button , and then click Open.
   2. In the Look in box, locate the network location where the shared workbook is stored, and then click the shared workbook.
   3. Click Open.

   Tip You can also go to the network location where the shared workbook is stored and double-click the shared workbook to open it.

2. Click the Microsoft Office Button , and then click Excel Options.
3. In the Popular category, under Personalize your copy of Office, in the User Name box, enter the user name that you want to use to identify your work in the shared workbook, and then click OK.
4. Enter and edit data on the worksheets as usual.

   Note You won't be able to add or change the following: merged cells, conditional formats, data validation, charts, pictures, objects including drawing objects, hyperlinks, scenarios, outlines, subtotals, data tables, PivotTable reports, workbook and worksheet protection, and macros.

5. Make any filter and print settings that you want for your personal use. Each user's settings are saved individually by default.

   Tip You can also use the filter or print settings that were made by the owner of the workbook whenever you open the workbook.

   How to use the original filter and print settings

   1. On the Review tab, in the Changes group, click Share Workbook.

      

   2. On the Advanced tab, under Include in personal view, clear the Print settings or Filter settings check box, and then click OK.
6. To save your changes to the workbook and see the changes that other users have saved since your last save, click Save on the Quick Access Toolbar, or press CTRL+S.

   

   If the Resolve Conflicts dialog box appears, resolve the conflicts.

   Tip To resolve conflicts, see Resolve conflicting changes in a shared workbook in this topic.

Notes:

• You can see who else has the workbook open on the Editing tab of the Share Workbook dialog box (Review tab, Changes group, Share Workbook button).
• You can choose to get automatic updates of the other users' changes periodically, with or without saving, under Update changes on the Advanced tab of the Shared Workbook dialog box.

Remove a user from a shared workbook

If needed, you can disconnect users from a shared workbook.

Note Before disconnecting users, make sure that they have completed their work on the workbook. If you remove an active user, any unsaved work will be lost.

1. On the Review tab, in the Changes group, click Share Workbook.

   

2. On the Editing tab, in the Who has this workbook open now list, review the names of users.
3. Select the name of the user who you want to disconnect, and then click Remove User.

   Note Although this action disconnects the user from the shared workbook, it does not prevent that user from editing the shared workbook again.

4. To delete any personal view settings of the removed user, do the following:
   1. On the View tab, in the Workbook Views group, click Custom Views.

      

   2. In the Views list, select the view of another user, and then click Delete.

Resolve conflicting changes in a shared workbook

A conflict happens when two users are both editing the same shared workbook and try to save changes that affect the same cell. Excel can keep only one of the changes in that cell. When the second user saves the workbook, Excel displays the Resolve Conflicts dialog box.

1. In the Resolve Conflicts dialog box, read the information about each change and the conflicting changes made by the other user.
2. To keep your change or the other person's change and to advance to the next conflicting change, click Accept Mine or Accept Other.

   To keep all of your remaining changes or all of the other user's changes, click Accept All Mine or Accept All Others.

3. To have your changes override all other changes without displaying the Resolve Conflicts dialog box again, do the following:
   1. On the Review tab, in the Changes group, click Share Workbook.

      

   2. On the Advanced tab, under Conflicting changes between users, click The changes being saved win, and then click OK.
4. To view how you or others resolved past conflicts, do the following:
   1. On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.
   2. In the When list, select All.
   3. Clear the Who and Where check boxes.
   4. Select the List changes on a new sheet check box, and then click OK.
   5. On the History worksheet, scroll to the right to view the Action Type and Losing Action columns.

      Note Conflicting changes that were kept have Won for Action Type. The row numbers in the Losing Action column identify the rows with information about the conflicting changes that were not kept, including any deleted data.

   Tip To save a copy of the workbook with all your changes, click Cancel in the Resolve Conflicts dialog box, click Microsoft Office Button , click Save As, and then type a new name for the file.

Stop sharing a workbook

Before you stop sharing the workbook, make sure that all other users have completed their work. Any unsaved changes will be lost. Because the change history will also be deleted, you may want to start by printing the History worksheet or by copying it to another workbook.

1. To keep a copy of the change history information, do the following:
   1. On the Review tab, in the Changes group, click Track Changes, and then click Highlight Changes.

      

   2. In the When list, select All.
   3. Clear the Who and Where check boxes.
   4. Select the List changes on a new sheet check box, and then click OK.
   5. Do one or both of the following:
      • To print the History worksheet, click Microsoft Office Button , and then click Print.
      • To copy the history to another workbook, select the cells that you want to copy, click Copy on the Home tab in the Clipboard group, switch to another workbook, click where you want to place the copied data, and then click Paste on the Home tab in the Clipboard group.

        

      Note You may also want to save or print the current version of the workbook, because this history data might not apply to later versions of the workbook. For example, cell locations, including row numbers, in the copied history may no longer be current.

2. In the shared workbook, on the Review tab, in the Changes group, click Share Workbook.
3. On the Editing tab, make sure that you are the only person listed in the Who has this workbook open now list.

   Tip To remove any additional users, see Remove a user from a shared workbook in this topic.

4. Clear the Allow changes by more than one user at the same time. This also allows workbook merging check box.

   Note If this check box is not available, you must first unprotect the workbook. To remove shared workbook protection, do the following:

   1. Click OK to close the Share Workbook dialog box.
   2. On the Review tab, in the Changes group, click Unprotect Shared Workbook.
   3. If you are prompted, enter the password, and then click OK.
   4. On the Review tab, in the Changes group, click Share Workbook.
   5. On the Editing tab, clear the Allow changes by more than one user at the same time. This also allows workbook merging check box.
5. When you are prompted about the effects on other users, click Yes.

See also:

• Allow cells to be edited in a protected worksheet
• Protect worksheet or workbook elements