Link an Excel worksheet into a shared workbook

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

In How to share a single Excel worksheet or tab we explained how to send or share a single Excel worksheet or tab from a larger workbook.

That’s OK as a one-off measure to send out a sheet but how can you do it easily as a share or ongoing collaboration?

The answer is a little messy because Excel lacks some linking controls at the worksheet level.

We start with a single workbook that has one sheet that you want to share.

link an excel worksheet into a larger workbook 12086 - Link an Excel worksheet into a shared workbook

And other workbook that has only the information you want to share.

  • Create a new worksheet shared on OneDrive, Sharepoint, network share etc.
  • Select the cells you want to share from the source worksheet.
  • Paste Link into the shared worksheet.

This copies the latest version of the original cells.  You should add / restore the cell formatting.

link an excel worksheet into a sharedworkbook 12088 - Link an Excel worksheet into a shared workbook

Two Way Sharing

You can link the worksheets either way (combined workbook linked to the Public only document (as above) or Public to combined workbook).   The choice depends on your situation.

The shared or public worksheet might have incoming information from collaborators that’s then included in Private calculations on another worksheet.  The private worksheet can Paste Link data from the shared worksheet.

Or the shared worksheet could be displaying the results of a larger and private workbook.

In this case, when the shared worksheet is opened elsewhere the viewers get this warning.  ‘Links to external workbooks are not supported and have been disabled’.

link an excel worksheet into a sharedworkbook 12090 - Link an Excel worksheet into a shared workbook

That’s because the shared workbook doesn’t have access to the source workbook.  It shows the values available when the .xls was last opened with working links to the source.

It’s up to the owner of both worksheets to open the public version locally and allow Excel to update the cells from source worksheet.

subs profile e1563205311409 - Link an Excel worksheet into a shared workbook
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address