How to use the IMPORTRANGE function in Google Sheets

If you work with Google Sheets often enough, you'll inevitably need to get data from one spreadsheet to another.

The IMPORTRANGE function in Google Sheets will help us link data from different spreadsheets, to quickly search for data, and retrieve data according to the display requirements in the function. Thus, you can use the IMPORTRANGE function to extract data and quickly link data in Google Sheets data tables.

Using the IMPORTRANGE function in Google Sheets is also simple and can be combined with some functions in Google Sheets . The article below will guide you to use the IMPORTRANGE function in Google Sheets.

IMPORTRANGE function structure in Google Sheets

Google Sheets IMPORTRANGE function structure

=IMPORTRANGE(“spreadsheet_url”; “range_string”)

In there:

• Spreadsheet key: is a long string of numbers and letters in the URL for a particular spreadsheet.
• Spreadsheet url: is the address link of a specific spreadsheet file.
• Range string: is the exact name of the spreadsheet from which to get the data followed by '!' and the range of cells from which to get the data.

We will take an example with the Grade 10-2019 Benchmark data table, with the requirement to extract data from cell B2 to cell C29 in the Page 1 spreadsheet.

You open a completely new spreadsheet in Google Sheets to get the data of the spreadsheet you need. First, you need to copy the link of the document Grade 10-2019 Standard Scores .

Next, in the new spreadsheet interface, we enter the function formula as below and press Enter.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/19AGre5-Fvin6Z4D0bW6e4iq6yYX13kar0EIvXAVh-VQ/edit#gid=887314864"; "Page1!B2:C29")

You will immediately see the #REF! message . You need to click on this message and click Allow Access to allow the IMPORTRANGE function to access data from another worksheet.

The data results from the worksheet you selected are displayed in another worksheet as shown below.

How to use the IMPORTRANGE function in Google Sheets

1. With just two arguments, using the IMPORTRANGE function is usually quite simple. Let's say you have a spreadsheet and you want to import the table into a new spreadsheet.

2. Click on the URL in the address bar at the top of your browser and copy it. Alternatively, you can just copy the spreadsheet key from within the URL.

3. In the new spreadsheet, enter "=IMPORTRANGE(" - without the quotes.

