2 Ways to Share Workbooks Using Co-Authoring or the Legacy Tool

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

In this post you'll discover 2 ways to share workbooks, using the legacy Share tool and the new Co-Author tool.

1. Co-authoring - the new way to share workbooks

Co-authoring is a way for multiple people to open and work on the same workbook at the same time and see each other's changes almost instantly. Co-authoring utilises the cloud and has replaced the old Share Workbooks feature.

Here are some things to be aware of:

  • Co-authoring is only available on Office 365, Excel for the web, Excel for Android, Excel Mobile and Excel for iOS. And only if you have an active Office 365 subscription and are signed in.
  • Some versions of Excel let you see other people's selections in different colours.

2. How to share workbooks using co-authoring

1.  Save your workbook to a cloud location (OneDrive or a SharePoint Online library).

2.  Click Share in the top-right corner of the Excel screen.

The co-author tool

3.  Alternatively, select the File tab then click Share on the left sidebar, then click Share with People, then Share with People again.

How to share workbooks using the new co-authoring tool

    Either way, you'll see the Share sidebar on the main Excel screen.

4.  In the Share sidebar, type an email address or a number of email addresses separated by a semicolon ( ; ) choose whether they can edit or view the workbook, add a short message if you want, then click Share.

The invitation is sent and the sidebar now shows who has shared access to the workbook.

Invite people to share workbooks

If you want to manually send a link to the file:

1.  Click Get a sharing link at the bottom of the Share sidebar.

2.  Click either the Create an edit link button or the Create a view-only link button.

3.  When the special link is displayed, click the  button then paste the link into an email.

Create an edit link

3. Managing co-authored workbooks

To remove a user:

  • In the Share sidebar, right-click the person you wish to remove and choose Remove user.

To change the permissions of a user (view or edit)"

  • In the Share sidebar, right-click the person you wish to remove and choose Change permission. The option toggles depending on to the current settings.

To open a shared workbook from an email invitation:

1.  Click the link in the email to open the workbook. A web browser will open, and the workbook will open in Excel for the web.

2.  If you have an active subscription, click Edit in Desktop App to edit using the full feature set.

To open a shared workbook from the Excel Desktop app:

1.  Open the file from the cloud location as you would for any other workbook. Alternatively, select the File tab, then Open, then Shared with Me.

2.  A yellow bar may appear that states that the file is in Protected View. Click the Enable Editing button.

4. Legacy tool:  Share workbooks

Up until Excel 2019, workbooks were locked when opened but could be shared which meant that multiple people could make changes to the same spreadsheet at the same time. Well, not exactly the same time, because potential conflicts could occur if two people change the same thing at the same time. This was controlled with some simple rules which controlled which change was applied?.

Tracking changes was also part of the Sharing feature but is now a legacy tool.

When you attempted to open a locked workbook, a message was displayed offering 3 choices:

  • Wait a while and try again.
  • Open as Read Only.
  • Receive notification when the file becomes available.

Because Sharing Workbooks is now a legacy feature, it has been removed from the Review ribbon and you must customise the Quick Access Toolbar (QAT) in order to access it. There is a separate chapter covering this.

1.  Select the File tab and choose Options at the bottom of the left sidebar.

2.  On the Options dialog, select Quick Access Toolbar on the left sidebar.

3.  Select All Commands or Commands not in the ribbon from the Choose commands from list.

4.  Select Share Workbook (Legacy) and click Add.

5.  If you need it, you can also select Track Changes (Legacy), Protect Sharing (Legacy) and Compare and Merge Workbooks and click Add for each one.

6.  Click OK to close the options dialog. The new icons are on the Quick Access Toolbar.

5. Allowing a workbook to be shared

To enable the workbook to be shared:

1.  Click the Share Workbook icon on the Quick Access Toolbar.

2.  Click the Editing tab, if necessary.

If sharing has previously been enabled for the workbook then all users who are currently using the workbook are listed. If it is not currently shared, it will show you as the exclusive user.

3.  Tick the box labelled Use the old shared workbooks feature instead of the new co-authoring experience.

This enables the workbook to be shared and also activates the Advanced tab.

The Advanced Tab is where updates to the workbook are controlled and the rules regarding conflicts are established.

1.  Click the Advanced tab.

If someone has been nominated as the owner of the workbook, it is wise to activate tracking. This records any changes that are made and allows the owner to override any changes that have been made

2.  Specify when changes should be updated.

You can either update the workbook when you save or allow Excel to save any updates periodically (e.g. every 10 minutes).

The settings for the Share Workbook dialog

3.  The bottom section of the dialog box is the most important. Specify what action should be taken when two users change (and save) the same cell(s) concurrently.

  • Ask me which changes win – this is the best option because it’s flexible. Both users will receive the message, so it’s then down to old-fashioned communication to sort it out.  
  • The changes being saved win – gives precedence to the first person to save, but it is harsh because it overwrites the other user’s changes.

4.  Click Ok when you have chosen the sharing behaviours.

5.  Workbooks that are shared display [Shared] after the worksheet name on the title bar.

6. Tracking changes made to a workbook

Depending on the sensitivity of the data, it may be a good idea to track any changes made. This includes anything you add, change or delete is recorded, and your name is associated with the change.

To switch on the Track Changes feature:

1.  Click the Track Changes icon on the Quick Access Toolbar, then Highlight Changes.

The Highlight Changes dialog controls what changes are tracked

2.  Choose when you want changed tracked from  The choices are:

  • Since I last saved 
  • All 
  • Not yet reviewed, or 
  • Since Date …

3.  Select who you want to track changes for. The choices are:

  • Everyone 
  • Everyone but me
  • You

4.  Select where (a specific cell range) you want to track or leave the box empty to track changes for the whole workbook.

5.  Choose whether to Highlight changes on the screen and/or list changes on a new sheet, so they can be reviewed later.

6.  Click OK.

7.  Save the workbook when prompted to do so.

Changes are now tracked.

Changes are now tracked

When you are ready to finalise changes, you will need to decide whether to accept or reject each change.

Here’s how:

1.  Click the Track Changes icon on the Quick Access Toolbar, then Accept/Reject Changes.

2.  Click OK when yo are prompted to save the workbook.

This action will now save the workbook. Do you want to continue?

3.  Filter the changes you want to check by setting the When, Who and Where options, then click OK

Select changes to accept or reject

   All the changes that match your criteria are listed, one at a time.

Accept or reject changes

4.  For each change choose Accept, Accept All, Reject or Reject All

If more than one change has been made to a cell, all the changes for the cell will be listed and you can choose which one to Accept.

7. Key Takeaways

  • Sharing workbooks allow a workbook to be viewed and editied by more than one person simultaneously.
  • To share a workbook, select the Review tab and click Share Workbook icon.
  • You must first tick the box on the Editing tab to allow the workbook to be shared.
  • The Advanced tab controls the behaviour of shared workbooks. It states to track changes when updates are made to the workbook and most importantly, how to handle conflict.
  • You can track changes made to a workbook, or part of a workbook. The Track Changes feature is on the Review tab, right-hand side.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Office Legends Academy | Making Office Legends

Master Microsoft Office
in 30 days or less

more from

Office Mastery logo
Read More
SOS-Click: The Easy Automated Backup Tool For Home Users
Read More
6 Excel Templates to Help Organize Your Daily Activities
Read More
10 Ways Excel Consultants Can Help You Fully Leverage Your Business Spreadsheets
Read More
How to fix formulas: 21 Fatal Formula Fails (and how to avoid them)

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>