Yes, you can reference another workbook in Excel. But if you open the source file after the destination file — or save either as .xlsb — Excel quietly drops the full path and replaces it with just the filename, breaking the link the next time you restart.
Quick Answer
To reference another workbook, type =[Sales_Q3_2024.xlsx]Sheet1!A1 in a cell — but only if both workbooks are open. If the source is closed, Excel inserts the full file path automatically (e.g., ='C:\Reports\[Sales_Q3_2024.xlsx]Sheet1'!A1), and that path must stay valid or the formula returns #REF!.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Direct typing (both files open) | Type =[, switch to source workbook, click cell, press Enter | One-off references; quick cross-checks | Fails silently if source closes before saving |
| Paste Link (Paste Special) | Copy cell → Alt+E+S+L in destination → Enter | Preserving formatting + values while linking | Only works when source is open; no relative addressing |
| INDIRECT with CONCATENATE | Build dynamic path string → wrap in INDIRECT | Switching between monthly reports without editing formulas | Breaks if source is closed; volatile — recalculates every change |
| Power Query (Get Data) | Data > Get Data > From File > From Workbook → select sheet → Load | Large datasets, scheduled refreshes, audit trails | No live cell-to-cell links; requires Power Query Editor familiarity |
| Excel Tables + Structured References | Convert source range to table → use [Workbook.xlsx]Table1[Column1] | Maintaining clarity across shared reporting templates | Only works when source is open; doesn’t auto-update path on rename |
| Defined Names with external references | Formulas > Define Name → Refers to: ='C:\[Budget_2024.xlsx]Q1'!$B$5 | Centralizing key assumptions (e.g., tax rate, FX rate) | Names don’t update if source moves; hard to debug for new users |
Method 1 Deep Dive
Let’s say you’re building a dashboard in Dash_Master.xlsx and need live sales figures from Sales_Q3_2024.xlsx, currently open. You want cell B2 in Dash_Master to show Sarah Chen’s Q3 revenue from row 7 of Sheet1 in the other file.
Click B2 in Dash_Master.xlsx. Type =. Click over to Sales_Q3_2024.xlsx → click Sheet1 → click cell D7 (her revenue). Press Enter. Excel writes: =[Sales_Q3_2024.xlsx]Sheet1!D7.
Here’s the catch: if you now close Sales_Q3_2024.xlsx and save Dash_Master.xlsx, Excel keeps the simple name — but the next time you open Dash_Master.xlsx alone, it shows #REF!. Why? Because Excel didn’t embed the full path. It assumed the file would always be open alongside.
So — open both files. Go to Formulas > Edit Links. You’ll see Sales_Q3_2024.xlsx listed. Click Change Source… and re-point to the exact file location — even if it’s the same one. This forces Excel to store the full path. (Trust me, I learned this the hard way during a client demo.)
| Before (both files open) | After (full path embedded) |
|---|---|
=[Sales_Q3_2024.xlsx]Sheet1!D7 | ='C:\Finance\Q3 Reports\[Sales_Q3_2024.xlsx]Sheet1'!D7 |
| Breaks if source closes | Stays functional even if source is closed |
Method 2 Deep Dive
Paste Link is underrated — especially when you need to pull multiple cells at once and keep them aligned. Say you’re pulling quarterly headcount data from HR_FTE_Tracker.xlsx into your budget model.
In HR_FTE_Tracker.xlsx, select B2:D6 — that’s Q1–Q3 headcount for Acme Corp, BetaSoft, and NexGen Labs. Copy it (Ctrl+C). Switch to your budget workbook. Right-click cell F10 → press Alt+E+S+L (that’s Paste Special → Paste Link). Done.
You’ll get three linked columns starting at F10. Each cell contains something like ='C:\HR\[HR_FTE_Tracker.xlsx]Q3'!B2. Notice Excel auto-included the full path — because Paste Link *only works when the source is open*, and Excel knows it needs durability.
Here’s the surprising part: if you later insert a row above F10, the links *won’t shift down*. They’ll stay anchored to F10:F14 — but their references won’t update to point to B3:D7 in the source. So avoid inserting rows/columns near Paste Link ranges unless you manually adjust them. (I’ve seen teams waste half a day debugging this.)
Sample data pulled via Paste Link:
| Company | Q1 2024 | Q2 2024 | Q3 2024 |
|---|---|---|---|
| Acme Corp | 84 | 87 | 92 |
| BetaSoft | 112 | 115 | 120 |
| NexGen Labs | 63 | 65 | 68 |
| Veridian Dynamics | 201 | 205 | 213 |
| Skyline Systems | 47 | 49 | 52 |
Cheat Sheet
| Action | Keyboard Shortcut | Notes |
|---|---|---|
| Paste Link | Alt+E+S+L | Works only when source workbook is open |
| Edit external links | Alt+A+L | Opens ‘Edit Links’ dialog — check paths here |
| Break all links | Alt+A+U+B | Converts links to static values — irreversible |
| Force full path in formula | Manual edit | Add apostrophes around path + brackets: ='C:\[file.xlsx]Sheet'!A1 |
| Test link integrity | None | Open both files → Formulas > Edit Links → Click ‘Check Status’ |
| Update links on open | File > Options > Advanced > “Ask to update automatic links” | Uncheck to suppress prompts — but verify paths first |
| Find all external references | Ctrl+G → Special → Formulas → Check ‘External’ | Highlights every cell referencing another workbook |
