How to Fix #REF! Errors When Linking Excel Workbooks in Teams
Question details
Users encounter #REF! errors when trying to retrieve updated values from linked Excel workbooks stored in Microsoft Teams or SharePoint.
- Product
- Microsoft Excel / Teams
- Device & OS
- not provided
- Scenario
- Collaborating on or accessing linked Excel workbooks hosted on Microsoft Teams or SharePoint cloud environments.
- Observed behavior
- Excel displays a #REF! error or an alert stating it cannot retrieve updated values from the linked external workbook.
Ensure that you are signed into your Office application with the correct organizational account that has explicit access to the Microsoft Teams or SharePoint site hosting the files.
Verify Permissions for Both Source and Linked Workbooks
Ensure that all users accessing the destination file have the appropriate permissions to read the source workbook in Teams or SharePoint.
External links in Excel require users to have direct access to the source file. If a user only has permission to view the destination file, Excel cannot fetch the data from the source file, resulting in a #REF! error.
Navigate to the Microsoft Teams channel or SharePoint folder where the source workbook is currently stored.
Click the three dots (More options) next to the source file, select 'Manage access', and confirm that the affected users have 'Read' or 'Edit' access.
Ask the affected users to open both the source and linked workbooks directly in their Excel desktop application to verify they can access the files without credential prompts.
Refresh Links and Verify Account Sign-In
Manually force Excel to update external links while ensuring your Office application is authenticated properly.
Replace Fragile External Links with Power Query
Standard cell-to-cell external links can break easily in cloud environments. Power Query provides a much more robust data connection.
Seamlessly Manage Spreadsheets and Data with WPS Office
If external linking in Microsoft Excel causes recurring #REF! errors or persistent cloud permission conflicts, consider switching to WPS Office. As a free, lightweight, and powerful alternative, WPS Spreadsheet offers high compatibility with Microsoft Excel formats and provides a familiar user interface for smooth offline and cloud-based data management.
- 1. Download and Install: Visit the official WPS website to download and install WPS Office for your device.
- 2. Open your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files directly without any format conversion.
- 3. Manage Data Easily: Use the familiar interface to link data, build formulas, and collaborate with your team effortlessly.
Frequently Asked Questions
Why do my Excel links work locally but show a #REF! error in Microsoft Teams?
When moved to Teams or SharePoint, local file paths (like C:\Users\...) break. The links must be updated to use the web URL of the SharePoint library, and every user accessing the file must have explicit permissions to read the source file in the cloud.
Does co-authoring affect external workbook links in Excel?
Yes, when multiple users are editing a linked workbook simultaneously (co-authoring), Excel may fail to retrieve updated values from a locked source file, resulting in temporary #REF! errors. Switching to single-user mode can often resolve this.
How do I fix the 'Excel cannot retrieve updated values' message?
You can fix this by navigating to the Data tab, clicking on Edit Links, selecting the linked file, and clicking 'Update Values'. Make sure you are signed in with the correct Microsoft 365 account that has access to the linked files.
What is the best way to link data between Excel files in Teams without getting errors?
Instead of using traditional cell references (like ='[Workbook.xlsx]Sheet1'!A1), it is highly recommended to use Power Query ('Get Data') to import data from another workbook stored in SharePoint or Teams, as it handles cloud paths and user authentication much more reliably.
