How To Reference A Cell From Another Sheet In Google Sheets

How to reference a cell from another cell in Google Sheets
Reading Time: 4 minutes

Quick Guide
How to reference a cell from another sheet in Google Sheets:
Step 1: Open the sheet to import the data
Step 2: Select the cell to display the data
Step 3: Type in the formula =sheet_name!cell, eg, Sheet1!A2

When you create a Google Sheets file, you can add any number of sheets. You can also reference a cell from another sheet in Google Sheets when creating charts in the main spreadsheet. 

Referencing a cell from one sheet to another allows you to access and move the data in a cell from another sheet. This is useful if you want to create a formula that uses information from two different sources or if you want to use the same data in more than one place in your spreadsheet.

Why reference a cell from another sheet in Google Sheets?

Multiple data sheets are accessible from the same spreadsheet. You can also reference a cell on another sheet in a different spreadsheet file. It is also similar to the use of Indirect reference in Google sheets.

Say you have a budget spreadsheet that includes monthly income and expenses. When creating charts, referencing cells from other sheets is less difficult than typing them in manually. 

This article will show you how to do that by referencing another sheet with simple examples.

How to reference a cell from another sheet in Google Sheets

Google Sheets is a powerful tool that can be used to make your life easier. If you are looking to get some data from another sheet, this guide will show you how to do it.

To reference a cell from another sheet in Google Sheets, we’d use the following generic syntax:

=Sheet_name!cell

For example, to reference cell A1 from a sheet named Employees, the syntax would be

=Employees!A1.

If the sheet name has spaces or special characters, we enclose the name with inverted commas. For example: =’Employees List’!A2:A100

We can, of course, use the function to do more complicated tasks. So that is what we are going to learn here.

Consider the following data set containing the order history of different products, types, quantities, and delivery statuses. But the price details are missing, which are in another sheet.

Sample data to reference a cell from another sheet in Google Sheets

Shown below are the price details in another sheet.

Sample data to reference

We can easily copy this information to the first sheet by referencing the cell.

Here’s how to do it.

Step 1: Go to the sheet you want to pull data from and take note of the cell that contains the data and the sheet name. In this case, the cell starts from B2, and the data is in Sheet2.

Data in the second sheet

Step 2: Click on the cell to which you want to copy the data and enter the formula. The formula is =Sheet2!B2. 

Entering the formula

Step 3: Press Enter. And you will see the product price on the cell you want it to appear.

The referenced cell value from another sheet

Step 4: To fetch the price for the rest of the products, drag the cell containing the formula to the last cell you want to apply the formula.

Below is the result.

The formula applied across all the cells

Reference a cell from another sheet using the FILTER function

The FILTER function is another method to reference a cell from another sheet in Google Sheets. This FILTER function also uses the LEN function to pull data from multiple sheets. 

For Instance, if you are trying to pull the destinations of products delivered from Sheet3 to Sheet1, this formula can come in very handy. 

Sample data

Below is a simple guide on how to do this; 

Step 1: Enter the formula in the column you want the data to appear.

The formula is

={ FILTER (Sheet3!B2:B9, LEN(Sheet3!B2:B9))}

Where the FILTER + LEN functions pull data from ‘Sheet3’ and populate it in ‘Sheet1’, this function extracts the data from ‘Column B’ in ‘Sheet3’ and places them in your desired cell range.

Formula of the FILTER function to reference another sheet

Step 2: Press Enter. All the list of destinations will appear on the sheet.

Referenced values from another sheet

We need to note that referencing cell from another sheet is not the same as copying the data; it’s simply displaying the data from the referenced sheet. So, making changes to or deleting the data in the parent sheet will be reflected in the other sheets as well.

Conclusion

Referencing a cell from another sheet in Google Sheets is a simple but useful function. It lets us easily access data without having to switch between different sheets.

We can also reference a cell from a different spreadsheet file. For that we use the IMPORTRANGE function.

See Also

You might find our other articles helpful if you are interested in learning more about Google Sheets. We provide helpful tutorial guides on Google Sheets to help you organize your data.

Here are some other articles that you may find helpful:

How to Use The Querry Function With IMPORTRANGE In Google Sheets

https://blog.tryamigo.com/vlookup-from-another-sheet-in-google-sheets/

How To Merge Duplicates In Google Sheets