Working with several sheets
9 minutes · 5 steps · free, no sign-up
A real workbook is rarely one big sheet. Prices live on one tab, orders on another, totals on a third. The trick is to keep each fact in one place, and let formulas on other tabs read it. Change a price once, and every order using it updates.
- Switch between sheets using the tabs at the bottom
- Refer to a cell on another sheet, such as Prices!B2
- Fill a cross-sheet formula down
- Change a value on one sheet and watch another update
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceQuick reference
=Prices!B2 one cell on the Prices sheet
=SUM(Prices!B2:B4) a range on another sheet
='Price list'!B2 quote names that contain spaces
=B2*Prices!B2 mix this sheet and anotherThe key ideasSummary: Working with several sheets
A reference to another sheet is the sheet name, an exclamation mark, then the cell: Prices!B2. If the name has a space, put it in single quotes. It behaves like any other reference, so it can be filled down and used inside bigger formulas.
This is how you keep data in one place. When the Pens price is only typed on the Prices sheet, changing it there updates every formula that uses it. Typing the price into each order row instead would mean changing it everywhere, and missing one.
Watch out forCommon mistakes
- Typing a price again on every sheet. Copies drift apart. Refer to the original.
- Forgetting the quotes. A sheet called Price list needs
'Price list'!B2. Without the quotes you get an error. - Renaming a tab and breaking nothing. Sheets updates references when you rename a tab, but typed text such as a name inside quotes in INDIRECT will not follow.
- Typing on the wrong tab. If a formula seems to vanish, check which tab is selected.
Next stepTry it in the real Google Sheets
Sheets also has IMPORTRANGE, which reads from a different spreadsheet file entirely, covered at the Adept level. Clicking a cell on another tab while you type a formula inserts the reference for you.