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.
Table of Contents ToggleTo 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
Master List Worksheet
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. 
Step 2: Move to Office 1 worksheet and select the first name there. 
Step 3: Press ENTER.
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:
='worksheet-name'!cell-referenceRange 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. 
Press ENTER. 
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 <>.
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)
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)
Formula Breakdown:
This formula functions similarly to = 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 >:
=
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.
Adding a new Employee Name
Changing the worksheet name also changes its name in the formula
Similar Readings
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”)
Source worksheet: Office 1
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)
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.
The IMPORTRANGE syntax:
IMPORTRANGE(spreadsheet_url, range_string)
We have created a separate workbook containing the Office 4 worksheet
We will be extracting all the names from the Office 4 spreadsheet here in the New Employees worksheet in our main workbook.
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
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("https://docs.google.com/spreadsheets/d/1M0QaYmxGruuvAlRiS5wlntXSltWEByyV1Q2nbrRO1NE/edit#gid=0","Office 4!B3:B12")
Alternatively, we can also use the Spreadsheet Key of the spreadsheet URL in the formula to give the same results.
New formula:
=IMPORTRANGE("1M0QaYmxGruuvAlRiS5wlntXSltWEByyV1Q2nbrRO1NE","Office 4!B3:B12")
If you want to learn more, here’s an in-depth guide on How to Reference Another Workbook in Google Sheets.
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.
Mehrab Imtiaz is a Technical Writer for officewheel.com 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.
