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.

Method 1 Step 1A - Open spreadsheet

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.

Method 1 Step 1B - Make a separate sheet for reference

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.

Method 1 Step 2A - Select the column next to the table

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
Method 1 Step 2B - Use this formula to reference the other sheet

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.

Method 1 Step 2C - It even autofills for your convenience

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

='Sheet Name'!B1

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.

Method 1 Step 3A - What to do if the sheet has a name

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

Method 1 Step 3B - This is the new formula for named sheets

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 1 Step 3C - The results should be the same

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.

Method 2 Step 1 - Using another table to reference a separate spreadsheet

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.

Method 2 Step 2A - Open second spreadsheet in new tab

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

Method 2 Step 2B - We_ll be referencing total amounts payable starting with April

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

=IMPORTRANGE("URL","SheetX!YY")

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

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/██████████████████████████████████████,"Sheet1!H9")

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

Method 2 Step 3A - Use IMPORTRANGE to reference separate sheet

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.

Method 2 Step 3B - The results of IMPORTRANGE

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.

Method 2 Step 4A - April actually has two sums so you need to add the two cells together

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

=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/██████████████████████████████████████","Sheet1!H9:H35"))
Method 2 Step 4B - This is the result of the SUM and IMPORTRANGE functions used together

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

Method 2 Step 4C - Finish up the table with the given formulas

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

Andy Avery

I really enjoy helping people with their tech problems to make life easier, ​and that’s what I’ve been doing professionally for the past decade.

Recent Posts