Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
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.
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.
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’.
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.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.