How to Reference Data from Another Sheet in Google Sheets – Ultimate Guide [2024]

With its remarkable functions and tools, Google Sheets has proven to be a versatile tool for managing massive datasets. Users can easily reference data across multiple spreadsheets using built-in tools such as Named Ranges and functions such as the IMPORTRANGE.

Do you also find yourself juggling between sheets to reference data? 

Then, you might be aware of the challenges that come across. And sometimes, you may feel overwhelmed while pulling the data from an entirely different workbook.

To simplify your life, we have compiled a list of methods to reference data from other sheets in Google Sheets.

Whether you are a beginner or looking to enhance your skills, understanding and trying the following techniques can be a boon. So, make sure to read the article till the end.

How to reference data from another sheet in Google Sheets

It includes two methods depending on whether you wish to reference data from the same or a different workbook.

When we say a “workbook”, it generally refers to a new or separate Google Sheets.

Note that Google Sheets is a cloud-based spreadsheet program storing user’s documents online. It means that they can access and connect different workbooks using a URL.

Let us start by learning to reference data from the same workbook or Google Sheets.

Reference Data from different spreadsheets within the same workbook

This approach allows you to access data from different spreadsheets within the same workbook or Google Sheets.

For example, you are working on Sheet1 and want to pull the data from Sheet2 in the following workbook.

There are four ways to do this:

• Using Cell References
• Using Named ranges
• Using Array Formulas
• Using Indirect Function

 Let’s discuss them one by one.

Method #1 – Using Cell References

This is used when you want to pull data from one sheet and populate it in another. To understand it more clearly, let us see an example.

We have employee data, including their name, age, and city. The table shows that the state is missing in the last column.

We have states available in the second sheet named “Sheet2”.

Let us use the cell references and import the state from column B of Sheet2 to the main employee data table of Sheet1.

We will use the following formula,

=(SheetName!CellRef)

Here, the “SheetName” needs to be replaced with the spreadsheet name from where you wish to reference the data, and “CellRef” needs to be replaced with the cell reference holding the data you want to pull.

Note that both arguments are separated by the “!” symbol, without which the formula will return an error.

Now, let’s begin.

• Go to the Sheet1
• Click on the cell D2
• Type “=” to insert a formula in the cell
• Now, type “Sheet2” so that our formula will refer to the second spreadsheet of the workbook
• Put “!“
• Provide the cell reference as “B2“
• Press the “Enter” key
  

Here’s how the final formula looks,

=(Sheet2!B2)

As we learned, the above formula includes two different sections: SheetName and CellRef. The “!” symbol separates both of them.

To apply this formula to the cells of column D. Click on the cell D2 and hover to the lower-rightmost corner. Now, double-click on the Fill Handle, and the formula will get populated into the rest of the cells.

Method #2 – Using Named Ranges

As the name suggests, a specific range of cells is assigned a name used in the formulas to pull the data.

Let us consider the below example to understand it better.

The first sheet, named “Sheet1,” includes a table with employee details such as their name, age, city, and working hours on weekdays and weekends.

Then, we have the second spreadsheet named “Sheet2,” where all employees’ total working hours on weekdays and weekends need to be calculated.

Our task is to sum individual employees’ working hours on weekdays and weekends.

Let’s begin by naming the cell range that holds working hours on weekdays by individual employees.

• Go to the Sheet1
• Select all the cells from E2 to E8
• Hover to the main menu
• Click on the “Data” tab
• Select “Named Ranges” from the popup
  
• A new dialog box will be opened on the right side of your screen
  
• Name your range of cells without any spaces, say “WeekdaysHours“
  (Avoid using space while naming the cell range using the Named ranges tool)
• Click on the “Done” button in the green

Your named range will appear as follows. To edit it in the future, you can click on the pencil icon.

Make sure to follow the above steps to name the second range of cells F2 to F8. I have called it “WeekendHours“.

Now, let us find the sum of weekday working hours in Sheet2.

• Go to the Sheet2
• Click on the cell B2
• Type “=sum”
• Select the first option from the popup or press “Tab” key
• Type “WeekdaysHours“
• Complete the bracket using “)“
• Press “Enter” on your keyboard
  

Our final formula should look as follows,

=SUM(WeekdaysHours)

To find the sum of weekend working hours, you need to use the following formula.

=SUM(WeekendsHours)

Note that you must provide the cell references or range entirely for the SUM function to work in Google Sheets. But when combined with the Named ranges, you don’t need to enter the cell references or range manually.

In other words, the Named Ranges simplify the formulas, thereby making them more readable by maintaining a clear connection between different sheets.

Method #3 – Using the ARRAYFORMULA

The Array formulas in Google Sheets are handy to calculate the entire range of data at once without dragging the formula down.

The syntax for this function is as follows,

=ARRAYFORMULA(array_formula)

Here, the argument “array_formula” must be replaced with the logical expression, cell range, or function that generates results more significant than one cell.

Let us consider the example we used in the previous method.

Our task remains the same: calculating the working hours on weekdays and weekends.

Here are the steps,

• Go to the “Sheet2“
• Select the cell B2
• Type “=arrayformula”
• Select the first option from the popup or press “Tab” key
• Now, let’s type the “sum” in the place of the array_formula argument
  
  (As the SUM is a function in Google Sheets, you will see a popup as shown in the above image)
• Choose the first option from the popup
• Next, click on the “Sheet1” as shown below
  
• Select cell range “E2:E8“
• Complete the parentheses for the SUM function using “)“
• Complete the parentheses for the ARRAYFORMULA function using “)“
• Press “Enter” on your keyboard
  

