Reference Another Tab in Google Sheets (2 Examples)

Today, we will look at a couple of different scenarios where we reference another tab in Google Sheets spreadsheets. Knowing how to reference another tab is a fundamental skill to have to use any spreadsheet application.

Let’s get started.

Table of Contents

2 Instances Where We Reference Another Tab in Google Sheets

1. Reference Another Tab in the Same Spreadsheet in Google Sheets

To reference another tab in Google Sheets we simply have to utilize the following format:

sheet_name!cell_reference

For example, we have the following spreadsheet containing four worksheets: Main, Jan Data, Feb Data, and Mar Data.

The month worksheets all follow the same format and contain the Cost data for their respective months.

What we want to do is, retrieve the Total Cost data of each month and put them in the Main worksheet of the current spreadsheet.

As we can see from the following image, the Total Cost data for each month is located in cell C10 of each month worksheet.

So, to extract the January Data to the Main worksheet, first type “=” in the target cell and use the format we have:

='Jan Data'!C10

Where Jan Data is the sheet_name and C10 is the cell_reference. Press ENTER after you are done.

Now for the other two months:

Cell Range from Another Tab (Formula)

A more practical use of referencing cells from another tab is to use it in functions.

Let’s say that we want to use the SUM function to directly calculate the monthly expenses right here in the Main worksheet.

We simply type “=SUM” and move to the other tab to select the range for our function. We get the formula:

=SUM('Jan Data'!C5:C9)

Using Named Range from Another Tab

Another way to utilize cell range is to use Named Ranges in Google Sheets. This enables you to give a range of cells a unique name within the current spreadsheet to be utilized in formulas.

That said, to sum the expenses from the Feb Data tab by using named ranges, we use the formula:

=SUM(‘Feb Data’!FebruaryExpense)

=SUM(FebruaryExpense)

Beyond the methods we have just discussed, there are others, like using INDIRECT to reference cells in another tab which is complex yet helps overcome a lot of limitations of traditional referencing. Or use array format to display the values in a cell range from another tab.

Using curly braces {} to display the range of values from another tab

2. Reference Another Tab in a Different Spreadsheet in Google Sheets

Before we get to referencing another tab in another spreadsheet, we must understand the core function that we will be utilizing to do so. That is the IMPORTRANGE function.

The IMPORTRANGE function syntax:

IMPORTRANGE(spreadsheet_url, range_string)

Using this function is fairly simple. The range_string field will be covered by the tab reference format (sheet_name!cell_reference). This works with both a single cell reference or a range of cells

What’s new here is the spreadsheet_url. This can be both the whole URL of the other spreadsheet, or the spreadsheet Key of the other spreadsheet.

Spreadsheet URL

Spreadsheet Key

With both our values for the fields taken, we can now apply them to our formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1zWAd940NR1iJG4tJfec2l4p-XmyZKQx2dLBUIyJFaTA/edit#gid=1946913002","Mar Data!C10")

Here we have imported March Total Cost data from the Mar Data tab that was in another spreadsheet with the help of IMPORTRANGE.

Final Words

That concludes all the ways to reference another tab in Google Sheets for both the same spreadsheet or a different one. Please feel free to leave any queries or advice you might have for us in the comments section below.