To reference a cell on another sheet in the same workbook, put the sheet name and an exclamation point in front of the cell address: =Sheet2!B2 returns the value of cell B2 on Sheet2. If the sheet name contains a space or a hyphen, wrap it in single quotes: ='Q1 Sales'!B2. To reach another workbook, add the workbook name in square brackets: =[Budget.xlsx]Annual!C10.
That is the entire syntax family. The rest of this guide covers the pointing route that builds these references for you, the single-quote rule that trips people up most often, external workbook references in their open and closed forms, 3-D references across many sheets, and the specific ways each kind breaks.
How do you reference a cell on another sheet?
The typed form is sheet name, exclamation point, cell address:
Nobody has to memorize that, because pointing builds it. Select the cell where the formula should live, type = (plus any function you need, like =SUM(), select the tab of the worksheet you want, then select the cell or range there and press Enter. Excel writes the sheet-qualified reference into the formula for you.
The part after the ! is an ordinary cell reference, so everything in relative and absolute references still applies: =Sheet2!B2 shifts when filled down, =Sheet2!$B$2 stays pinned.
When does a sheet name need single quotes?
Microsoft’s rule covers sheets and workbooks alike: “you must enclose the name (or the path) within single quotation marks” whenever the name contains spaces or non-alphabetical characters. The wording is broader than Microsoft’s own examples, which leave the trailing digit in Sheet2 unquoted; in practice, spaces, hyphens, and other punctuation inside the name are what force the quotes. So Sheet2 needs nothing, and Q1 Sales needs them:
A hyphen counts too, which matters more than it sounds: version suffixes like Budget-v7 are exactly how working files get named, and the hyphen quietly puts them under the quoting rule. Sheet names themselves have limits worth knowing while you are here: at most 31 characters, and none of / \ ? * : [ ], several of which Excel reserves for reference syntax.
How do you reference another workbook?
A reference into a different file is what Microsoft now calls a workbook link (the older name, external reference, is still everywhere). It has two forms, and Excel switches between them on its own.
While the source workbook is open, the link carries the workbook name in square brackets, then the sheet name, the exclamation point, and the cells. This is Microsoft’s own example:
Creating one works like the cross-sheet pointing route: type = and your function, switch to the source workbook, click the worksheet that holds the cells, select them, and press Enter.
When you close the source workbook, Excel automatically appends the full file path to the formula, and the whole path-plus-workbook-plus-sheet lands inside one pair of single quotes:
The quoting rule from the previous section follows you here, and this is where it bites. A workbook named Budget-v7.xlsx has a hyphen in it, so even the open-workbook form needs quotes around the bracketed name and sheet together:
What is a 3-D reference?
A 3-D reference points at the same cell or range on a whole run of sheets. Five identically laid-out sheets, one total:
That formula adds the values of A2:A5 on every sheet from Sheet2 through Sheet6, endpoints included. To build one, type =SUM(, click the tab of the first sheet, hold Shift and click the tab of the last sheet, select the range, and press Enter.
Not every function accepts a 3-D reference. The functions that do include SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, the STDEV and VAR families, and the newer VSTACK and HSTACK; Microsoft’s 3-D reference page has the full list. VLOOKUP and IF are not on it.
The span is defined by its endpoint sheets, and it is elastic. Per Microsoft’s documentation of the Sheet2:Sheet6 example: insert or copy sheets between the endpoints and their values join the calculation; delete sheets between the endpoints, or drag them out of the span, and their values leave it. Move an endpoint sheet elsewhere in the workbook and Excel adjusts the calculation to whatever now sits between the endpoints, unless the move reverses their order. Delete an endpoint and its values drop out. The elasticity is the feature and the risk in one: a sheet inserted or copied into the middle of the run joins every 3-D total silently.
How do cross-sheet and cross-workbook references break?
Within one workbook, references break the way all references break: delete the cells a formula pointed at and it returns #REF!. Deleting a whole referenced worksheet is the severe case. Microsoft’s guidance says it plainly: “a worksheet that’s been deleted can’t be recovered”, so a formula erroring against a deleted sheet cannot be repaired, only rewritten. If the deletion just happened, Ctrl+Z is the escape hatch for deleted rows and columns. The errors guide covers #REF! alongside the rest.
Workbook links break differently, and more quietly. Move or rename the source file and the formula does not error: it stays intact and keeps showing its last value until you update it. The break surfaces at open time instead. On the desktop, opening a workbook that contains links gets you a warning dialog with Update and Don’t Update (Excel for the web asks you to trust workbook links instead), and a link whose source has moved cannot update.
The repair surface depends on your build. Current Microsoft 365 uses the Workbook Links pane, under Data > Queries and Connections: select the source that moved, open its more-commands menu, choose Change source, and browse to the file’s new location. Older perpetual and Mac builds use the Data > Edit Links dialog, where Change Source does the same job; Microsoft replaced that legacy command with the pane in current Microsoft 365, and it only reappears if you add it back to the ribbon yourself.
Both surfaces also hold Break Link, which severs a link on purpose: every formula that used the source is converted to its current value. On the desktop this cannot be undone, so Microsoft’s own advice is to save a backup copy of the workbook first.
When is a named range the better tool?
A raw cross-sheet reference says where a value lives; a named range can say what it is. A workbook-scoped name is recognized on every sheet, so =SUM(Sales) works anywhere in the workbook with no sheet prefix and no quoting rule to remember, and when the source range moves, you repoint the name once instead of editing every formula that spelled out 'Q1 Sales'!B2:B50. Names even cover the 3-D case: Formulas > Define Name can hold a reference that spans a whole set of sheets, built with the same Shift-click selection.
The trade-off is indirection, covered honestly in the named-range guide. For a one-off pull from another sheet, the plain reference is fine. For a range that many formulas on many sheets depend on, name it.
What changed in the workbook you linked to?
A workbook stitched together with links has a property worth being nervous about: its numbers change when the source files change, and nothing inside it announces that. The formulas look identical, the file was never edited, and the totals are new. When a linked source workbook was edited and you need to know what moved before trusting the roll-up, compare the two versions of the source instead of eyeballing it.
The short version
Reference another sheet as =Sheet2!B2, and wrap the name in single quotes the moment it contains a space, a hyphen, or other punctuation: ='Q1 Sales'!B2. Reference another workbook as =[Budget.xlsx]Annual!C10 while it is open; close it and Excel rewrites the link with the full quoted path. Reference a run of sheets with a 3-D reference like =SUM(Sheet2:Sheet6!A2:A5), remembering that sheets inserted into or dragged out of the span join and leave the total silently.
Build all three by pointing rather than typing: =, then click the tab, then click the cells. When a reference breaks, deleted cells and sheets show up as #REF!, but a moved or renamed source workbook fails quietly, surfacing only in the update warning at open time and fixable with Change source in the Workbook Links pane (Data > Edit Links in older builds). And when many formulas lean on one remote range, a named range is usually the sturdier tool.
