Reference Cell in Another Sheet in Google Sheets (3 Ways)

Today, we will look at all the ways we can reference cell in another sheet in Google Sheets. We will first touch on the fundamentals of referencing cells then move onto some scenarios where we extract data from another worksheet, both from the same and different spreadsheet.

3 Ways to Reference Cell in Another Sheet in Google Sheets

1. The Basic Way

We start with the simplest way to reference cells in another worksheet in Google Sheets to understand the fundamentals.

Like everything in Google Sheets, any task or function follows a specific syntax, and referencing cells is no different.

To reference cell from another worksheet, we have to follow this syntax:


To show the process, we will use the following simple worksheets. The first worksheet, Main, is where we will show the cell reference (data extraction) from the other worksheet. The second worksheet, Shop 1, contains the data that is to be extracted.

the main worksheet for reference cell in another sheet in google sheets

Main Worksheet

shop 1 worksheet

Shop 1 Worksheet

Reference a Single Cell from Another Worksheet

Let’s say we want to reference the first name of the table in the Shop 1 worksheet. The idea is to replace the placeholders in the syntax accordingly.

The first name in the Shop 1 worksheet is in cell B3.

we will extract the data in cell B3

So, our formula in the main worksheet will be:

='Shop 1'!B3

reference cell in another sheet in google sheets

While you can manually write the sheet_name and cell-reference, you can also type “=” and navigate to the other worksheet to select your desired cell to reference it. Press ENTER to complete.

reference cell in another sheet in google sheets animated

Reference a Range of Cells (with formula)

As convenient as it would be, selecting multiple cells this way does not display the values selected.

basic formula can only display one value animated

To display all the values, we need the help of other functions that we will see in Section 2 of this article, or display it as an array. But you should know that the values are indeed extracted and saved in memory. Simply enclose this formula within curly braces {}:

={'Shop 1'!C3:C12}

taking help of an array to reference cell in another sheet in google sheets

We can also use a formula to utilize them. In this case, we are using SUM to calculate the total Sales from the other worksheet.

The formula:

=SUM('Shop 1'!C3:C12)

using sum with cell reference from another sheet in google sheets

With that, we have covered the fundamentals of how to reference a cell in another sheet in Google Sheets. The rest of the article will focus on using all that we have learned in different scenarios.

Read More: Indirect Range in Google Sheets (3 Easy Ways)

2. Reference Cell in Another Sheet with Criteria

More often than not, just referencing cells in another worksheet just doesn’t cut it. For complex or advanced spreadsheets, some conditions or criteria might likely be involved.

One such way to reference cells and extract data from another worksheet in Google Sheets is by using the FILTER function.

FILTER function syntax:

FILTER(range, condition1, [condition2, ...])

filter syntax in google sheets

From a Single Sheet

Let’s first extract data from our Shop 1 worksheet using FILTER. The function allows us to extract a range of cells instead of just a row, column, or just a cell.

To begin, type in the function and apply the range.

starting the filter function with the range

Next, apply the condition/criterion for the extraction. In our case, we want all the entries that have sales of more than $500. We first select the column that we will apply the condition on then input the condition (>500)

applying condition to the filter function

Press ENTER to see the results.

using filter to reference cell in another sheet in google sheets

From Multiple Sheets

We have to get a bit creative if we want to extract data from multiple worksheets since FILTER can only hold one range (one worksheet).

For multiple worksheets we must utilize multiple FILTER functions, we do that by putting them in an array.

To show this example, we have added two more Shop worksheets with a similar layout.

more worksheets added

The idea is that each shop will have its own FILTER function presented together as an array.

Our formula:

={FILTER('Shop 1'!B3:C12,'Shop 1'!C3:C12>500);FILTER('Shop 2'!B3:C12,'Shop 2'!C3:C12>500);FILTER('Shop 3'!B3:C12,'Shop 3'!C3:C12>500)}

using filter to reference cells from multiple sheets in google sheets

Since the formula is an array, it is enclosed within curly braces {} and each FILTER function is separated by semicolons (;).

You should also know that a very big advantage of this formula, or the FILTER function in general, is that it is dynamic. Meaning that if the source data is changed, the changes will also be reflected in the extracted data.

But you also have to be careful about putting the wrong type of data in the source since it may result in an error.

Read More: Reference Another Sheet in Google Sheets (4 Easy Ways)

Similar Readings

3. Reference Cell in Another Sheet in another Workbook

For our final example, we will go beyond the scope of our current spreadsheet and to another one.

Our task is the same as we have seen in our first section, but this time, we have to use the IMPORTRANGE function to reference another spreadsheet.

IMPORTRANGE function syntax:

IMPORTRANGE(spreadsheet_url, range_string)

Three things to note about IMPORTRANGE:

I. You can use either the whole spreadsheet URL or just the spreadsheet Key for the spreadsheet_url field.

Whole Spreadsheet URL

Whole Spreadsheet URL

Spreadsheet Key

Spreadsheet Key

II. The function takes only string values. This means that the field values in the function must be enclosed in quotation marks (“”).

III. The function imports all values within the range.

Our formula to extract the entire data table from the worksheet in another spreadsheet:

=IMPORTRANGE("","Shop 1!B3:C12")

using importrange to reference cell from another workbook in google sheets

Like FILTER, IMPORTRANGE is also dynamic, meaning if the source value changes, the value in the main sheet will also change.

Read More: Reference Another Workbook in Google Sheets (Step-by-Step)

Final Words

That concludes all the ways we can reference a cell in another sheet in Google Sheets. We hope that the methods we discussed come in handy in your daily tasks.

Feel free to leave any queries or advice you might have for us in the comments section below.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply