Consider the following scenario:

1. You insert a table in an Excel file, named for example source.xlsx.

2. In another file, named for example destination.xlsx, you insert a link to the data table which resides in source.xlsx.

3. You close both source.xlsx and destination.xlsx.

4. You open destination.xlsx and you choose to update links when prompted.

Result: the cells that are linked to the external data table display #REF!.


This is expected behavior for Excel.

New references to external workbooks that aren’t open will successfully parse without verifying the reference, but will return #REF. 

When loading an external structure reference to a closed workbook, the reference is not updated by default.  If you choose to calculate the formula the result is #REF! instead of the corresponding value.


Structured references require the external workbook to remain open for the formulas to work properly. 

More Information

Using structured references with Excel tables

Working with external links

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!