Fix Excel Linked Workbook Data Not Updating from SharePoint
Question details
Data in a main Excel workbook fails to update when changes are made to linked external workbooks stored on SharePoint or OneDrive.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user is trying to refresh a master workbook that aggregates data from multiple individual source files hosted in cloud storage.
- Observed behavior
- Changes made and saved in the individual source workbooks do not appear in the main workbook due to link disconnection, blocked content, or incorrect refresh settings.
Verify that you have an active internet connection, are signed into the correct Microsoft account, and have the necessary read permissions for all source folders on SharePoint or OneDrive.
Refresh Data and Verify Workbook Links
Manually force a refresh and check if the file paths to the source workbooks on SharePoint or OneDrive are still correct.
Often, the connection to external cloud files goes dormant or the file path changes slightly when synced online. Forcing a refresh and checking the link status is the best first step.
Open your main workbook, navigate to the 'Data' tab on the ribbon, and click 'Refresh All'. Wait for the background query to complete.
Go to 'Data' > 'Workbook Links' (or 'Edit Links'). Review the status of each source file to ensure it says 'OK' and not 'Error' or 'Source not found'.
Open the specific source workbook directly from its SharePoint or OneDrive location to confirm that your latest changes were actually saved to the cloud.
Enable Blocked External Content
Ensure that Excel's security settings are not preventing the workbook from pulling in external data.
Use Power Query for a Stable Connection
Rebuild fragile cell-to-cell external links using Power Query for a more robust multi-file data architecture.
Try WPS Office for Seamless Spreadsheet Management
If you frequently encounter syncing issues, broken links, or sluggish performance in complex spreadsheets, consider switching to WPS Office. It provides a lightweight, highly compatible alternative for managing data with full support for popular spreadsheet formats.
- 1. Download and Install: Visit the official WPS website to download the free WPS Office suite and install it on your device.
- 2. Open Your Workbooks: Launch WPS Spreadsheet and open your existing .xlsx files. All your data, formatting, and formulas will be preserved.
- 3. Manage Data Smoothly: Enjoy a fast, lag-free experience while working with your consolidated reports and multiple worksheets.
Frequently Asked Questions
Why does Excel say my external link source is not found?
This usually happens if the source file was renamed, deleted, or moved to a different folder in SharePoint or OneDrive. You will need to open the 'Workbook Links' menu and update the file path to restore the connection.
Do source workbooks need to be open for links to update?
Standard cell references to external files often require the source workbook to be open to update reliably in real-time. However, if you use Power Query, the source files can remain closed while the data refreshes.
How do I stop Excel from asking to update links every time I open the file?
You can change this prompt by going to 'Data' > 'Workbook Links' (or Edit Links), clicking on 'Startup Prompt', and choosing to either automatically update links without asking or to not display the alert and not update automatically.
Does moving files to a local drive fix SharePoint sync issues?
Yes, moving the master and source files to a single local folder can rule out network or cloud-syncing delays. If the files update locally but not on SharePoint, the issue is likely related to OneDrive/SharePoint sync delays or network permissions.
