How to Link Cells Across Sheets and Workbooks in Excel (2026)
Home›Excel›Link Cells Across Sheets
ExcelHow-To Post⏱ 9 min read

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
Pulls the value of A1 from Sheet2

The easy way — let Excel write it for you

You never need to type the sheet name manually:

  1. In the destination cell, type =
  2. Click the source sheet tab
  3. Click the source cell
  4. 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)
Sums cell A1 on every sheet from Jan through Dec inclusive.
Order of sheet tabs matters

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.

ReferenceBehavior when copied
Sheet2!A1Fully relative — both row and column shift
Sheet2!$A1Column locked, row shifts
Sheet2!A$1Row locked, column shifts
Sheet2!$A$1Fully 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

  1. Open both workbooks in the same Excel instance
  2. In the destination workbook, type =
  3. Switch to the source workbook (Alt+Tab or the View → Switch Windows menu)
  4. Click the source cell
  5. 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.

Both files must open in the same Excel instance

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

  1. Select the source range (a cell or block on any sheet)
  2. Formulas → Define Name
  3. Name it (letters, numbers, no spaces): MonthlyBudget
  4. Verify the "Refers to" reference — it should include the sheet name
  5. 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.

Names are scoped by workbook or by sheet

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")
If A1 = "Jan", pulls B2 from the Jan sheet. Change A1 to "Feb" and it pulls from Feb.

Sum a dynamic range

=SUM(INDIRECT("'" & A1 & "'!B2:B100"))
Sums B2:B100 on whichever sheet A1 names. The single quotes handle sheet names with spaces.
INDIRECT doesn't work with closed external workbooks

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

Ribbon: Data → Edit Links (in the Queries & Connections group)

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 .xls or [ → 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:

  1. Data → Edit Links → select the source
  2. Click Break Link
  3. Confirm

Every formula referencing that source is replaced with its current value. This is permanent — save a backup first.

Break Link doesn't touch named ranges

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

#REF! after renaming a sheet

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.

Moving files breaks cross-workbook links

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).

Circular references

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-paste-values loses the link

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.

Use Excel Tables for stability

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.

Excel Wizard

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.