Referencing other sheets
Not a function, but a piece of syntax. Any formula can reach a cell or range on another sheet in the same workbook.
Real workbooks stay organized by splitting work across sheets: assumptions and rates on their own sheet, raw detail data on its own sheet, and a summary sheet that points at both instead of duplicating their numbers. The syntax that makes that possible is just a sheet name followed by an exclamation mark in front of an ordinary cell reference.
Every function already covered elsewhere on this site, SUM, IF, VLOOKUP, or plain arithmetic, works exactly the same way the moment one of its cell references points at another sheet instead of the current one.
Syntax
| Argument | Description |
|---|---|
| SheetName! | The sheet to reach into, followed by "!". No quotes needed if the name has no spaces, e.g. Rates!B2. |
| 'Sheet Name'! | If the sheet name contains a space (or starts with a number), wrap it in single quotes: 'Marketing Spend'!B2:B4. |
A real example: a tax rate kept on its own sheet
An invoice total that reaches across to a separate "Rates" sheet rather than hardcoding the percentage into every row:
Keeping the rate on its own sheet means updating it in one place changes every formula that references it, instead of hunting down every row where 0.08 was typed in directly.
Worked examples
| A | B | |
|---|---|---|
| 1 | Item | Amount |
| 2 | Consulting | 500 |
On a separate sheet named "Rates":
| A | B | |
|---|---|---|
| 1 | Item | Rate |
| 2 | Consulting | 0.08 |
(Rates!B2 holds 0.08 on a separate "Rates" sheet)
| A | B | |
|---|---|---|
| 1 | Category | Total |
| 2 | Marketing |
On a separate sheet named "Marketing Spend":
| A | B | |
|---|---|---|
| 1 | Week | Spend |
| 2 | Week 1 | 1200 |
| 3 | Week 2 | 1200 |
| 4 | Week 3 | 1200 |
(quoted because the sheet name has a space)
Common mistakes
- Forgetting the quotes around a sheet name that has a space. The formula will throw an error rather than silently misreading it.
- Typing a colon instead of an exclamation mark (Sheet:B2 instead of Sheet!B2). B2 alone still needs the ! to say which sheet.
- Assuming a function that takes a range (like SUM or VLOOKUP's table) can't point at another sheet. It can, exactly as shown above.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice cross-sheet references →