How to Reference Another Sheet in Google Sheets?

This tutorial covers the subject of referencing data from another sheet or another spreadsheet altogether in Google Sheets.

Wouldn’t it be nice to lookup info from another huge sheet—like totals and averages—without having to copy-paste it onto your sheet? Especially if it’s a dynamic sheet with regularly updated info.

Instead of having to copy and paste sections of data in existing sheets on a spreadsheet or inside separate spreadsheet files in the cloud, you can instead use those tables as lookup tables with this neat trick on how to reference another sheet in google sheets.

How to Reference Another Sheet in Google Sheets

Referencing sheets allows you to categorize different things better, with them separated by sheets instead of just by tables, rows, and columns. This is particularly handy with huge chunks of data wherein you only need their monthly sums or averages.

Obviously, you should have some sort of access to the Google Sheet you’re referencing, particularly if you’re not its author. It should be shared to you to at least the level of being able to look at it without editing privileges.

For instance, a sheet might not hold all the products you sell including UPC codes. Or you want to summarize the total sales on a separate sheet that references the more detailed breakdown in the first sheet.

Method 1: Referencing a Sheet in the Same Spreadsheet

Step 1: Open a new or existing Google Sheets file.

In my case, I separated a 5-column table and placed the last column in a separate sheet to illustrate how to reference data from one sheet to another in the same spreadsheet.

Step 2: Select the column next to the table (Column E). We’ll be referencing the TESLA column here. The TESLA column on Sheet2 occupies cells B1 to B14.

Now let’s type the equals sign followed by the sheet name (Sheet2) then an exclamation point, and the cell you’re copying. For instance, on Cell E1, let’s reference Sheet2’s Cell B1 with the following:

=Sheet2!B1

The sheet even auto-fills for your convenience. You can manually put in the rest as “=Sheet2!B2“, “=Sheet2!B3“, and so forth until “=Sheet2!B14” if you want.

Step 3: If the sheet has a name, it should instead be:

='Sheet Name'!B1
Code language: JavaScript (javascript)

The single quotes help out in referencing sheet names that contain non-alphanumeric symbols, spaces, and so forth in the formula for referencing. Otherwise, those symbols might render the formula unreadable or induce a syntax error.

Let’s rename Sheet2 to illustrate how to use that new formula.

So in our case, it should translate to “=’TESLA Revenue’!B1“

The results should be the same. Regardless, it should be plain to see that the formatting in Sheet2 or the “TESLA Revenue” sheet is missing in Sheet1. This referencing formula only gets values, not formatting.

Method 2: Referencing Another Spreadsheet Altogether

Step 1: Let’s use a separate table this time. An annual revenue comparison chart spanning 2019 to 2021.

The entries for April to December 2019 are empty. This is because they’re located on a separate sheet we’ll be opening right about now.

Step 2: Open the second spreadsheet on a new tab beside your current spreadsheet for easy reference. Take note of the other spreadsheet’s URL and sheet name.

We’ll be referencing the total amount payable for every month, starting with April at Cell H9.

Step 3: Go back to the original table and type in the following formula:

=IMPORTRANGE("URL","SheetX!YY")
Code language: JavaScript (javascript)

Just insert the URL of your second spreadsheet on the URL portion, replace X with the sheet number, and YY with the cell designation

<em>=IMPORTRANGE("https://docs.google.com/spreadsheets/d/</em>██████████████████████████████████████<em>,"Sheet1!H9")</em>
Code language: HTML, XML (xml)

We’ve redacted the file URL for privacy, but you should have a rough idea of what it meant.

Long story short, “IMPORTANTRANGE” works essentially the same as referencing data from separate sheets, but with the added functionality of inserting the URL of the separate sheet.

Step 4: What if two cells contain the same info needed for one cell? As in, you need to add the two values together to get the total on the cell? Here’s what you need to do.

For example, the month of April actually has two sums available, so you need to add the two cells (H9 and H35) together to get the total amount of work done for the month.

To do this using IMPORTRANGE, we need to add the SUM function into the equation like so.

<em>=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/</em><em>██████████████████████████████████████","Sheet1!H9:H35"))</em>
Code language: HTML, XML (xml)

Just fill in the rest of the references to complete the table and you’re good to go.

The Big Picture

Use the formula of =SheetX!YY wherein X refers to the sheet number and YY refers to the cell name to reference a separate sheet on the same spreadsheet file. Or if it’s a named sheet, use the formula of =’Sheet Name’!YY, with the ‘Sheet Name’ referring to the name of the sheet.

For references to a separate spreadsheet, use the formula of =IMPORTRANGE(“URL”,”SheetX!YY”) or =IMPORTRANGE(“URL”,”‘Sheet Name’!YY”). Just replace the URL with a link to the Google Sheets file followed by a cell reference.

References:

1. “Reference data from other sheets“, Google.com Support, Retrieved July 6, 2022
2. “How to Link Data Between Multiple Spreadsheets“, Couple.io Blog, Retrieved July 6, 2022
3. Ryan Dube, “How to Use Google Sheets to Reference Data From Another Sheet“, Lifewire, December 18, 2021
4. Prolific Oaktree, “Google Sheets | How to Reference Cells in Other Sheets“, YouTube, YouTube, October 28, 2021

Google Sheet: Make a copy

Google Sheet Referenced: Make a copy here