SharePoint Document Issues

Keep Linked Excel Data Visible When a SharePoint Source Is Closed

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

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.

Keep Linked Excel Data Visible When a SharePoint Source Is Closed
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.
Before you start

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.

Solution 1Recommended

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.

1
Identify the source table

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.

2
Connect 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.

3
Select the workbook and table

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.

4
Load and save the data

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.

5
Test refresh access

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.

Import the SharePoint source table with Power Query
Refresh and sharing limits: Power Query does not bypass SharePoint permissions. Refresh availability can vary by Excel version and environment. The loaded data is stored in the destination workbook, so share it only with people allowed to see that data.
Free Microsoft Office alternative

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.

View and edit saved spreadsheet data without an active workbook linkCompatibility with Microsoft Excel XLS and XLSX formatsFamiliar spreadsheet interface for an easier transitionFree and lightweight office suite
QA img-9

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.