Formula Errors

How to Fix Excel #REF! Error with External Link Formulas

Maira Mehtab Sep 21, 2026 869 views

Question details

Excel formulas referencing an external structured table break and return a #REF! error when opened on a colleague's computer, with the formula expanding to include the complete source file path.

Product: Excel
Device & OS: not provided
Scenario: Sharing Excel workbooks containing formulas that use structured references to pull data from external data tables.
Observed behavior: The formulas calculate correctly on the original computer but display the full file path and #REF! errors when accessed on a different computer.

Before you start

Ensure you have access to both the target workbook and the source workbook (e.g., DATABASE.xlsx) containing the referenced tables before troubleshooting the external links.

Solution 1Recommended

Open Source and Target Workbooks Simultaneously

Structured references to external workbooks often require the source workbook to be open in the same application instance to evaluate correctly and avoid #REF! errors.

When referencing tables (structured references) like Table5[COMMENTS] across different workbooks, Excel requires the source file to be open in the background. If the source file is closed or missing on the new computer, the link breaks and triggers a #REF! error.

Transfer all files

Ensure that both the source file (DATABASE.xlsx) and the target file containing the formulas are saved on the colleague's computer or a shared accessible network drive.

Open the source workbook

Launch Excel and open the source workbook (DATABASE.xlsx) first.

Open the target workbook

With the source file still open, open the target workbook containing the formulas.

Verify formula evaluation

Check the cells to confirm the formula correctly evaluates the Table5[COMMENTS] reference instead of returning #REF!.

Hyperlinks Do Not Fix Structured References: Creating a hyperlink between the two workbooks for easier navigation will not repair a broken external structured reference or automatically update the #REF! error.

Solution 2

Verify Source Table and Column Names

Check that the referenced table and column still exist in the source file and have not been renamed or deleted.

Seamlessly Manage External Links with WPS Spreadsheet

WPS Spreadsheet provides robust support for structured references and external workbook links, ensuring your formulas calculate accurately even when files are shared across different devices.

1. Open Both Files: Launch WPS Spreadsheet and open both the source workbook and the destination workbook to maintain active reference links.
2. Access Link Management: Go to the 'Data' tab and click on 'Edit Links' to review the status of connected external workbooks.
3. Refresh Source Data: Select the linked source file and click 'Update Values' to instantly resolve broken reference errors and synchronize your data.

Fully compatible with Microsoft Excel file formats (.xlsx)Efficiently manages external links and complex table referencesFree and lightweight alternative for spreadsheet data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Excel formulas show the full file path instead of the result?

When a formula references an external workbook, Excel displays the full file path if the source workbook is currently closed. If the path is broken or inaccessible on a new device, the formula fails to evaluate and results in a #REF! error.

Can I use structured table references for closed external workbooks?

No, structured references (such as Table5[COMMENTS]) generally require the source workbook to be open to evaluate correctly. If the source file is closed, the formula cannot pull the data and will return a #REF! error.

How do I fix broken links in my spreadsheet?

Navigate to the Data tab, select 'Edit Links' in the Queries & Connections group, and use the 'Change Source' button to re-link the formula to the correct file location on your current computer or network drive.

Excel & Data Problems