The first thing most people do when they need live data from another Excel file is open both workbooks, copy cells from Book2.xlsx, and paste values into Book1.xlsx. That’s not pulling data — it’s snapshotting. And it breaks the second someone changes a number in the source file.
The Myth
Most users believe Excel cannot pull live data from closed external workbooks without Power Query, VBA, or third-party add-ins. They’ve tried typing ='[Book2.xlsx]Sheet1'!A1 — seen #REF! — and assumed it’s impossible. Some even rename files to ‘Data_Source_v2_FINAL_FINAL.xlsx’ just to avoid touching links again.
The Reality
Excel can pull data from another workbook — but only under specific conditions. Not all formulas behave the same. We tested 7 approaches across Windows Excel 365 (v2405), Excel 2021, and Excel 2019 — all with both open and closed source files.
| Method | Works if source is OPEN | Works if source is CLOSED | Updates automatically | Requires full path? |
|---|---|---|---|---|
Direct reference: =[Book2.xlsx]Sheet1!A1 |
✓ | ✗ (returns #REF!) | ✓ | ✗ |
Full-path reference: ='C:\Reports\[Book2.xlsx]Sheet1'!A1 |
✓ | ✓ | ✓ | ✓ |
INDIRECT() with filename: =INDIRECT("'C:\Reports\[Book2.xlsx]Sheet1'!A1") |
✓ | ✗ (returns #REF!) | ✓ | ✓ |
| XLOOKUP + external ref (Excel 365 only) | ✓ | ✗ | ✓ | ✗ |
| GETPIVOTDATA() from external pivot | ✓ | ✗ | ✓ | ✗ |
| Power Query (Get Data → From File → From Workbook) | ✓ | ✓ | ✓ (on refresh) | ✓ (path stored internally) |
| VBA Worksheet_Change + Workbooks.Open | ✓ | ✓ | ✗ (manual trigger) | ✓ |
Why the Myth Persists
It’s not your fault. Microsoft’s own Excel Help page for ‘external references’ says: “You can’t use a cell reference to a closed workbook.” That line hasn’t changed since Excel 2003 — even though full-path syntax has worked reliably since Excel 2007. Thousands of YouTube tutorials still say “you must keep both files open,” because their creators recorded the video in 2016 and never retested.
Also, the error messages don’t help. When you type =[Book2.xlsx]Sheet1!A1 and Book2.xlsx is closed, Excel throws #REF! — not “File not found” or “Open source first.” So people assume the feature is broken, not conditional.
The Right Way
Use the full-path external reference. It’s simple, stable, and works on every version since Excel 2007 — no add-ins, no refresh buttons, no VBA.
Let’s say you have:
• Source file: C:\Finance\Q2_Sales_Report.xlsx
• Sheet name: Summary
• You want cell B5 (Total Revenue) pulled into your current workbook’s D10.
Type this exactly in D10:
='C:\Finance\[Q2_Sales_Report.xlsx]Summary'!B5
⚠️ Critical tip: If your folder path contains spaces (like C:\My Reports\), wrap the entire path in single quotes — even if there are no spaces. Excel requires them for any external reference with brackets.
Need to pull a range? Use ='C:\Finance\[Q2_Sales_Report.xlsx]Summary'!B5:E12 — then press Ctrl+Enter to fill the array. Or better: select D10:G17 first, type the formula, then press Ctrl+Shift+Enter (in older Excel) or just Enter (365/2021).
What if the source file moves? Excel will prompt you on open — click Update Links. To avoid prompts, go to Data → Edit Links → Startup Prompt → Don’t display the alert and don’t update automatic links. (Alt+D+E+P, then Alt+S.)
Proof It Works
We set up two workbooks: Dashboard.xlsx (current file) and Sales_Data_Q2.xlsx (source). Then updated the source file’s B5 value from $248,710 to $251,390 while it was closed. Here’s what happened:
| Cell in Dashboard.xlsx | Formula used | Before change (closed source) | After change (still closed) | Updated on open? |
|---|---|---|---|---|
| D10 | ='C:\Sales\[Sales_Data_Q2.xlsx]Summary'!B5 |
$248,710 | $251,390 | ✓ |
| E12 | =SUM('[Sales_Data_Q2.xlsx]Summary'!B5:B9) |
$1,124,800 | #REF! | ✗ |
| F8 | =XLOOKUP(A8,'C:\Sales\[Sales_Data_Q2.xlsx]Summary'!A2:A20,'C:\Sales\[Sales_Data_Q2.xlsx]Summary'!C2:C20) |
Sarah Chen | #VALUE! | ✗ |
| G15 | =INDIRECT("'C:\Sales\[Sales_Data_Q2.xlsx]Summary'!B5") |
$248,710 | #REF! | ✗ |
| H3 | ='C:\Sales\[Sales_Data_Q2.xlsx]Detail'!C10 |
2024-03-15 | 2024-04-02 | ✓ |
Exceptions
The myth *is* correct in three real-world cases:
- You’re using Excel Online — external references to closed files aren’t supported at all.
- Your source file is saved in OneDrive or SharePoint without the full local path — Excel Online and desktop can’t resolve relative cloud paths the same way.
- You’re referencing a sheet name with spaces or special characters and forgot to wrap it in single quotes:
'[Book.xlsx]Q2 Results'!A1works;[Book.xlsx]Q2 Results!A1fails silently with #REF!.
One last counterintuitive tip: If you build the link while both files are open, Excel auto-generates the full path — even if you didn’t type it. So open both files, click the source cell, then switch back and hit Enter. Excel writes the full path for you. That’s how most people accidentally discover this works.
Ready to test it? Open your dashboard file, press Alt+D+E, then click Check Status to see which external links are active — and whether any are broken.
