How To Reference Another Sheet
Quick Navigation
When you’re working with complex spreadsheets, it’s a good idea to keep data organized by dividing it up into separate sheets.
It might seem like this will make it harder to do calculations with data on other sheets, but fortunately Google Sheets makes it easy to reference data on other sheets in your spreadsheet.
If you know the basic A1 notation for referencing cells in the same sheet, referencing cells in a different sheet is very similar.
A standard cell reference contains two piece:
- A letter or letters that define the column
- And a number that defines the row
Any cell in a given sheet can be assigned a unique cell reference following this A1 notation.
However, when you get multiple sheets involved things become more complicated because each can contain the same column letters and row numbers.
For example, you will have a cell A1 on every sheet in your spreadsheet
To get around this, Google Sheets adds an additional piece when referencing ranges on another sheet.
Here’s how to construct a reference to a cell on another sheet:
Step 1
Select a cell and type the equals sign (=) to start typing a formula
Step 2
Type the name of the sheet you want to reference. If the sheet name includes spaces, you must wrap it in single quotes. If you omit the quotes or use double quotes, the formula will not work.
Step 3
Type an exclamation mark (!) to separate the sheet name from the column letter
Step 4
Type the A1 notation reference you would use to reference the cell if you were referencing it from the same sheet. The resulting reference should look something like this
Step 5
You’ll know the reference is correctly formatted when it turns from black to colored in the formula bar
Your reference will now display whatever data is currently in the referenced cell on the other sheet and update just like a regular cell reference
Summary
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to reference another sheet in Google Sheets. Want more? Check out all the Google Sheets Tutorials.