Google Sheets | Reference Cell in Another Sheet

How do we reference data from one sheet to another sheet in Google Sheets? And can we do this with two completely different spreadsheets? Yes, with this simple formula, you’ll be able to reference cells in another sheet or in another spreadsheet!

Reference Cell in Another Sheet

To reference a cell within another sheet, you must follow this formula:

=Sheet1!A1

Replace Sheet1 with the name of your sheet, and A1 with the cell reference. Don’t forget the exclamation mark. You may also perform calculations, such as =Sheet1!A1+Sheet1!A2

In the above example. we are adding up the cells B2:B in a sheet called Jan.

If your sheet name contains spaces or non-alphanumeric you’ll get an error. Instead, enclose single quotes around your sheet name in the formula.

='Sheet1'!A1

View this article by Google for more information.

---

Reference Cell in Another Spreadsheet

First, we’ll need to use the IMPORTRANGE function, which follows this pattern

=IMPORTRANGE(spreadsheet_url, range_string)

Retrieve your spreadsheet URL from the spreadsheet you’re trying to get the data from. Make sure to include the https://. Then we’ll just repeat the formula from above to reference the cell.

=Sheet1!A1

So all together:

IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Sheet1!A1:B10")
 

In this example, we reference another spreadsheet, and inside that spreadsheet, we reference E1 in the Jan tab.

Remember, you’ll need to accept permissions once you enter your IMPORTRANGE formula.

This IMPORTRANGE documentation explains further.