Keep Linked Excel Data Visible When a SharePoint Source Is Closed
Question details
Keep source data visible in a destination Excel workbook when the SharePoint source workbook is closed, including when the destination is shared externally.

- Product
- Microsoft Excel and SharePoint
- Device & OS
- not provided
- Scenario
- A destination workbook refers to data in a SharePoint source workbook and is shared with external users.
- Observed behavior
- The destination does not reliably display or refresh the linked data when the source workbook is closed, particularly for external users.
Confirm the SharePoint site address, the source workbook location, and whether external users have permission to access the source. Check which source range or table needs to appear in the destination workbook.
Import the SharePoint source table with Power Query
Use Power Query to load a saved copy of the source data into the destination workbook and refresh it when authorized access is available.
The loaded table can remain visible after the source workbook is closed, but it reflects the last successful refresh. Anyone who needs to refresh it must have access to the source and use a supported Excel environment.
Open the source workbook in Excel and identify the table to import. If the data is not already a table, select its range and choose Insert > Table, then save the workbook to SharePoint.
Open the destination workbook in desktop Excel and select Data > Get Data > From File > From SharePoint Folder. Enter the SharePoint site URL and sign in with an account that can access the source.
Choose Transform Data, filter the file list to the source workbook, and select its Binary content. In the resulting navigation view, select the source table.
Choose Close & Load to place the table in the destination workbook, then save the destination. Close the source workbook and reopen the destination to check that the last loaded data is visible.
Select Data > Refresh All and verify that the data updates. For external users who need to refresh, have them test access to the SharePoint source and sign in with their own authorized account.

Save a static data snapshot in the destination workbook
Use a values-only copy when external recipients need to view the data but do not need to refresh it directly.
Work with Excel snapshots in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and work with saved data snapshots for an easy transition, while managing SharePoint access and refresh requirements separately.

Frequently Asked Questions
Can Excel show linked values when the source workbook is closed?
Excel can display previously saved values for external workbook links, but those values may be outdated or unavailable. Open the destination workbook and check Data > Workbook Links or Edit Links, depending on your Excel version, to inspect the links.
Why can an external user see the destination but not refresh the data?
Permission to the destination does not automatically grant permission to the SharePoint source. Check the source file's Share settings and your organization's external sharing policies, then have the user sign in with an authorized account.
Will a Power Query table update automatically when the source changes?
No. The destination displays the last successfully loaded data until a refresh occurs. Use Data > Refresh All in a supported Excel environment with access to the source.
Can recipients view a snapshot without access to the source workbook?
Yes. Values pasted into the destination or data saved in a loaded table can be viewed from the destination without opening the source. Recipients still need access to the source if they are expected to refresh the imported data.