Here’s how the final formula should look,

=ARRAYFORMULA(SUM(Sheet1!E2:E8))

You can follow the above steps to find the sum of the working hours on the weekends using the following formula,

=ARRAYFORMULA(SUM(Sheet1!F2:F8))

Method #4 – Using the INDIRECT Function

The INDIRECT function in Google Sheets is used to create dynamic cell references. It allows users to reference data indirectly from the same or different spreadsheets within the same workbook.

The General Syntax of the INDIRECT function is as follows,

=INDIRECT(cell_reference_as_string, [is_A1_notation])

• The first argument, named “cell_reference_as_string,” must be replaced with the cell range from where you wish to reference the data.
• On the other hand, the second argument, named “is_A1_notation,” is optional and considered TRUE if kept blank.

Now, let’s use this function to reference data in Google Sheets.

Consider the following example where we have employee data in the first spreadsheet named “Sheet1“.

Our task is to get the state for each individual from Sheet2, which holds the employee’s name along with their state name.

Here are the steps,

• Go to the “Sheet1“
• Click on the cell “D2“
• Type “=indirect”
• Select the first option from the popup or press “Tab” key
• Replace the “cell_reference_as_string” argument with the “Sheet2!B2:B8”
  (Make sure to use the double quotation marks; otherwise, the formula will return an error at the end)
• Complete the bracket using “)” on your keyboard
• Press the “Enter” key
  

Our final formula after following the above steps should look like this,

=INDIRECT(Sheet2!B2:B8)

• Sheet2 is the name of the reference sheet
• B2:B8 is the array of cell references from where the data is being extracted

Note that the formula separates the sheet name and cell reference by the “!” symbol. Without this symbol, the function won’t work correctly.

Reference data from another workbook (Google Sheets)

In this method, you access data from different workbooks. For example, you are working on a Google Sheet and wish to import data from another Google Sheet.

This can be achieved using the IMPORTRANGE Function.

Explained: The IMPORTRANGE Function in Google Sheets

The IMPORTRANGE is a function to reference data from different Google Sheets. Here’s the general syntax,

=IMPORTRANGE(spreadsheet_url, range_string)

You need to carefully deal with both of the arguments of the IMPORTRANGE function,

• “spreadsheet_url” – It represents the URL of the Google Sheets from where you wish to import the data. Make sure to use the editable link; otherwise, the function will return an error.
• “range_string” – Here, you can define the exact cell range you wish to import from the primary Google Sheets.

SIDENOTE: Remember to use double quotation marks while putting the URL and cell range within the IMPORTRANGE formula.

Consider the following Google Sheets, which contains a single spreadsheet with a table for employee details.

As you may have noticed, the state needs to be added in the last column of the table.

Here’s another Google Sheet with the exact employee details, but this time, the table includes employee names and their respective state only.

Let’s import the above states to the first Google Sheets.

• Go to the second (source) Google Sheet
• Click on the “Share” button in the upper-rightmost corner of the screen
  
• A new dialog box will appear as shown below,
  
• Below the General access, click on the dropdown named “Restricted“
  
• Select “Anyone with the link” from the popup
  
• Copy the link using the “Copy link” button
  
• Now, go to the first (destination) Google Sheet
  (You can create a new Google Sheet by clicking here)
• Click on the desired cell
• Type “=importrange”
• Select the first option from the popup or press “Tab” key
• Replace the spreadsheet_url argument with the link you copied earlier
  (Make sure to use double quotation marks; otherwise, the function won’t work as expected)
• Press “,” to move to the next argument
• Now, type “Sheet2!B2:B8” in the place of range_string argument
  (Make sure to use the double quotation marks)
• Complete the bracket using “)“
• Press the “Enter” on your keyboard
  

The formula should look as follows,

=IMPORTRANGE(“docs.google.com/spreadsheets/d/1gKvnSjtmmYJAoSn3i3nkAsI6afkyl7wwyYFYymxpp0Y”, “Sheet2!B2:B8”)

As the IMPORTRANGE is an array formula, you must keep the column or entire table range blank before using the formula in the destination Google Sheets.

Additionally, the function might not work correctly if multiple Google accounts are logged in or the spreadsheet has Restricted access.

FAQs

Q. What does reference mean in Google Sheets?

In Google Sheets, reference generally refers to a cell location used in formulas to perform different calculations.

The cell references help Google Sheets formulas dynamically update their results based on the changes made to the source data.

Q. Can you reference data from another spreadsheet? 

Yes, of course! You can reference data from another spreadsheet using the IMPORTRANGE function in Google Sheets.

Here’s the general syntax for the formula,

=IMPORTRANGE(spreadsheet_url, range_string)

It is pretty straightforward to use. We have explained all the steps to import the data from one Google Sheet to another in the above section of this article.

Q. What are the two types of references?

The two types of references are Relative and Absolute references.

• Relative references – It changes based on the formula’s position
• Absolute references – It remains fixed, pointing to the same cell, no matter where the formula is placed

Conclusion

I hope you learned all the bells and whistles of referencing data from another sheet in Google Sheets.

The steps discussed in this article are pretty straightforward.

If you are stuck somewhere or have any particular issue, let us know in the comment section. Our team will try to answer your questions as quickly as possible.

Linking data between sheets helps you organize your spreadsheet, making it more efficient.

Next time you encounter a situation where you would reference data from the same or another workbook, make sure to use the easy-to-follow techniques discussed above.

Check out our blog for more tips and tricks to use Google Sheets like a pro.