Formula Errors

How to Fix Excel External Links Returning #REF! Error

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to resolve #REF! errors that appear in cells containing external links after the source workbooks have been moved to a different folder or renamed.

Product: Excel
Device & OS: not provided
Scenario: Moving, copying, or renaming linked workbooks, such as shifting files into new quarterly folders.
Observed behavior: Formulas linked to external workbooks fail to retrieve data and return a #REF! error because the original file path is no longer valid.

Before you start

Ensure you know the exact new file path and name of the source workbook before attempting to update the external links.

Solution 1Recommended

Update the External Link Source

Reconnect your current workbook to the moved or renamed source file using the Edit Links feature to instantly resolve the #REF! errors.

When you move a referenced workbook to a new location while the destination file is closed, the connection breaks. Updating the source path directs the formulas to the new location.

Open Edit Links

Go to the 'Data' tab on the Excel ribbon and click on 'Edit Links' located in the Queries & Connections group.

Change Source

In the Edit Links dialog box, select the broken link from the list, click the 'Change Source' button, and navigate to the new folder where the source workbook is currently saved.

Select File and Refresh

Select the correct file and click 'OK' to update the values. Finally, save your workbook to preserve the newly established link paths.

Prevent Future Breakages: Keep related workbooks in a consistent folder structure. If you must move them, try to move both the source and destination files together, or keep both files open in Excel while renaming or moving them.

Seamless Data Management

Manage External Links Easily with WPS Spreadsheet

WPS Spreadsheet handles external links and complex formulas with ease, offering a highly intuitive interface to update sources and prevent #REF! errors when organizing your files.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the broken external links.
2. Access the Data Tab: Navigate to the 'Data' tab on the top ribbon.
3. Edit Links: Click on 'Edit Links' to view all connected external workbooks in your current file.
4. Update Source File: Click 'Change Source' to locate the newly moved or renamed file and restore the connection seamlessly.

Fully compatible with Microsoft Excel formats (.xlsx, .xls)Built-in 'Edit Links' manager for easy source updatingLightweight application that opens large linked files quicklyFree to use for daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my spreadsheet show a #REF! error when I rename a linked sheet?

The #REF! error occurs because the formula is explicitly looking for the old sheet name. When the sheet is renamed while the destination workbook is closed, the application loses the reference. You need to update the formula to match the new sheet name.

Can I find all broken external links at once in my workbook?

Yes, you can go to Data > Edit Links to see a list of all external sources. If a link is broken, its status will typically show as 'Error: Source not found' when you click the 'Check Status' button.

How do I remove external links completely without losing the current data?

Open the Data tab, click Edit Links, select the linked source you want to remove, and choose 'Break Link'. This action will permanently convert all formulas referencing that external source into static values.

Excel & Data Problems