Fix Excel #REF! Errors in OneDrive External Workbook Links
Question details
The user needs to resolve a #REF! error in Excel desktop formulas that link to a source workbook stored on OneDrive.
- Product
- Microsoft Excel desktop with OneDrive-hosted workbooks
- Device & OS
- not provided
- Scenario
- An external formula works after links are updated or while the source workbook is open, but needs to remain valid when the source workbook is closed.
- Observed behavior
- Excel for the web may calculate the external link, while Excel desktop returns #REF! after the linked OneDrive source workbook is closed.
Make sure the OneDrive desktop sync client is running and that both the destination workbook and source workbook are available locally. Confirm that you can open the source workbook from its OneDrive-synced folder before changing the link.
Open the OneDrive source workbook and update the Excel workbook link
Use this method when the link works only while the source workbook is open or when Excel reports that values from other workbooks were not updated.
Excel desktop may be unable to refresh a cloud-based external reference when the OneDrive source workbook is closed. Opening the source file from its synced location lets Excel resolve the workbook and update the stored link path.
Open File Explorer, go to your OneDrive-synced folder, and open the source workbook directly in Excel. Do not open only a browser link to the file.
Select the OneDrive cloud icon in the Windows notification area and confirm that the source workbook has finished syncing and does not show a sync error.
Open the workbook containing the #REF! formula. On the Data tab, select Workbook Links or Edit Links, select the affected source workbook, and choose Update Values.
In Data > Workbook Links or Edit Links, select the broken link and choose Change Source. Browse to the source workbook inside the local OneDrive folder, then save the destination workbook.
Close the source workbook, reopen the destination workbook, and select Enable Content or Update when Excel asks to update links. Check whether the formula still returns a value instead of #REF!.
Replace a fragile external formula link with Power Query
Use Power Query when the external formula repeatedly fails after the OneDrive source workbook is closed or when you need a more reliable refresh process.
Work with linked Excel workbooks in WPS Spreadsheets
If Excel desktop continues to mishandle a OneDrive external workbook link, try WPS Office as a free, lightweight alternative with a familiar spreadsheet interface. You can open and migrate existing workbooks easily, then review formulas and linked data from locally synchronized files.
Frequently Asked Questions
Why does an external OneDrive formula work in Excel for the web but show #REF! in Excel desktop?
Excel for the web and Excel desktop can resolve cloud workbook links differently. In desktop Excel, the source workbook may need to be accessible through a valid locally synchronized OneDrive path, especially when the source file is closed.
Where can I update a broken workbook link in Excel?
Open the destination workbook and go to the Data tab. Select Workbook Links in newer Excel versions or Edit Links in older versions, choose the affected source, and use Update Values or Change Source.
Why does Open Source make the #REF! formula work temporarily?
Opening the source workbook gives Excel direct access to the referenced cells, allowing it to calculate the formula. If the result fails after closing the source, the saved external link path or closed-workbook access is likely unreliable.
Can I use a OneDrive web URL as the source for an Excel external formula?
A OneDrive URL may not behave consistently for desktop external references. For better reliability, use the source workbook from the local folder created by the OneDrive sync client and update the link to that location.
When should I use Power Query instead of an external workbook formula?
Use Power Query when you need to import and refresh data from another workbook reliably, particularly when the source file is normally closed. Use direct external formulas only when you need cell-by-cell formula relationships that update as linked values.
