Reference Another Sheet in Google Sheets (4 Easy Ways)

Today, we look at how to reference another sheet in Google Sheets. While it does sound easy (and it is), it’s common to see users of all levels of spreadsheet expertise mess up.

This article stands as a trusty guide for those situations. Let’s get started.

Reference Another Sheet in the Same Spreadsheet in Google Sheets

1. Basic Cell Reference to Another Sheet

To show our processes, we have created this workbook containing worksheets of three separate workplaces: Office 1, Office 2, and Office 3. And a fourth worksheet called Master List which we will be populating.

Office Worksheet - reference another sheet in google sheets

Office Worksheet

Master List Worksheet

Master List Worksheet

a. Single Cell Reference from Another Sheet

Let’s start simple. We will be extracting (referencing) the name of the first employee from Office 1 worksheet to cell B3 of the Master List worksheet.

Step 1: Input equals-to (=) in cell B3.

opening formula for reference another sheet in google sheets

Step 2: Move to Office 1 worksheet and select the first name there.

selecting reference cell in another sheet

Step 3: Press ENTER.

='Office 1'!B3

basic reference another sheet in google sheets

You can also type in the name of the worksheet you are referring to within single quotes (‘’) and the cell number of that worksheet separated by an exclamation (!).

Thus, our syntax for referencing another worksheet in Google Sheets is:


b. Range Reference from Another Sheet in Google Sheets

Range references work the same way. Instead of typing in a single cell value, we input a range.

Continuing from Step 2 from our previous section, we click and drag down the column of names to select them.

selecting range from another worksheet

Press ENTER.

not all values are showing

But this doesn’t show all the names, does it?

The data has been extracted but it is confined to one cell. To display all the names we have to present the data as an array, thus we have to enclose the formula within curly braces {}.

={'Office 1'!B3:B12}

output array for reference another sheet in google sheets

That concludes the fundamentals of referencing data from another sheet in Google Sheets. From this point onward, we will be looking at the iterations of what we have learned, up to referencing another spreadsheet entirely.

Read More: Reference Cell in Another Sheet in Google Sheets (3 Ways)

2. Import Data From Multiple Sheets into One Column

A situation may arise where you may need to extract data from ranges that belong to multiple worksheets. Similar to our All Employees column in our Master List worksheet, where we want to list the names of all employees from the three offices.

To help us with that, we will utilize the FILTER function.

To extract names from Office 1 we use the following formula:

=FILTER('Office 1'!B3:B12,LEN('Office 1'!B3:B12)>0)

using FILTER function to reference range in another sheet

Formula Breakdown:

  • ‘Office 1’!B3:B12: Our range of names in the Office 1 worksheet. You can update the range to ‘Office 1’!B3:B to extract the column dynamically.
  • LEN(‘Office 1’!B3:B12)>0: Our condition for extraction. It checks whether the value of the cell is greater than 0, returns TRUE if it is.

This formula functions similarly to ={'Office 1'!B3:B12} of the previous section, up to the point of outputting the data as an array.

Formula for Office 2:

=FILTER('Office 2'!B3:B,LEN('Office 2'!B3:B)>0)

Formula for Office 3:

=FILTER('Office 3'!B3:B,LEN('Office 3'!B3:B)>0)

All we need to do now is combine the different sections with a semicolon (;) separator and enclose it in curly braces {}:

={FILTER('Office 1'!B3:B,LEN('Office 1'!B3:B)>0);FILTER('Office 2'!B3:B,LEN('Office 2'!B3:B)>0);FILTER('Office 3'!B3:B,LEN('Office 3'!B3:B)>0)}

reference multiple other sheets in google sheets

One other advantage of using this method is that it is dynamic. Not only for new data added to each of these Office worksheets but also for the worksheet names. So even if you change the name of the worksheet, it will be reflected in the formula.

dynamically adding new items to the list animated

Adding a new Employee Name

Changing the worksheet name also changes its name in the formula

Changing the worksheet name also changes its name in the formula

Similar Readings

3. Import Data With Criteria

To present this method, we will again use the FILTER function, but we will be changing up the scenario.

This time, we will extract names according to the Departments of the Employees.

The modification to the formula is simple:

=FILTER(‘Office 1′!B3:B,’Office 1’!C3:C=”Sales”)
Office 1 worksheet

Source worksheet: Office 1

reference another sheet in google sheets with condition

You can do it for all the departments. The Department name condition can be referred to the header cells.

=FILTER('Office 1'!$B$3:$B,'Office 1'!$C$3:$C=C$2)

data extracted according to department name

Reference Another Sheet From a Different Spreadsheet in Google Sheets

Google Sheets does not restrict us to only reference worksheets from the same spreadsheet. We can also use references from a separate spreadsheet thanks to the IMPORTRANGE function.


IMPORTRANGE(spreadsheet_url, range_string)


We have created a separate workbook containing the Office 4 worksheet

Office 4 spreadsheet

We will be extracting all the names from the Office 4 spreadsheet here in the New Employees worksheet in our main workbook.

New Employees worksheet

Let’s go through the process, step-by-step.

Step 1: Open the function in cell B3, =IMPORTRANGE(

Step 2: Copy the URL of the other workbook and paste it within the function within quotation marks (“”). Press comma (,) to move to the next part

copying the spreadsheet URL of Office 4

Step 3: Input the worksheet name and the range of cells in the following format:

“Office 4!B3:B12”

Step 4: Close parentheses and press ENTER.

=IMPORTRANGE("","Office 4!B3:B12")

reference another spreadsheet in google sheets using IMPORTRANGE

Alternatively, we can also use the Spreadsheet Key of the spreadsheet URL in the formula to give the same results.

Spreadsheet Key

Spreadsheet Key

New formula:

=IMPORTRANGE("1M0QaYmxGruuvAlRiS5wlntXSltWEByyV1Q2nbrRO1NE","Office 4!B3:B12")

reference another spreadsheet in google sheets using spreadsheet key

If you want to learn more, here’s an in-depth guide on How to Reference Another Workbook in Google Sheets.

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

Final Words

The ability to reference another sheet in Google Sheets is very easy. The only difference you may face, when compared to applications like Excel, is when we reference a different workbook or spreadsheet entirely with its IMPORTRANGE function.

We hope that the methods we have presented in this article help to solve any issues you may face or to just simply understand how referencing works in Google Sheets.

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

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