Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.


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?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!