Use a shared workbook to collaborate

Try Office 2010 In Excel 2010, take advantage of saving your workbooks to a SharePoint site. The Excel Web App, available on a server that uses Microsoft SharePoint 2010 technology, allows multiple users to edit data in a worksheet in the browser at the same time.
Read a blog post or try Office 2010!

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.

Sharing Excel data

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

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

    Excel Ribbon Image

  2. On the Editing tab, select the Allow changes by more than one user at the same time. This also allows workbook merging check box.

  3. On the Advanced tab, select the options that you want to use for tracking and updating changes, and then click OK.

  4. 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 Office button image, and then click Save As.

  5. On a computer that is running Windows Vista    

    • In the Address bar, select a network location that is accessible to the intended users, and then click Save.

      On a computer that is running Microsoft Windows XP    

    • 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.

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

    Quick access toolbar

    How to verify and update links to other workbooks or documents

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

      Excel Ribbon Image

      Note   The Edit Links 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 Office button image, 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.

Note   

  • 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.

Top of Page

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 Office button image, and then click Open.

    2. On a computer that is running Windows Vista    

      • In the Address bar, locate the network location where the shared workbook is stored, and then click the shared workbook.

        On a computer that is running Microsoft Windows XP    

        • 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 Office button image, 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.

    1. 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.

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

    2. How to use the original filter and print settings

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

      2. Excel Ribbon Image

      3. 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 Button image on the Quick Access Toolbar, or press CTRL+S.

    Quick access toolbar

    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.

Top of Page

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.

Excel Ribbon Image

  1. On the Editing tab, in the Who has this workbook open now list, review the names of users.

  2. 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.

  1. 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.
      Excel Ribbon Image

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

Top of Page

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.
      Excel Ribbon Image

    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 Office button image, click Save As, and then type a new name for the file.

Top of Page

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.
      Excel Ribbon Image

    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 Office button image, and then click Print .

      • To copy the history to another workbook, select the cells that you want to copy, click Copy Button image 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 Button image on the Home tab in the Clipboard group.

        The Clipboard group on the Home tab

        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.

Top of Page

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language