Wednesday, May 5, 2021

About the shared workbook feature

Important: "Shared Workbooks" is an older feature that allows you to collaborate on a workbook with multiple people. This feature has many limitations, and has been replaced by co-authoring. Excel co-authoring is available in Microsoft 365 Apps Excel desktop client and Excel for the Web for Microsoft 365 subscribers.

Windows macOS

Enabling the Shared Workbook feature

  1. Be certain that you want to use this method before continuing. Shared Workbooks have limitations, and one in particular is the inability to edit using Excel for the web. Therefore we highly recommend co-authoring, which is the replacement for Shared Workbooks.

  2. Create a new workbook or open an existing workbook. Then place it on a network location. For example, put it on a location like \\server_name\folder_name. Don't put the file on OneDrive or SharePoint. If you prefer those locations for the file, co-author the workbook instead.

  3. Click Review > Share Workbook.

    Note that in newer versions of Excel, the Share Workbook button has been hidden. Here's how to unhide it.

  4. On the Editing tab, select the Allow changes by more than one user ... check box.

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

  6. If this is a new workbook, type a name in the File name box. Or, if this is an existing workbook, click OK to save the workbook.

  7. If the workbook contains links to other workbooks or documents, verify the links and update any links that are broken.

  8. Click File > Save.

  9. When you're done, - Shared will appear at the top of the Excel window, next to the filename.

Additional information

Please be aware that Shared Workbooks is an older method of sharing. Therefore, many items and actions are not supported when this method is used. The table below is only a partial list of features not supported.

Unsupported items:

Unsupported actions:

Creating or inserting tables

Inserting or deleting blocks of cells

Adding or changing conditional formats

Deleting worksheets

Adding or changing data validation

Merging cells or splitting merged cells

Creating or changing charts or PivotChart reports

Sorting or filtering by format

Inserting or changing pictures or other objects

Using drawing tools

Inserting or changing hyperlinks

Assigning, changing, or removing passwords

Creating, changing, or viewing scenarios

Protecting or unprotecting worksheets or the workbook

Inserting automatic subtotals

Grouping or outlining data

Creating data tables

Writing, recording, changing, viewing, or assigning macros

Creating or changing PivotTable reports

Changing or deleting array formulas

Creating or applying slicers

Adding, renaming, or deleting XML maps

Creating or modifying sparklines

Mapping cells to XML elements

Adding or changing Microsoft Excel 4 dialog sheets

Using the XML Source task pane, XML toolbar, or XML commands on the Data menu

Using a data form to add new data

Adding threaded comments

Editing, or deleting threaded comments

Before you disconnect users, make sure that they have completed their work on the workbook. If you remove active users, any unsaved work of theirs will be lost.

  1. Click Review > Share Workbook.

    Note that in newer versions of Excel, the Share Workbook button has been hidden. Here's how to unhide it.

  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. Please note that although this action disconnects the user from the workbook, it does not prevent that user from editing the workbook again.

  4. To delete any personal view settings of the removed user, click View > Custom Views and delete any other user views.

Before you turn off this feature, you might want to make a copy of the change history. First make sure that all other users have completed their work.

  1. Click Review > Track Changes > Highlight Changes.

    Note that in newer versions of Excel, the Track Changes button has been hidden. Here's how to unhide it.

  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. Now you can either print the History worksheet, or you can copy the history and paste it into another workbook.

  1. Click Review > Share Workbook.

    Note that in newer versions of Excel, the Share Workbook button has been hidden. Here's how to unhide it.

  2. On the Editing tab, make sure that you are the only person listed in the Who has this workbook open now list.

  3. Clear the Allow changes by more than one user ... check box. If this check box is not available, you must first unprotect the workbook. 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 ... check box.

    6. When you are prompted about the effects on other users, click Yes.

Enabling the Shared Workbook feature

  1. Be certain that you want to use this method before continuing. Shared Workbooks have limitations, and therefore we highly recommend co-authoring, which is the replacement for Shared Workbooks.

  2. Create a new workbook or open an existing workbook. Then place it on a network location. Don't put the file on OneDrive or SharePoint. If you prefer those locations for the file, co-author the workbook instead.

  3. Click Review > Share Workbook.

  4. On the Editing tab, select the Allow changes by more than one user ... check box.

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

  6. If this is a new workbook, type a name in the Save As box. Or, if this is an existing workbook, click OK to save the workbook.

  7. If the workbook contains links to other workbooks or documents, verify the links and update any links that are broken.

  8. Click File > Save.

  9. When you're done, - Shared will appear at the top of the Excel window, next to the filename.

Additional information

Please be aware that Shared Workbooks is an older method of sharing. Therefore, many items and actions are not supported when this method is used. The table below is only a partial list of features not supported.

Unsupported items:

Unsupported actions:

Creating or inserting tables

Inserting or deleting blocks of cells

Adding or changing conditional formats

Deleting worksheets

Adding or changing data validation

Merging cells or splitting merged cells

Creating or changing charts or PivotChart reports

Sorting or filtering by format

Inserting or changing pictures or other objects

Using drawing tools

Inserting or changing hyperlinks

Assigning, changing, or removing passwords

Creating, changing, or viewing scenarios

Protecting or unprotecting worksheets or the workbook

Inserting automatic subtotals

Grouping or outlining data

Creating data tables

Writing, recording, changing, viewing, or assigning macros

Creating or changing PivotTable reports

Changing or deleting array formulas

Creating or applying slicers

Adding, renaming, or deleting XML maps

Creating or modifying sparklines

Mapping cells to XML elements

Adding or changing Microsoft Excel 4 dialog sheets

Using the XML Source task pane, XML toolbar, or XML commands on the Data menu

Importing, refreshing, and exporting XML data

Using a data form to add new data

Before you disconnect users, make sure that they have completed their work on the workbook. If you remove active users, any unsaved work of theirs will be lost.

  1. Click Review > 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. Please note that although this action disconnects the user from the workbook, it does not prevent that user from editing the workbook again.

  4. To delete any personal view settings of the removed user, click View > Custom Views and delete any other user views.

Before you turn off this feature, you might want to make a copy of the change history. First make sure that all other users have completed their work.

  1. Click Review > Track Changes > 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. Now you can either print the History worksheet, or you can copy the history and paste it into another workbook.

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

  3. Clear the Allow changes by more than one user ... check box. If this check box is not available, you must first unprotect the workbook. Do the following:

    1. Click OK to close the Share Workbook dialog box.

    2. On the Review tab, click Protect Workbook.

    3. If you are prompted, enter the password, and then click OK.

    4. On the Review tab, Share Workbook.

    5. On the Editing tab, clear the Allow changes by more than one user ... check box.

Before you turn off this feature, you might want to make a copy of the change history. First make sure that all other users have completed their work.

  1. Click Review > Track Changes > 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. Now you can either print the History worksheet, or you can copy the history and paste it into another workbook.

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

  3. Clear the Allow changes by more than one user ... check box. If this check box is not available, you must first unprotect the workbook. Do the following:

    1. Click OK to close the Share Workbook dialog box.

    2. On the Review tab, click Protect Workbook.

    3. If you are prompted, enter the password, and then click OK.

    4. On the Review tab, Share Workbook.

    5. On the Editing tab, clear the Allow changes by more than one user ... check box.

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)