Prevent #REF! Errors in Excel External Workbook References
Question details
The user needs to prevent #REF! errors in a summary workbook that retrieves data from other Excel workbooks.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A summary workbook depends on data stored in source workbooks B and C.
- Observed behavior
- Formulas can return #REF! when source workbooks are moved, renamed, unavailable, disconnected, or contain broken references.
Locate the current copies of every source workbook and confirm that you have permission to open them. Check whether the source files or their folders were renamed, moved, deleted, or disconnected from a network or cloud location.
Restore the source workbook path used by the Excel formulas
Use this when #REF! appears after a linked workbook was moved, renamed, deleted, or replaced.
An external reference must point to an existing workbook, worksheet, and cell or range. Restoring the original file location or updating the workbook link prevents formulas from referring to invalid locations.
In Microsoft Excel, open the summary workbook that displays the #REF! error.
Select Data > Workbook Links. In some Excel versions, select Data > Edit Links.
Select the unavailable source workbook, choose Change Source, and browse to the current file location. Select the correct workbook and confirm the update.
Open the source workbook and confirm that the worksheet names, columns, and referenced cells still exist. Restore renamed sheets or update formulas that refer to deleted ranges.
Select Formulas > Calculate Now, confirm that the formulas return values instead of #REF!, and save the summary workbook.
Refresh external links after opening accessible source workbooks
Use this when the source files exist but the summary workbook displays outdated values or cannot update linked data.
Replace live workbook links with a managed shared data source
Use this when multiple users need to edit the summary independently or source files are frequently unavailable.
Manage Linked Workbook Data with WPS Spreadsheets
If Microsoft Excel external links are disrupted by unavailable or moved source files, WPS Office provides a free, lightweight spreadsheet alternative with a familiar interface. You can open, review, edit, and migrate Excel workbooks while keeping common formulas and workbook formats compatible.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the app launcher.
- 2. Open the Excel summary workbook: Select Open and choose the existing .xlsx or .xls summary workbook.
- 3. Review formulas and source data: Select a formula cell to inspect its references, then open the required source workbook or import its data into a local sheet.
Frequently Asked Questions
Why does an Excel formula show #REF! when it links to another workbook?
#REF! means part of the formula reference is invalid. The source workbook may have been moved, renamed, deleted, unavailable, or changed so that the referenced worksheet, row, column, or cell no longer exists.
Can Excel update links when the source workbook is closed?
Excel can update many standard external cell links from a closed workbook if the file path is available. However, some functions, queries, connections, and protected or inaccessible files may require the source workbook to be open.
Where can I change a broken external workbook link in Excel?
Open the summary workbook and select Data > Workbook Links. In older Excel versions, use Data > Edit Links. Select the source workbook, choose Change Source, and select the correct file.
How do I find formulas that contain external workbook references?
Select Home > Find & Select > Find, enter an opening bracket character ([), then select Find All. External workbook formulas commonly include the source workbook name inside brackets.
Is Power Query better than direct workbook references?
Power Query is often more reliable for recurring imports because it loads source data into the destination workbook and can be refreshed through Data > Refresh All. It still requires an accessible source location, but it reduces dependence on many live cell-by-cell links.
