How to Use the IMPORTRANGE Function in Google Sheets

If you need to import data from another Google Sheet into the one you are currently working on, you may be wondering how to do this.

In other spreadsheet programs like Excel, it’s easy to import a workbook that exists on your computer into your spreadsheet, but in Google Sheets, the process is a little bit different.

In Google Sheets, you have to use the IMPORTRANGE function to pull data from another file.

In this tutorial, I will show you how to use the IMPORTRANGE function in Google Sheets.

What is the IMPORTRANGE Function?

IMPORTRANGE is a function in Google sheets that will import a range of cells from another spreadsheet.

If you are working on a spreadsheet, and you need to pull data from another file, IMPORTRANGE will help you do this.

The syntax of IMPORTRANGE is:

=IMPORTRANGE(spreadsheet_url, range_string)

• spreadsheet_url – The first parameter of this formula is the URL of the spreadsheet that has the data that you want to be imported
• range_string – The second parameter in this formula is a string that contains the range you want to be imported. It has to be in this format: “[sheet_name!]range” (examples: “Sheet1!B2:C7” or “A2:B6”)

One important thing to note as that when using the IMPORTRANGE function you will have to grant permission for the spreadsheet to have access to another spreadsheet. When using this function Google Sheets will ask you for permission to link the sheets

How to Use IMPORTRANGE

Now that I have shown you the basic syntax of this formula, I will show you how to use it by walking you through an example.

1. In this example, I am going to import the table below into a new file. So the first thing you want to do is open the file that contains that data you want to import

2. Next you need to copy the URL of the workbook that you want imported. You can either copy the entire URL, or you can copy just copy the workbook key in the middle section. Right-click and copy or press CTRL + C on your keyboard.

OR

3. Open up your new workbook where you want to import the data, and select the cell where you want the imported data to appear. In the cell type the equals signs (=) and begin your IMPORTRANGE function by typing IMPORTRANGE and pressing Tab on your keyboard

4. Next paste the URL that you copied in step 2. You can either right-click and select Paste or press CTRL + V on your keyboard. Make sure to put your URL is inside quotation marks by placing one at the beginning and ending like in the below example. Add a comma when you are done with this step

5. Next you need to add the range of data that you are importing. Make sure this range is also inside quotation marks. In my example, I am using “Sheet1!:A:C” as the second parameter in my function since I am importing the table that exists on Sheet1 in the cell range of A:C. When you are done with this step add your closing parenthesis “)” and press Enter to calculate the function

6. If these sheets have never been connected before you will get a #REF error. Click on the cell and you should see a prompt asking you whether or not you want to connect these sheets. Select Allow access

7. You should now see the range of data that you are importing in your file

IMPORTRANGE from Multiple Sheets

If you want to import data from multiple sheets in one column, there is a clever way to do this by using IMPORTRANGE and creating a vertical array with the data.

Let’s say that I have data that exists on two sheets. I can use two IMPORTRANGE functions by wrapping them in array brackets “{}” to import data from both sheets. In sheet 1, I will import the range from A1:A12, and in sheet2 I will import from A1:50.

Between each IMPORTRANGE function I need to have a semi colon, because this will tell sheets to place the data from the second import right after the data from the first import.

Here is what my formula looks like:

={IMPORTRANGE(“SpreadsheetURL”,“Sheet1!A1:A12”);IMPORTRANGE(“SpreadsheetURL”,“Sheet2!A1:A50”)}

This method also works for importing data from multiple workbooks, just change the spreadsheet URL to the appropriate workbook if you need to import from different files.

Closing Thoughts

The IMPORTRANGE function is incredibly useful when you need to use data that exists in another workbook.

In this example, we have imported entire columns but you can also just use specific ranges instead. The benefit of importing entire columns though is that the imported data is dynamic, so if your source data changes, so will your imported data.

More Google Sheets Tutorials:
How to Use the ARRAYFORMULA function
How to Use the SORT function
How to Create Hyperlinks
How to Use the IMPORTXML Function