You've got a workbook with twelve monthly tabs and one summary sheet that's supposed to total them all. You can rebuild that summary by hand every month, or you can link the cells once and let Excel do the math forever. That's what this guide is about — pulling values from another tab in the same file, not from a separate workbook.
Quick distinction up front. Cross-workbook linking (file A pulling data from file B) is a different beast — it breaks when files move or get renamed. If that's what you need, read the linking excel workbooks walkthrough instead. This article stays inside one workbook, where links never break from a file rename and the syntax is simpler.
Short answer first. The basic pattern is =SheetName!CellAddress. Type an equals sign in your destination cell, click over to the source tab, click the cell you want, hit Enter. Done. Excel writes the reference for you. That's the foundation everything else builds on — Paste Link, INDIRECT, 3D references, named ranges, hyperlinks, VBA. Seven methods, one workbook, and almost no chance of a #REF! disaster.
Worth knowing: sheet names with spaces need single quotes around them. So Sheet2 stays bare, but 'Q1 Summary' wears quotes. Get that wrong and Excel throws #NAME? at you. That's the single most common gotcha you'll hit in the first ten minutes.
This guide walks each method with the exact keystrokes, common errors, and when each one earns its keep. By the end you'll have a working excel cell reference pattern memorized, plus three or four advanced techniques most people never bother learning.
In any cell, type =Sheet2!A1 to pull the value from cell A1 on Sheet2. Sheet names with spaces need single quotes: ='My Sheet'!A1. Same workbook only — for separate files, use the cross-workbook linking guide.
Manual =Sheet2!A1 entry — fastest for one cell.
Type =, click destination tab, click cell, Enter. Excel writes syntax for you.
Copy range from source tab, Paste Link on destination — auto-builds references.
=INDIRECT(B1&"!A1") — sheet name comes from a cell, fully dynamic.
=SUM(Jan:Dec!B5) — adds the same cell across many sheets at once.
Insert → Link → Place in This Document. Navigates, doesn't pull data.
Worksheets("Sheet2").Range("A1").Value — for automated workflows.
The fastest way, once you've done it twice. Click your destination cell. Type the equals sign. Type the sheet name, a bang (!), and the cell address. Press Enter.
So if you want cell B5 from a tab called Details to appear on your Summary tab, you'd type =Details!B5. That's it. Excel grabs the value and recalculates whenever Details!B5 changes.
Need math? Combine references the same way you'd combine any cells: =Summary!B5+Details!C2 adds two values from two tabs. =Jan!D10*Rates!B2 multiplies them. Excel doesn't care which tab a reference lives on — it treats them all the same.
Tab names with spaces. If your sheet is called Q1 Summary, you can't write =Q1 Summary!A1 — Excel reads that as the cell A1 on Q1 and gets confused by Summary. Wrap the name in single quotes: ='Q1 Summary'!A1. Same rule for names with apostrophes, parentheses, or anything beyond letters and numbers.
You're editing an existing formula. You know the exact tab and cell. You don't want to click around. Three keystrokes faster than the click-to-build method, every time.
Better for new users and for tab names you can't quite remember. Click the destination cell. Type only the equals sign. Then click the tab you want at the bottom of the screen. The formula bar now shows ='SheetName'! waiting for you. Click the source cell. Press Enter. Excel jumps you back to the destination cell with the formula filled in correctly — quotes and all.
This method handles the space-in-tab-name problem automatically. Excel knows when to add quotes. You don't have to remember.
The click-build method extends to ranges. Type =SUM( in your destination cell, click the source tab, drag across the cells you want summed, type the closing parenthesis, and press Enter. Excel writes the entire SUM(Sheet2!A1:A10) formula for you with proper quoting. Faster than typing the syntax by hand, and it never throws a #NAME? error from a mistyped tab name. Works for any function that takes a range: AVERAGE, COUNT, COUNTIF, MAX, MIN, all of them.
One more wrinkle. You can build cross-sheet formulas in one go that pull from three or four different tabs. Type =, click Sheet1 and a cell, type +, click Sheet2 and a cell, type +, click Sheet3 and a cell, hit Enter. Excel handles the navigation and the syntax. You just point and click. Some users find this faster than typing for any formula with more than two cross-sheet references.
This one's underused. Most people don't know it exists. It's perfect for linking a range — say twenty cells, or a whole column — without typing twenty formulas.
Steps: select the source range. Right-click, copy (or Ctrl+C). Switch to the destination sheet. Click the top-left cell of where you want the linked range to appear. Right-click and choose Paste Special. In the dialog, click the Paste Link button at the bottom-left. Done. Excel fills every cell in your range with a =SheetName!Cell formula pointing to the matching source cell.
The links update live. Change a value on the source sheet and the destination updates instantly. No drag-fill needed.
Paste Link creates relative references. If you copy A1:A10 and paste-link it starting at C5, the result is =Source!A1, =Source!A2, etc. — clean. But if you then drag-fill or copy that pasted range elsewhere, the references shift. To lock them, you'd need to manually convert to absolute reference excel by adding dollar signs.
Now we get clever. The INDIRECT function lets the sheet name come from another cell. Type a sheet name into cell B1, and =INDIRECT(B1&"!A1") pulls A1 from whatever sheet B1 names.
Why bother? Imagine a workbook with sheets named Jan through Dec, and one Summary sheet. Type "Apr" into Summary!B1, and =INDIRECT(B1&"!B5") returns Apr!B5. Type "Sep" instead and the same formula returns Sep!B5. One formula, twelve possible answers. Dropdown menus pair beautifully with this — read the indirect function excel deep-dive for the full pattern.
Fair warning: INDIRECT is a volatile function. Excel recalculates it every time anything in the workbook changes, even unrelated cells. On a workbook with thirty INDIRECT formulas, you'll feel it. On one with three hundred, you'll regret it. Use it where you need it. Don't sprinkle it everywhere.
=Sheet2!A1 — pulls A1 from Sheet2. Simplest cross-sheet reference. Use whenever the tab name has no spaces or special characters.
Combine multiple references in one formula: =Sheet1!A1+Sheet2!A1+Sheet3!A1 sums three values from three tabs.
='Q1 Summary'!B5 — single quotes around tab names with spaces, hyphens, apostrophes, or symbols. Excel adds them automatically if you build the reference by clicking.
If you write the reference by hand and forget the quotes, you'll see #NAME? — that's your signal to wrap quotes around the tab name.
=SUM(Sheet2!A1:A10) — sums a range from another sheet. Works the same as a same-sheet range, just with the SheetName! prefix added.
Pair with COUNTIF, AVERAGEIF, VLOOKUP — any function that accepts a range can take a cross-sheet range.
=SUM(Jan:Dec!B5) — adds cell B5 across all twelve monthly tabs in one shot. The colon between sheet names means "all sheets between these, inclusive."
Only works for sheets sitting next to each other in tab order. Drag a tab and the 3D formula updates.
=INDIRECT(B1&"!A1") — sheet name pulled from cell B1. Change B1, change the source tab. Pair with data validation dropdowns for a clean dashboard.
Use with caution on big workbooks — it's volatile and slows recalculation.
Probably the most powerful linking trick in Excel. Type =SUM(Jan:Dec!B5) and Excel adds cell B5 across every tab from Jan to Dec, inclusive. Twelve sheets. One formula. No tab-by-tab clicking.
The colon between sheet names tells Excel "every sheet between these in tab order." So if your tabs go Jan, Feb, Mar... Dec from left to right, that one formula sums all of them. Add a new tab called "July-Adj" between June and July, and the SUM picks it up automatically.
3D references work with SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, PRODUCT, and STDEV. They don't work with VLOOKUP, INDEX, MATCH, or most lookup functions. That's the trade-off — 3D is built for aggregation, not lookup.
If you reorder your tabs, the 3D formula's scope changes too. Drag Jan to the end of the workbook, and =SUM(Jan:Dec!B5) might suddenly mean something else — or break entirely. Keep a fixed tab order when you're using 3D references, or use named ranges instead (more on that below).
Different beast. A hyperlink doesn't pull data — it navigates. Click the cell, jump to another tab. Useful for table-of-contents sheets, dashboard navigation, or jumping between related sections.
To add one: select the cell that'll become the link. Go to Insert → Link (Ctrl+K on Windows, Cmd+K on Mac). In the dialog, choose Place in This Document on the left. Pick the sheet you want to jump to. Optionally specify a cell. Click OK.
The cell now shows blue underlined text. Click it — you're teleported to the chosen tab. For more on the hyperlink mechanics and styling options, the how to create hyperlink in excel guide covers every variation including HYPERLINK() formulas for dynamic links.
Drop hyperlinks on a single "Index" tab pointing to each section of a big workbook. Users click section names instead of hunting through tabs. Old trick. Still works in 2026.
This is the cleanest cross-sheet linking method for serious workbooks. Define a name once, use it everywhere — without ever typing SheetName!Cell again.
Open the Name Manager (Formulas → Name Manager, or Ctrl+F3). Click New. Give your reference a memorable name like JanRevenue. Set the scope: Workbook (visible from any sheet) or Sheet (only visible from one sheet). Type the reference: =Jan!B5. Click OK.
Now anywhere in the workbook, you can write =JanRevenue instead of =Jan!B5. If somebody renames the Jan tab to January, Excel updates the named range automatically. Every formula using JanRevenue keeps working. Compare that to fifty hardcoded =Jan!B5 formulas, every one of which would break the moment Jan becomes January.
A workbook-scoped name is visible globally — type =JanRevenue on any tab, it works. A sheet-scoped name only resolves from one tab, which lets you reuse the same name across sheets with different values. Useful, but rare. Most named ranges should be workbook scope.
For automation. If you're writing a macro to copy values, pull data, or rebuild a dashboard, you'll reference other sheets by object syntax: Worksheets("Sheet2").Range("A1").Value. That gets you the value at A1 on Sheet2.
Assignment goes the same direction: Worksheets("Summary").Range("D10").Value = Worksheets("Details").Range("B5").Value. That copies B5 from Details into D10 on Summary. No formula — just a value transfer.
For deeper macro work and the basic link cells in excel patterns within VBA, write helper functions instead of repeating the long syntax everywhere. Workbooks with hundreds of cross-sheet operations get unreadable fast if you don't.
Every cross-sheet reference adds calculation overhead. A workbook with fifty linked cells? You won't notice. A workbook with fifty thousand? You'll feel it open in slow motion.
The worst offenders: volatile functions (INDIRECT, OFFSET, TODAY), complex 3D references across many tabs, and nested cross-sheet lookups. If your workbook is dragging, the fix order is usually: replace INDIRECT with direct refs where possible → convert formulas to values where you don't need live updates → split huge sheets into separate workbooks and Paste Link only the needed summary cells.
If links break or you need to remove them, the link break in excel guide shows the Edit Links dialog and how to convert formulas to static values in one shot. That's often the right move for archival workbooks.
Create twelve monthly tabs: Jan, Feb, Mar … Dec. Drop your data on each.
Insert a thirteenth tab labeled Summary. This is where you'll pull totals from each month.
On Summary, type =SUM(Jan:Dec!B5) to sum cell B5 across all twelve sheets in one formula.
Put a dropdown in Summary!A1 with month names. Reference =INDIRECT(A1&"!B5") to show just that month's value.
Add hyperlinks on Summary pointing to each month tab. Click any link to jump to that month's detail.
Three errors show up over and over when linking sheets. Learn to recognize them and you'll diagnose problems in seconds instead of minutes.
The cell or sheet your formula points to no longer exists. Most common cause: someone deleted a sheet. Second most common: someone deleted rows or columns that held the referenced cell. Excel can't show the value because the address is gone. Fix it by retyping the reference to a valid cell.
Excel doesn't recognize part of your formula. With cross-sheet refs, this usually means a misspelled sheet name or missing quotes around a sheet name with spaces. Check spelling first. Then check whether the tab needs single quotes. =Q1 Summary!A1 fails — ='Q1 Summary'!A1 works.
The referenced cell holds text where the formula expects a number (or vice versa). Pull A1 from another sheet into a SUM formula, and if A1 contains a word, you'll see #VALUE!. Fix the source data or use IFERROR to handle it gracefully.
Cross-sheet linking is great. It's not always right. Three situations where you should reconsider.
First: tiny workbooks with two or three values. Don't build infrastructure for nothing — type the numbers directly into the summary cell. Faster, simpler, less to break.
Second: read-only archives. Once a workbook is finalized, convert formulas to values (Copy → Paste Special → Values). Future-you opening this file in two years won't remember what each link was for, and won't have to.
Third: data that updates from external sources. If your monthly numbers come from a database or another file, use Power Query, not cross-sheet links. Cross-sheet linking assumes the data is already in your workbook. Power Query pulls fresh data from outside on demand. Different jobs, different tools.
For broader Excel skills and certification prep, the cell reference in excel resource covers absolute and relative references in depth — those concepts apply to cross-sheet references too, with dollar signs working identically across tabs.
One of the most common real-world uses for cross-sheet references is feeding a VLOOKUP. Your lookup table lives on one tab. Your invoices, transactions, or records live on another. The formula bridges them: =VLOOKUP(A2, Prices!A:C, 3, FALSE) takes the value from A2 on the current sheet, finds it in column A of the Prices tab, and returns column 3.
Same idea works with INDEX/MATCH, XLOOKUP, and the newer FILTER and SUMIFS functions. You're just adding SheetName! in front of any range argument. Excel doesn't care which tab the range lives on — the function reads it the same way either way. That's the magic of cross-sheet references: every range-accepting function in Excel works across tabs without modification.
Don't put your lookup formula on the same tab as the lookup table if you're building a dashboard. Separate them. Keep raw data on data tabs, formulas on a calculation tab, and final display on a presentation tab. The whole point of cross-sheet linking is the separation of concerns — flatten everything into one tab and you've lost the benefit.