Reference another sheet in Excel: formulas across sheets
Real workbooks rarely have one sheet: raw data on one, a price list on another, the report on a third. Here you learn how a formula pulls values from other sheets and how a single formula adds up a whole set of month sheets.
In short: =Prices!B3 fetches cell B3 from the Prices sheet. If the sheet name contains spaces, add single quotes: ='Sales 2026'!B3. Across several sheets in a row, use =SUM(Jan:Mar!B5).
Try it now
The exercises on this page are small workbooks with several sheets. Switch sheets with the tabs below the grid, even while you are typing a formula, just like in Excel.
Task The prices are on the Prices sheet. In C2, calculate the amount for the coffee: quantity times the price from the Prices sheet.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Quantity | Amount |
| 2 | Coffee | 12 |
| A | B | |
|---|---|---|
| 1 | Item | Price |
| 2 | Tea | 3.5 |
| 3 | Coffee | 4.2 |
| 4 | Cocoa | 2.8 |
How a reference to another sheet works
| Written as | Meaning |
|---|---|
Prices!B3 | cell B3 on the Prices sheet |
Prices!A2:B9 | range A2:B9 on the Prices sheet |
'Sales 2026'!B3 | a sheet name with spaces or special characters goes in single quotes |
Jan:Mar!B5 | cell B5 on every sheet from Jan to Mar (3D reference) |
B3 | no sheet name: always the sheet the formula is on |
You do not have to type the reference. Type =, click the tab of the other sheet and then the cell. Excel adds the sheet name and the exclamation mark. Press Enter to return to the formula's sheet.
Adding up month sheets: the 3D SUM
Many teams keep one sheet per month with the same layout. If the monthly total is in the same cell on each sheet, one formula adds up every sheet in between. AVERAGE, MIN, MAX and COUNT work the same way.
Task Each month has its own sheet with the total in B5. In B2, add up the three monthly totals with a single SUM across the sheets Jan to Mar.
| A | B | |
|---|---|---|
| 1 | Quarter 1 | |
| 2 | Total revenue |
| A | B | |
|---|---|---|
| 1 | Branch | Revenue |
| 2 | North | 4200 |
| 3 | South | 5100 |
| 4 | West | 3900 |
| 5 | Total | 13200 |
| A | B | |
|---|---|---|
| 1 | Branch | Revenue |
| 2 | North | 4100 |
| 3 | South | 4500 |
| 4 | West | 4800 |
| 5 | Total | 13400 |
| A | B | |
|---|---|---|
| 1 | Branch | Revenue |
| 2 | North | 5600 |
| 3 | South | 4300 |
| 4 | West | 4700 |
| 5 | Total | 14600 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
Note: a 3D reference follows the order of the tabs, not the names. Move a sheet between Jan and Mar and it is included; drag it after Mar and it drops out.
SUMIF and VLOOKUP across sheets
Ranges on other sheets work in every function. A typical report adds up expenses from another sheet by category: the criterion is on your own sheet, the ranges are on the data sheet. The SUMIF lesson explains the function itself.
Task The expenses are on the Expenses sheet. In B2, use SUMIF to work out how much was spent in the category in A2.
| A | B | |
|---|---|---|
| 1 | Category | Total |
| 2 | Office |
| A | B | |
|---|---|---|
| 1 | Category | Amount |
| 2 | Office | 89.9 |
| 3 | Travel | 240 |
| 4 | Office | 35.5 |
| 5 | IT | 1200 |
| 6 | Travel | 64.2 |
| 7 | Office | 18.4 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
In the same way, VLOOKUP reads a price list on another sheet: =VLOOKUP(A2,Prices!A2:B50,2,FALSE). To keep the range fixed when you fill the formula down, lock it with $: Prices!$A$2:$B$50. See absolute references.
Common mistakes
#REF! after deleting a sheet
If you rename a sheet, Excel updates every formula for you. If you delete it, every reference to it shows #REF!. A sheet name that does not exist also gives #REF! in the exercise, while desktop Excel opens a file dialog because it assumes another workbook.
Missing quotes
Excel cannot read =Sales 2026!B3. As soon as a name contains spaces, hyphens or brackets, it needs single quotes.
Sheet name in a cell
If the sheet name should come from a cell, for example a month picker, a normal reference will not do. That is what INDIRECT is for: =INDIRECT("'"&A1&"'!B5").
References to other workbooks
A reference can also point into another file, such as ='[Budget.xlsx]Plan'!B3. That works while the file stays where it is. If it moves, Excel asks on opening whether to update the links. Before you send a report, replace such formulas with values (Copy, Paste Special, Values).
In German Excel
The exclamation mark notation is the same in every language version. German Excel calls the functions SUMME, SUMMEWENN and SVERWEIS and separates arguments with a semicolon: =SUMME(Jan:Mär!B5).
Which Excel versions support this?
References to other sheets and 3D references work in every version of Excel for Windows and Mac and in Excel for the web.
FAQ
How do I link two sheets in Excel?
Type = in the target cell, switch to the other sheet with its tab, click the cell and press Enter. When the value on the other sheet changes, the linked cell changes too.
Can I edit several sheets at once?
Yes. Ctrl+click several tabs and Excel groups them, so every entry goes into all selected sheets. Ungroup afterwards (right-click a tab), or you may overwrite data by accident.
How do I compare two sheets?
Use a formula like =A2=Sheet2!A2 in a helper column, or COUNTIF to check whether a value exists on the other sheet. The lesson on comparing columns covers this in detail.