Excel: Enable Sharing

Enable the legacy sharing buttons

Excel 365 has removed the Share Workbook (Legacy) feature as they are pushing cloud integration with Sharepoint. But if you're working on a network you can still enable Share Workbook (Legacy) using the following steps:

Share a specific workbook

WARNING: Make sure you make an archive copy of the spreadsheet at regular intervals. One of the reasons that Microsoft deprecated Share Workbook (Legacy) is that it was prone to losing data, or locking people out, or not working if people have accessed the file from a mapped network drive, or if they overwrite the file with a copy, etc etc.

1. Copy the file to network share.

2. Check that the file path is a a full path name like \\server\folder\

3. Open the Excel file from \\server\folder\
Do not open it from a mapped network drive. If you open it from a mapped network drive, this will disable Share Workbook (Legacy). If you use a mapped network drive, Excel may treat it as a local file, thus stealing exclusive access to the Excel spreadsheet and locking all other users out. If you get an error message when saving saying that "you are no longer connected to this file", you need to navigate to the actual \\server\folder\ location. See Troubleshooting to see how to work this out.

4. Share the Workbook:

NOTE: If this button is greyed out or an error message appears, follow the Troubleshooting section below.

5. Follow the wizard to turn on the feature and set the update rate:

6. Save the Excel file.

7. Close the Excel file.

8. Openthe Excel file.

9. Grab the URL: File > Info > Copy Path

10. In Outlook, create a new email. NOTE: Outlook is the only guaranteed method of sending the URL so that no one gets blocked. Outlook automatically converts the URL to the full UNC path, even if you use a network drive letter. Other methods such as navigating with Explorer may occasionally use the network drive letter and thus lock the Excel file.

11. In the email body, right-click and select Paste. NOTE: Do not press CTRL-V, as this will break the URL if there are spaces.

12. Press spacebar to turn the text into a proper full URL.

13. To check who currently has the workbook open, click on "Share Workbook (Legacy)":

14. Once other people begin using the spreadsheet, you will notice that each person will have their own personal view which saves any columns, filters, and print settings unique to that user. Occasionally you may need to clean these views up by deleting them so that everyone will have the same view:

Troubleshooting

How to fix "You are no longer connected to this file"

This problem occurs if you have opened the file from a mapped network drive. Instead you need to use the Universal Naming Convention (UNC) filepath \\server\folder\

The quickest way to find the actual UNC location is to click File > Info > Open file location:

You will need to close Excel first before reopening the file from this UNC location \\server\folder\.

Find your Network Drive mapping

To find out your network drives, perform these steps:

1. Win + R > cmd > OK

2. net use

Cannot turn on Share Workbook (Legacy)

You may get the following error message when trying to share a workbook:

If there are Tables, then you need to convert these to Ranges:

If you find that Share Wookbook (Legacy) is still disabled, this may be due to Hidden sheets. Follow this screenshot to unhide these sheets:

If you find that Share Wookbook (Legacy) is still disabled, there may be "Very Hidden" sheets(!!), which are only accessible in VBA Editor (ALT-F11). To unhide these sheets, modify the Visible property:

As per the prompt, there may also be XML maps. Follow the instructions in the prompt to remove XML maps.

https://support.microsoft.com/en-us/office/overview-of-xml-in-excel-f11faa7e-63ae-4166-b3ac-c9e9752a7d80 

"XML maps allows you to add, identify, and extract specific pieces of business data from Excel documents. For example, an invoice that contains the name and address of a customer or a report that contains last quarter's financial results are no longer just static reports. You can easily import this information from databases and applications, revise it, and export it to the same or other databases and applications."

Unsupported Features

Microsoft recommends using co-authoring instead of Share Workbook (Legacy) feature because there are many limitations. Below is the limitation list from https://support.microsoft.com/en-us/office/about-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2-c1c59a628534 

Excel Share Workbook (Legacy) doesn't support the following items:

• Creating or inserting tables

• Adding or changing conditional formats

• Adding or changing data validation

• Creating or changing charts or PivotChart reports

• Inserting or changing pictures or other objects

• Inserting or changing hyperlinks

• Creating, changing, or viewing scenarios

• Inserting automatic subtotals

• Creating data tables

• Creating or changing PivotTable reports

• Creating or applying slicers

• Creating or modifying sparklines

• Adding or changing Microsoft Excel 4 dialog sheets

• Adding threaded comments

Excel Shared Workbooks (Legacy) doesn't support the following actions:

• Unsupported actions:

• Inserting or deleting blocks of cells

• Deleting worksheets

• Merging cells or splitting merged cells

• Sorting or filtering by format

• Using drawing tools

• Assigning, changing, or removing passwords

• Protecting or unprotecting worksheets or the workbook

• Grouping or outlining data

• Writing, recording, changing, viewing, or assigning macros

• Changing or deleting array formulas

• Adding, renaming, or deleting XML maps

• Mapping cells to XML elements

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

• Using a data form to add new data

• Editing, or deleting threaded comments