4. Paste the URL and add double quotes (").

5. Type a comma, add double quotes ("), and enter the range of cells you want to include. It should look like this: "Sheet1!B1:C6". Here it specifies that the example wants the spreadsheet named "Sheet1" and wants cells B1 through C6.

6. Add a closing parenthesis and press Enter.

7. The complete function will look like this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Zoq0M0RG-RLYZ9HjOf01ff9eSPIYY3s/edit#gid=1027643093", "Sheet2!A1:C12")

8. However, you may notice that the data is not imported - there is a #REF! error in the replacement cell. Click on this cell and you will see a message that you need to connect the spreadsheets. Click "Allow access" and then the data will appear. You will only need to do this once for each spreadsheet you import data from.

How to use the IMPORTRANGE function with the QUERY function

When you combine the IMPORTRANGE function with the QUERY function, you can get the exact data according to the conditions you need to extract.

We will select the data from cell B2 to cell C29 in the spreadsheet Page 1 and only take column NV1. Enter the formula as below and press Enter.

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/19AGre5-Fvin6Z4D0bW6e4iq6yYX13kar0EIvXAVh-VQ/edit#gid=576960759";"Page1!B2:C29");"SELECT Col2") 

The result will only show the NV1 score column.

Or you can also get the score value of schools with NV1 score <40, for example.

We enter the function formula as below and press Enter.

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/19AGre5-Fvin6Z4D0bW6e4iq6yYX13kar0EIvXAVh-VQ/edit#gid=576960759";"Page1!B2:C29");"Sect Col1 where Col2 <40")>

The results will display the names of schools with NV1 scores lower than 40 points.

Example of how to use the IMPORTRANGE function in Google Sheets

Use IMPORTRANGE with URL or Spreadsheet Keys

Let's say you have multiple Google Sheets spreadsheets that contain students' test scores in different subjects (for example, one sheet for Math scores and one sheet for English scores, etc.).

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 using the formula, you need to get the URL of the Google Sheet you want to import data into. You can find that URL in the address bar of your 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). This tutorial will use the short key in the formula.

Step 2 : Enter the function and paste the URL or short key to add the first argument to the syntax.

Step 3 : Enter a comma and specify the range to import.

Below is the Google Sheets IMPORTRANGE formula that will allow you to import the student's name and their score into the current sheet using the short key from the above URL:

=IMPORTRANGE("1G_XEiSnUu0o8kmbkNVlQgTIjoE5lGqErRK3TlNfR1oI","'Math Score'!A2:B10")

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 a #REF! error in the worksheet. When you hover over the cell, you will see a prompt asking you to "Allow access".

Click the blue button and it will give you the results. Note that this only happens once per URL. Once you have allowed access, the request will not repeat.

This can be useful if you need to pull data from multiple spreadsheets. For example, in this case, you can pull grades from all your classes from different Google Sheets into a single spreadsheet.

Using the IMPORTRANGE function with Named Range

If you have to import ranges from multiple sources, it can get confusing. However, you can name your ranges to make things a little easier. Just follow this process:

Step 1 : In the worksheet you want to import from, highlight the range of cells and right-click on it, and navigate to More cell actions > Define named range .

Step 2 : In the pop-up menu, name the range.

Step 3 : In a new spreadsheet, use the Google Sheets IMPORTRANGE function with a range named range_string. In the example, the command would look like this:

=IMPORTRANGE("1G_XEiSnUu0o8kmbkNVlQgTIjoE5lGqErRK3TlNfR1oI","'NamedRange1")

Step 4 : Press Enter , then click on the #REF error and click Allow Access.

Some notes and tips for using the IMPORTRANGE function in Google Sheets

Once the sheets are connected (because you allowed access the first time you used the formula), any updates made in any sheet will automatically be reflected as the result of the formula. For example, if you change a student's grade, it will automatically change in the sheet with the formula.

When using the IMPORT RANGE formula, make sure there are enough empty cells to hold the result. If you have data in a cell that overlaps with the cell needed for the formula result, the formula will return an error. However, it helps identify the error by letting you know what the problem is when you hover over the cell.

If you are importing data from multiple worksheets, this can be a bit confusing. It is a good practice to create Named Ranges in the source sheet and then use the named ranges.

If you want that data to be added to the source sheet, instead of dragging a specific range, drag the entire column. This will pull data from the entire A and B columns. Now, if you add more data to the source sheet, it will automatically be updated in the destination sheet.

Frequently Asked Questions about the IMPORTRANGE function in Google Sheets

How to use the IMPORTRANGE function in Google Sheets?

1. Find the URL from your browser address bar to enter

2. Type =IMPORTRANGE( into an empty cell and paste the URL inside the quotes

3. Type a comma, then specify the range inside the quotes, for example: “Sheet2!B6:C18" and press Enter

4. Click on the #REF error and click Allow Access .

What is the IMPORTRANGE function in Google Sheets?

IMPORTRANGE is a function that allows you to transfer data from one spreadsheet to another without having to manually enter it. Using this function is better than copy pasting because it updates automatically.

Does the IMPORTRANGE function automatically update Google Sheets?

Yes, any changes made to the original worksheet will also change in the imported data.

Is there a limit to the IMPORTRANGE function in Google Sheets?

Yes, there can be up to 50 workbook cross-reference formulas.

How to filter the IMPORTRANGE function in Google Sheets?

You should use the QUERY function to filter information from the entered data.

How to use multiple IMPORTRANGE functions in Google Sheets?

Follow the standard procedure for importing a range of cells, but make sure that each time you use the IMPORTRANGE function, there is enough space in the worksheet to display the data.

Can you use the IMPORTRANGE function with formatting?

No, you can only import the data itself.