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 (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.) 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

  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 (merged cell: A single cell that is created by combining two or more selected cells. The cell reference for a merged cell is the upper-left cell in the original selected range.), conditional formats (conditional format: A format, such as cell shading or font color, that Excel automatically applies to cells if a specified condition is true.), data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.), charts, pictures, objects including drawing objects, hyperlinks (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an intranet. Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.), scenarios, outlines (outline: Worksheet data in which rows or columns of detail data are grouped so that you can create summary reports. The outline can summarize either an entire worksheet or a selected portion of it.), subtotals, data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.), PivotTable reports (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.), 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 (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input 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 (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) 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

  1. On the Editing tab, select the Allow changes by more than one user at the same time. This also allows workbook merging check box.
  2. On the Advanced tab, select the options that you want to use for tracking and updating changes, and then click OK.
  3. 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 Button image, and then click Save As.
  4. 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.

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

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

  1. 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) (OLE: A program-integration technology that you can use to share information between programs. All Office programs support OLE, so you can share information through linked and embedded objects.) or Dynamic Data Exchange (DDE) (Dynamic Data Exchange (DDE): An established protocol for exchanging data between Microsoft Windows-based programs.).

 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 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 (destination file: The file that a linked or embedded object is inserted into. The source file contains the information that is used to create the object. When you change information in a destination file, the information is not updated in the source file.), 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.

Top of Page 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 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.
  1. 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.

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. 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.
  2. 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.

  3. Make any filter (filter: To display only the rows in a list that satisfy the conditions you specify. You use the AutoFilter command to display rows that match one or more specific values, calculated values, or conditions.) 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.
  • Excel Ribbon Image
  • On the Advanced tab, under Include in personal view, clear the Print settings or Filter settings check box, and then click OK.
  1. 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 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 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.

  1. 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
  1. On the Advanced tab, under Conflicting changes between users, click The changes being saved win, and then click OK.
  1. 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.
  1. In the When list, select All.
  2. Clear the Who and Where check boxes.
  3. Select the List changes on a new sheet check box, and then click OK.
  4. On the History worksheet (History worksheet: A separate worksheet that lists changes being tracked in a shared workbook, including the name of the person who made the change, when and where it was made, what data was deleted or replaced, and how conflicts were resolved.), 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 Button image, click Save As, and then type a new name for the file.

Top of Page 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 (change history: In a shared workbook, information that is maintained about changes made in past editing sessions. The information includes the name of the person who made each change, when the change was made, and what data was changed.) will also be deleted, you may want to start by printing the History worksheet (History worksheet: A separate worksheet that lists changes being tracked in a shared workbook, including the name of the person who made the change, when and where it was made, what data was deleted or replaced, and how conflicts were resolved.) 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
  1. In the When list, select All.
  2. Clear the Who and Where check boxes.
  3. Select the List changes on a new sheet check box, and then click OK.
  4. Do one or both of the following:
    • To print the History worksheet, click Microsoft Office Button 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.

Excel Ribbon Image

 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.

  1. In the shared workbook, on the Review tab, in the Changes group, click Share Workbook.
  2. 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.

  1. 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.
  1. If you are prompted, enter the password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.), and then click OK.
  2. On the Review tab, in the Changes group, click Share Workbook.
  1. On the Editing tab, clear the Allow changes by more than one user at the same time. This also allows workbook merging check box.
  1. When you are prompted about the effects on other users, click Yes.

Top of Page Top of Page

 
 
Applies to:
Excel 2007