Start for free

All Excel functions

Working with multiple sheets in Excel: how it works

To use a cell on another sheet, write the sheet name, an exclamation mark and the cell: =Prices!B2. With =SUM(Jan:Mar!B2) you add up the same cell across several sheets.

Try the first exercises for freeIn the lesson Working with several worksheets

How do you work with multiple worksheets?

A workbook can contain several worksheets. At the bottom you see them as tabs, such as Sheet1, Prices or Jan. That way you keep data that belongs together separate, for example a sheet with prices and a sheet with orders, or a sheet per month.

In a formula, you use a cell from another sheet with the name of the sheet, an exclamation mark and the cell: =Prices!B2. If the price on the Prices sheet changes, every formula that refers to it recalculates automatically. With =SUM(Jan:Mar!B2) you can even add up the same cell across a whole series of sheets.

When do you use multiple worksheets?

  • You keep the prices on one sheet and use them in your orders: =B2*Prices!B2.
  • You have a sheet per month and want an overview on a separate sheet: =Jan!B5.
  • You add up the same cell from all the monthly sheets: =SUM(Jan:Mar!B2).
  • You look up a price in a list on another sheet: =VLOOKUP(A2,Prices!A2:C5,3,FALSE).

Using a cell from another sheet, step by step

C2 contains =B2*Prices!B2. The quantity on this sheet times the price on the Prices sheet: 6 × 89 = 534. Click Prices below the worksheet to see or change the price. Change the numbers or the formula and see what happens.

C2
A
B
C
D
E
F
G
H
1
Product
Quantity
Total
2
Chair
6
£534.00
3
4
5
6
7
8
9
10
Ready

This is how to go about it:

  1. Click C2, where the total should go, and start the formula with the quantity and a multiplication sign.=B2*
  2. Click the Prices tab at the bottom. Your formula stays open while you switch sheets.
  3. Click the cell with the price, B2. Excel writes the name of the sheet and the exclamation mark for you.=B2*Prices!B2
  4. Press Enter. Excel jumps back to your first sheet and shows the total: 534.00.

More examples

Adding up across sheets

B2 contains =SUM(Jan:Mar!B2). SUM adds up B2 from the Jan sheet, the Mar sheet and every sheet in between: 1200 + 980 + 1410. Click the tabs to see the figures per month.

B2
A
B
C
D
E
F
G
H
1
Quarter
Revenue
2
First
£3,590.00
3
4
5
6
7
8
9
10
Ready

A sheet name with a space

B2 contains =A2*(1+'Rates 2026'!B1). There's a space in the name of the sheet, so the name goes in single quotation marks. Excel adds them automatically when you click the cell.

B2
A
B
C
D
E
F
G
H
1
Amount
Incl. tax
2
£200.00
£240.00
3
4
5
6
7
8
9
10
Ready

Looking up on another sheet

B2 contains =VLOOKUP(A2,Prices!A2:C5,3,FALSE). The list of codes is on the Prices sheet. If you fill the formula down, lock the range with dollar signs: Prices!$A$2:$C$5.

B2
A
B
C
D
E
F
G
H
1
Code
Price
2
P-204
£219.00
3
4
5
6
7
8
9
10
Ready

Where it often goes wrong

  • If you rename a sheet, Excel updates the formulas by itself. If you delete a sheet, the formulas that refer to it get #REF!.
  • With =SUM(Jan:Mar!B2) the order of the tabs matters: a sheet you drag between Jan and Mar is included.
Want to try it yourself?
Practise this in the lesson Working with several worksheets. The first exercises are free.

Try the first exercises for free

Frequently asked questions

Do I have to type the sheet name myself?

No. Type = in your formula, click the tab of the other sheet at the bottom and click the cell there. Excel writes the reference for you.

Can I refer to another workbook too?

Yes. Excel then puts the file name in square brackets before the sheet name, such as =[Budget.xlsx]Jan!B2. A link like that only works as long as the other file stays in the same place.

Read next

Creating a formulaFilling and copying formulasOrder of operationsAbsolute references ($)Calculating percentages