Every person who uses spreadsheet platforms such as Google Sheets or Excel will encounter a situation where they need to reference a specific data range from another sheet in order to create a combined master view of both. This will let you consolidate information from multiple worksheets into a single one.
So, if you’re having trouble figuring out how to import data from another Google Sheet, linking Google Sheets, or how to reference cells from another sheet, you’re at the right place. In this article, we’ll answer the question, “Can Google Sheets reference another sheet?” and detail everything you need to know about importing data from another Google Sheets. Read on to learn more.
Table of Contents
Google Sheets Reference Another Sheet Overview
There are various methods you can use to reference or pull data from another Google Sheet.
- The first method is =’sheet_name’!cell_number where you can quickly and easily link data from another sheet in the same Google Sheets file.
- The second is called the IMPORTRANGE formula. This method can be used to reference data from another spreadsheet file.
To use this method, firstly, you’ll need the URL link to the Google Sheets file where you want to reference data before you can use the IMPORTRANGE formula. Highlight and copy the URL link before the last forward slash (/) in the URL at the end of the long code. Place the cursor in the destination cell on the original sheet where you want to pull data and type:
=IMPORTRANGE("URL")
Follow the quotes after the URL with a comma (,), then type the name of the sheet and the cell you want to get data from. If this seems a little confusing, don’t worry we’ll cover it in more depth in our example below.
First, there are a few things you must note:
- Using this Google Sheets reference another workbook method, you are required to specify both the sheet URL and a data range. You can’t specify another complete workbook by including only the URL.
- IMPORTRANGE expects text, not ranges or URLs, so ensure the title is within inverted commas, as shown in our example below.
- You must also confirm that IMPORTRANGE is not contained within another formula.
- Make sure that IMPORTRANGE has the appropriate permissions to work with both sheets.
- The third one is by using hyperlinks to gain quick access to another data source where the data does not have to be present in the current workbook.
When working, users often require quick access to other Google Sheets data sources but don’t want that data in their current workbook. In this case, a hyperlink is appropriate so that the other data source, or sources are a click away without taking up space in your current Google Sheet.
Fortunately, it is simple to make a hyperlinked cell in Google Sheets. Hyperlinks aren’t just for other Google Sheets. You can add a hyperlink to any URL on the internet, providing instant access to videos, articles, reports, or images, as well as advanced data created in external programs such as IBM SPSS.
Google Sheets Reference Another Sheet Step-by-Step Guide
Below is a step-by-step guide to help you link Google Sheets, reference another sheet, or import data between two sheets. Here is a copy of the spreadsheet that you can follow along with.
For this guide, I am using the following sheet where Sheet1 contains prices for 2019, and prices for 2020, 2021, and 2022 will be imported from other Google Sheets. To do this, I will link the other sheets that contain the prices to the current sheet using three different methods.
Here’s how the data is scattered:
Prices for 2020-21 are in a different sheet called 2020-21 Prices within the same file:
Prices for 2022 are in a different file altogether called 2022 Prices:
Let’s see how the different methods work!
Method 1
For this method, I will use the function
=’sheet_name’!cell_number
to link the sheet with the required data.
Step 1: Go to the empty Sheet where you want to bring the data.
Step 2: Go to the cell where you want to bring the data, in this case, Cell C3 (to bring the prices for 2020 from the other sheet), and use the formula
=’sheet_name’!cell_number
In the screenshot above, I have used the formula:
=’2020-21’!C3
to bring the first value for the 2022 column from the 2020-21 Sheet over to Sheet1. Next, I dragged down and applied the formula to the other cells as well.
Method 2
For this method, I will add a hyperlink to the data range with the prices for 2021 in the 2020-21 Prices sheet.
Step 1: Go to the cell where you want to paste the link – Right Click on your Mouse or Touchpad – Click on Insert Link
Step 2: Click on Sheets and named ranges.
Step 3: Enter the text for the hyperlink and Click on Select a range of cells to link.
Step 4: Select the cell range with the Prices for 2021 and click on OK.
This is how it looks in the sheet once pasted:
Every time this link is clicked, Google Sheets redirects to the data range with the prices for 2021.
Method 3
For this method, I will use the =IMPORTRANGE function since I want to link another Google Sheet file to this Google Sheet to import the 2022 prices.
Step 1: Copy the URL of the Spreadsheet with the 2022 prices.
Step 2: Go to the cell where you want to bring the data and use the function
=IMPORTRANGE(“spreadsheet_url”,”sheet_name!cell_range”)
That’s it! These are the three simple methods for linking Google spreadsheets.
Frequently Asked Questions
Can Google Sheets Pull Data From Another Sheet?
Fortunately, there is a way for pulling data from another Google Sheet. If you keep data in separate Google Sheets, you can go ahead and copy a range of data from one spreadsheet to another with the IMPORTRANGE function.
How Do I Automatically Pull Data From One Sheet to Another?
The easiest way to do that is to use Copy and Paste Link to automatically transfer data from one Excel or Google worksheet to another.
Firstly, Open two spreadsheets containing the same, simple dataset.
Then, In sheet 1, select a cell and type Ctrl + C / Cmd + C to copy it.
In sheet 2, right-click on the equivalent cell and go to the Paste > Link.
Conclusion
Now that we have gone through the methods and the step by step by guides on how to use them, we know how to use all the Google Sheets link to another spreadsheet methods. Through these Google Sheets reference another sheet methods, you can quickly and easily compare data from various sources without having to manually copy and paste between multiple sheets.
You can reference a single cell from another Google Sheet, or INDEX MATCH multiple cells and pull data from another sheet into your current sheet according to your requirements. Hope this article answered all your questions on how to reference a cell in another sheet.
Additional Resources
The following articles will help you add more tools to your arsenal in Google Sheets. The functions detailed in the articles below will enhance your work flow and help you become efficient, not only in linking one Google Sheet to another but in other Google Sheets tasks as well.
