Watch Video – IMPORTRANGE Google Sheets Guide
Do you want to import the data from other Google Sheets into the one in which your working currently?
You can do this easily using the IMPORTRANGE function in Google Sheets.
While it’s really easy to connect two workbooks in Excel that reside on your hard drive or a network drive, the only way to connect two Google Sheets is by using the IMPORTRANGE Function.
IMPORTRANGE Google Sheets Function – Syntax
- spreadsheet_url – this URL of the Google Sheets spreadsheet from which you want to import the data. This URL needs to be within double quotes. You can also have the URL in a cell and then use the cell reference.
- range_string – the range of cells that you want to import. Note that this needs to be in the following format: “[sheet_name!]range”. For example, if you want to import the cells A1:C10 from a sheet named Test, the format would be “Test!A1:C10”
- If you do not specify the sheet name, the formula would assume that you need to import the data from the first sheet of the Google Sheets document.
- You can also have this text in a cell and then use the cell reference as the second argument.
Let’s see an example on how to use this function in Google Sheets.
How To Use IMPORTRANGE in Google Sheets
We’ll dive into some specific examples to make it clearer. But the basics of using the IMPORTRANGE function can be broken down into just a few easy steps.
- Copy a URL from your browsers address bar that you want to import
- Type =IMPORTRANGE( into an empty cell in your blank spreadsheet and paste the URL inside quotation marks
- Type a comma, then specify the range inside quotation marks eg: “Sheet1!B5:C20 and press enter
- Click the #REF error and click Allow Access
Don’t quite understand? Let’s take a more in-depth look with some example data points.
Example 1: How To Use IMPORTRANGE in Google Sheets Using URLs or Spreadsheet Keys
Suppose you have different Google Sheets that have the test scores of students in different subjects (i.e., one sheet for Math score and one sheet for English and so on).
If you want to combine all these sheets and have the data in the same sheet, you can use the IMPORTRANGE function.
Step 1: Before you use the formula, you need to get the URL of the Google Sheets from which you want to import the data.
You can find that URL in the address bar of the browser when that Google Sheets document is open.
Alternatively, you can also copy the short key from the URL instead of the full URL (as shown below). For this tutorial, I will use the short key in the formula.
Step 2: Type the function and paste the URL or short key to add the first argument to the syntax.
Step 3: Type a comma and specify the range to import.
Below is the Google Sheets IMPORTRANGE formula that will allow you to import the names of the students and their scores into the current sheet using the short key from the above URL:
Note that the second argument of this formula is “‘Math Score’!A2:B10”. In this argument, you need to specify the sheet name as well as the range (enclosed in double quotes).
Step 4: When you enter this formula for the first time, you will see the #REF! error in the sheet. On hovering over the cell, you will see a prompt asking you to ‘Allow access’.
Click on the blue button, and it will give you the result. Note that this happens only once for a URL. Once you have allowed access, it will not ask for it again.
This can be helpful if you’re trying to get the data from multiple sheets. For example, in this case, you can get the score of all the subjects from different Google Sheets into a single sheet.
Example 2: Using the Google Spreadsheet IMPORTRANGE Function With Named Ranges
If you have to import ranges from several sources, it becomes easy to lose track of what’s what. But, you can name the ranges to make it a little easier. Just follow this process:
Step 1: In the sheet you wish to import from, highlight the range and right-click it, and navigate to More cell actions > Define named range.
Step 2: In the pop-up menu, name the range.
Step 3: In the new sheet, use the Google Sheets import range function with the named range as the range_string. In our example, it could be something like:
Step 4: Press Enter, then Click the #REF error and click Allow Access
A Few Notes and Tips for Using Google Spreadsheet IMPORTRANGE
Once the sheets are connected (as you allowed access the first time you used the formula), any update done in any of the sheets would automatically be reflected as the result of the formula. For example, if you edit the score of a student, it will automatically change in the sheet which has the formula.
When using the IMPORT RANGE formula, make sure enough empty cells are available to accommodate the result of the formula. If you have some data in a cell that overlaps with the cell needed for the formula result, the formula will return an error. However, it helps in identifying the error by letting you know the issue when you hover over the cell.
If you’re importing the data from many sheets, it may get a bit confusing. It’s a good practice to create named ranges in the source sheet and then use the named range. For example, instead of ‘Math Score’!A2:B10, you can use ‘Math Score’!Data
If you expect that data would be added in the source sheet, instead of pulling a specific range, pull the entire column. For example, instead of ‘Math Score’!A2:B10, use ‘Math Score’!A:B. This will pull the data from entire A and B columns. Now if you add more data to the source sheet, it will automatically be updated in the destination sheet.
Here some documentation by Google on the IMPORTRANGE function.
IMPORTRANGE Function Google Sheets FAQ
How Do I Use IMPORTRANGE in Google Sheets?
- Find the URL from your browsers address bar to import
- Enter =IMPORTRANGE( into an empty cell and paste the URL inside quote marks
- Type a comma, then specify the range inside quotation marks eg: “Sheet2!B6:C18 and press enter
- Click the #REF error and click Allow Access
What Is IMPORTRANGE in Google Sheets?
IMPORTRANGE is a function that allows you to bring data over from one spreadsheet to another without having to manually enter it. It is better than copy pasting as it automatically updates.
Does IMPORTRANGE Automatically Update Google Sheets?
Yes, any changes made to the initial sheet will change in the imported data too.
Is There a Limit on IMPORTRANGE in Google Sheets?
Yes, there can be a maximum of 50 cross-workbook reference formulas.
How Do I Filter IMPORTRANGE in Google Sheets?
You should use the QUERY function to filter data from imported data.
How Do I Use Multiple IMPORTRANGES in Google Sheets?
Follow the standard procedure for importing a range, but make sure each time you use IMPORTRANGE there is enough space in the sheet to display the data.
Can You IMPORTRANGE With Formatting?
No, you can only import the data itself.
If You Found This IMPORTRANGE Google Sheets Guide Useful, You May Also Like the Following Tutorials:
- Using Query Function in Google Sheets.
- How to Use IMPORTDATA function in Google Sheets.
- Using IMPORTFEED in Google Sheets to Fetch Feed from URL.
- The Ultimate Guide to Google Sheets VLOOKUP Function.
- Using IFERROR Function in Google Sheets.
- How to VLOOKUP in Another Sheet in Google Sheets
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.
3 thoughts on “An Easy to Follow IMPORTRANGE Google Sheets Guide for 2023”
I need to be able to use the IMPORTRANGE function to pull data from one spreadsheet to another, but place that data in a row below where the IMPORTRANGE function lives. The rows of data being pulled from another spreadsheet are moved from one sheet to another within the new spreadsheet based on an entry in a column using code in script editor. Everything works well until the row with the IMPORTRANGE function is moved – then the rest of the data below it disappears!
i have successfully created a master spreadsheet, which consists of multiple tabs of import range spreadsheets. i need to be able to edit the master though, to make updates.
also, i need to be notified when the individual imported sheets are edited.
is there any way to receive notifications on the master (which consists of 40 tabs of import range sheets)?
is there any way i can edit the master.
appreciate any help you can offer.
Pull the below data from ‘Teacher Data’ File where condition is ‘Overall Performance is Below 50%’ by using importrange function?
Comments are closed.