How to Link Cells Across Sheets and Workbooks in Excel
Linking cells across sheets keeps a workbook DRY: enter a number once, reference it everywhere. Linking across workbooks lets a summary file pull live data from source files. Both are simple to write and surprisingly easy to break. This guide covers the syntax, best practices, and what to do when links go wrong.
Linking within the same workbook
The basic syntax
References to cells on other sheets use the format SheetName!CellAddress. The exclamation mark separates the sheet from the cell reference.
Simple cross-sheet reference
=Sheet2!A1
The easy way — let Excel write it for you
You never need to type the sheet name manually:
- In the destination cell, type
= - Click the source sheet tab
- Click the source cell
- Press Enter
Excel writes the reference correctly, including quotes around sheet names with spaces.
Sheet names with spaces or special characters
If your sheet is named "Sales Data" (with a space), the reference needs single quotes:
Quoted sheet name
='Sales Data'!A1
Excel adds the quotes automatically when you click through. If you're typing the reference, remember the quotes for any sheet name with spaces, hyphens, or numbers at the start.
Referencing a range instead of a single cell
Standard range syntax works across sheets:
Sum a range from another sheet
=SUM(Sheet2!A1:A100)
VLOOKUP against another sheet
=VLOOKUP(A2, 'Reference Data'!$A:$C, 3, FALSE)
3-D references (multiple sheets at once)
Excel supports summing the same cell across multiple sheets — useful when each sheet has the same layout (monthly reports, regional sheets).
Sum A1 across Jan through Dec
=SUM(Jan:Dec!A1)
A 3-D reference like Jan:Dec includes every sheet between Jan and Dec in tab order. Rearranging tabs can silently change what your formula includes. Consider a hidden "Start" and "End" marker sheet at each boundary so reordering doesn't affect totals.
Absolute vs. relative references
Cross-sheet references follow the same $ sign rules as normal references.
| Reference | Behavior when copied |
|---|---|
Sheet2!A1 | Fully relative — both row and column shift |
Sheet2!$A1 | Column locked, row shifts |
Sheet2!A$1 | Row locked, column shifts |
Sheet2!$A$1 | Fully locked — always points to A1 |
Press F4 while editing a formula to cycle through the four variants.
Linking across workbooks
The full syntax
External references show the source file in brackets:
Reference to a cell in another workbook (both files open)
=[Budget.xlsx]Sheet1!$A$1
Same reference when the source workbook is closed
='C:\Users\zain\Documents\[Budget.xlsx]Sheet1'!$A$1
Excel automatically switches between the short form and the full path depending on whether the source is open.
Creating a cross-workbook link
- Open both workbooks in the same Excel instance
- In the destination workbook, type
= - Switch to the source workbook (Alt+Tab or the View → Switch Windows menu)
- Click the source cell
- Press Enter
Excel writes the reference with the source file name in brackets. When you close the source workbook, Excel adds the full file path automatically.
If you open one from Outlook and one from File Explorer, they may launch as separate Excel processes and can't link. Open the source workbook from within Excel (File → Open) to ensure the same instance.
Named ranges for readable links
Cross-sheet formulas get long fast. Named ranges make them readable and portable.
Creating a named range
- Select the source range (a cell or block on any sheet)
- Formulas → Define Name
- Name it (letters, numbers, no spaces):
MonthlyBudget - Verify the "Refers to" reference — it should include the sheet name
- OK
Using the named range in formulas
Instead of a raw cross-sheet reference
=SUM('Budget Data'!$B$2:$B$50)
Use the named range
=SUM(MonthlyBudget)
Named ranges also survive being copied to a new workbook — you can move formulas and the names travel with them.
In Define Name, the Scope dropdown lets you make a name available workbook-wide or only on one sheet. Workbook-wide is usually what you want. Sheet-scope is for names that mean different things on different sheets (rare).
INDIRECT for dynamic references
INDIRECT lets you build cell references from text. Useful when the sheet name or cell address depends on other cells.
Reference a cell whose sheet name lives in A1
=INDIRECT(A1 & "!B2")
Sum a dynamic range
=SUM(INDIRECT("'" & A1 & "'!B2:B100"))
INDIRECT can reference other sheets but not other workbooks unless both are open. Formulas like =INDIRECT("[Budget.xlsx]Sheet1!A1") break when the source workbook is closed. For cross-workbook dynamic links, consider Power Query instead.
Managing external links
The Edit Links dialog
Shows every workbook your file references externally. For each:
- Update Values — pull the latest from the source (if the source is available)
- Change Source — point the reference at a different file (useful when the source moved)
- Open Source — open the source workbook
- Break Link — replace formulas with their current values, severing the link permanently
Updating vs. not updating on file open
When you open a file with external references, Excel asks whether to update. Options:
- Update — pull the latest values from source files
- Don't Update — keep the last-saved values displayed
To control this default: File → Options → Advanced → General → uncheck "Ask to update automatic links". Or in Edit Links → Startup Prompt.
Finding hidden external references
Sometimes you know a link exists but can't find it. Try:
- Formulas: Find & Replace (Ctrl+F) → search for
.xlsor[→ search in Formulas - Named ranges: Formulas → Name Manager → look for names with external paths
- Data validation rules: harder to search, but Data → Data Validation → look for source ranges pointing outside the workbook
- Conditional formatting: Home → Conditional Formatting → Manage Rules → check formulas
- Charts: click each chart's data range, check source
Breaking external links
To convert live external references into static values:
- Data → Edit Links → select the source
- Click Break Link
- Confirm
Every formula referencing that source is replaced with its current value. This is permanent — save a backup first.
If you defined a name pointing to another workbook, Break Link replaces the formula results but leaves the name referring to the external workbook. Delete such names manually via Name Manager after breaking links.
Best practices
Keep source and destination in the same folder
Relative paths within a folder survive being moved together. Absolute paths (C:\Users\...) break when files move. Store linked workbooks in the same directory and reference them by file name only when possible.
Use OneDrive/SharePoint for shared linking
Files with cloud paths (OneDrive URLs) stay linked even when opened on different computers, as long as everyone has access. Local file paths (C:\Users\yourname\...) only work on your machine.
Use one summary workbook, not a chain
Workbook A links to B, which links to C, which links to D. Every time A opens, Excel walks the chain. Slow, fragile, and hard to debug. Better: one summary workbook links directly to all source files. If chains are unavoidable, document them.
Prefer Power Query for many-workbook combining
For 10+ source workbooks (monthly extracts, regional reports), external references become fragile. Power Query's "From Folder" connector reads any number of files, transforms consistently, and refreshes on demand. See the Power Query guide.
Google Sheets equivalent — IMPORTRANGE
Google Sheets doesn't use file paths for cross-file links; it uses IMPORTRANGE with the source's URL:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/ABC.../edit", "Sheet1!A1:C100")
First use requires granting access via a "Allow access" prompt. After that, IMPORTRANGE pulls live data. Same-sheet references in Sheets work identically to Excel — =Sheet2!A1.
Common pitfalls
If a formula references Sheet2!A1 and you rename Sheet2, Excel updates the formulas automatically. But if you delete Sheet2, formulas turn into #REF!. There's no undo. Test critical formulas after any sheet rename or deletion.
External references include the file path. Moving the source file to a new folder breaks every formula referencing it. Fix via Edit Links → Change Source, or keep files in stable locations (OneDrive, network shares).
A cell that references itself (directly or through a chain of cells) creates a circular reference. Excel warns you on entry but the warning is easy to dismiss. Formulas → Error Checking → Circular References lists any lurking ones.
Copy a linked cell → Paste Special → Values. The link is severed; you now have a static number. If you meant to keep the link, undo and paste normally.
Referencing Table1[Amount] is more stable than Sheet1!$B$2:$B$1000. Tables auto-extend as rows are added, and the reference is self-documenting.
Cross-sheet links that survive edits
Absolute references, named ranges, INDIRECT dynamics — cross-sheet linking has many patterns and easy ways to break. Excel Wizard picks the right pattern for your intent ("total this cell across every monthly sheet") and writes it with correct $ signs and quoting.
Install Excel Wizard →Frequently asked questions
How do I link a cell from another sheet in Excel?
Type =, click the source sheet tab, click the source cell, press Enter. Excel writes the reference like =Sheet2!A1. Changes to the source update the destination automatically.
How do I link cells from another workbook?
Open both workbooks. In the destination, type = and switch to the source workbook, click the cell, press Enter. Excel writes ='[Source.xlsx]Sheet1'!$A$1. When the source closes, the full file path is added.
Why does my Excel link show #REF! or a broken reference?
Usually: the source workbook moved or was renamed, the sheet was deleted, or the referenced cell was deleted. Fix via Data → Edit Links → Change Source, or rewrite the formula.
How do I break links to other workbooks?
Data → Edit Links → select the source → Break Link. Excel replaces formulas with their current values. Permanent — save a backup first.