How to reference another sheet in Excel
Last updated
To use a value from another sheet, type =, click the sheet’s tab, click the cell and press Enter. Excel writes the sheet name, an exclamation mark and the cell: =Jan!B5. When the name contains a space, it goes between single quotes: ='Main Street'!B5.
How a sheet reference is written
=Sheet!A1 ='Sheet name'!A1
- Jan
- The sheet name, exactly as it appears on the tab. Upper or lower case doesn’t matter.
- !
- Separates the sheet name from the reference. Every reference to another sheet needs it.
- ' '
- Single quotes around the name when it contains spaces or punctuation:
'Main Street'!B5. Excel adds them for you when you click. - B5
- A cell or a range, like any reference:
Jan!B2:B6is a range on the Jan sheet. Add$to lock it as usual.
Practice sheet references here
The grid has several sheets: switch between them with the tabs under it, or with Ctrl + Page Down and Ctrl + Page Up, then type your formula on the Summary sheet.
Bring a total from another sheet
January’s sales are on the Jan sheet, with the month’s total in B5. Click the tabs under the grid (or press Ctrl + Page Down) to look at it, then come back to Summary. In B2, show that total with a reference to the other sheet.
| Month | Sales ($) |
| January |
How to reference another sheet, step by step
- Select the cell that should show the result and type
=. - Click the tab of the other sheet at the bottom of the window, or press Ctrl + Page Down to move to the next sheet.
- Click the cell you need, or drag over a range inside a function such as SUM.
- Press Enter. Excel goes back to your sheet and the formula bar shows the reference, such as
=Jan!B5. You can also type it directly.
Examples of formulas across sheets
One cell from another sheet
=Jan!B5
Shows the total in B5 of the Jan sheet, and updates when it changes.
The same cell on several sheets
=Jan!B5+Feb!B5+Mar!B5
Adds the monthly totals into a quarter. Each reference names its own sheet.
A range on another sheet
=SUM(Mar!B2:B6)
Any function accepts a range from another sheet.
A sheet name with spaces
='Park Avenue'!B5
The quotes go around the name only, before the exclamation mark.
A lookup table on its own sheet
=VLOOKUP(A2,Prices!$A$2:$B$6,2,FALSE)
Keep the price list on a separate sheet and look it up from anywhere in the workbook.
Common problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #REF! | The sheet the formula pointed at was deleted. | Deleting a sheet usually can’t be undone: reopen the last saved version if you still need it, or retype the reference to a sheet that exists. |
| An Update Values window opens | Excel found no sheet with that name, often because of a typo or a name with spaces typed without quotes, and is looking for another file. | Cancel, check the spelling on the tab and add single quotes if the name has spaces. |
| 0 | The cell on the other sheet is empty, or the formula points at the wrong row. | Click the tab and check the cell the formula names. |
| Same month on every row | Copying a formula moves its cells but never changes the sheet name. | Edit the sheet name in each row, or write each formula by clicking its own sheet. |
Another sheet, another workbook or a copy?
A reference to another sheet in the same workbook (=Jan!B5) stays up to date and survives renaming: rename the tab and Excel rewrites every formula that uses it. A reference to another workbook adds the file name in brackets, such as =[Sales.xlsx]Jan!$B$5, and depends on that file staying where it is. Copying the values instead of referencing them is simpler, but they go stale as soon as the source changes, so keep a reference while the figures can still move.
Frequently asked questions
How do I reference a cell in another sheet in Excel?
Type =, click the other sheet’s tab, click the cell and press Enter. The result looks like =Jan!B5: sheet name, exclamation mark, cell.
Why are there single quotes around the sheet name?
The name contains a space or punctuation, as in 'Main Street'!B5. Without the quotes, Excel couldn’t tell where the name ends.
What happens to my formulas if I rename a sheet?
Excel updates them for you: =Jan!B5 becomes =January!B5 everywhere. Deleting the sheet, on the other hand, turns them into #REF! errors.
Can VLOOKUP or SUM use data from another sheet?
Yes. Any argument that takes a range accepts one from another sheet, such as =SUM(Mar!B2:B6) or =VLOOKUP(A2,Prices!$A$2:$B$6,2,FALSE).
Keep practicing
All 5 exercises on formulas across sheets, with explanations and progress tracking.
Open the sheet references practice set