How to Use Google Sheets IMPORTRANGE Function

When you’re working with spreadsheets, you rarely work in just one of them. Your work is spread out across a number of sheets and you often need to extract information from them into another sheet. Why do this manually when there’s a built-in function in Google Sheets called IMPORTRANGE?

With IMPORTRANGE, you can freely extract information from any spreadsheet at will and use its data in the required sheet. The data you import can be one cell or a range of cells. Just make sure the data isn’t too much or you might encounter an error.

How to use IMPORTRANGE

The syntax for IMPORTRANGE is very easy: =IMPORTRANGE(spreadsheet_url, range_string)

It takes 2 arguments:

• spreadsheet_url is where we type in the URL of the spreadsheet we want to extract data from
• range_string specifies which cells and which sheet we want to use

Spreadsheet_url can either be the entire URL of the spreadsheet or just the unique key from the URL. It doesn’t matter which one you choose as there is no difference in performance. The only advantage to using the key is having shorter strings inside the formula.

Here’s what we mean by the full URL:

and here it is with just the key:

Range_string

Range_string contains the name of the sheet along with the start and endpoints of the range we need. Let’s say we want to extract the cells A1:A11 from the first spreadsheet. Here’s what the string will look like: “sheet!A1:A11”

Note: If we don’t specify the name of the sheet (write it as “A1:A11”), the data will automatically be extracted from the first sheet inside the spreadsheet.

We can also reference a cell that contains the address and name of the sheet we need.

IMPORTRANGE example

Let’s extract some data with an example. Here we have a spreadsheet with data of some people.

And in another spreadheet, we have a column of fax numbers.

Our goal is to extract this column into the first spreadsheet with the help of IMPORTRANGE. We’ll begin typing the formula in cell E1 of the first spreadsheet and it’ll look like this:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1VfIzxRtWfglsgIbUTQvU0uuqW1xfPH-yu0tlJIkbB7Y/edit#gid=0”, “sheet1!A1:A11”)

The URL for your sheet will be different, so please don’t copy the above text.

Once you press enter, you’ll be prompted with an error that tells you to allow access into the sheet.

Just click the allow access button and now you should see the fax number column pop up in your spreadsheet. Just keep in mind that once you click ‘Allow access’, all editors in the sheet you’re extracting the data to will be able to access the external sheet’s information.

If the original editor who clicked ‘Allow access’ is removed from the sheet, the access will be revoked.

And here you go. You have successfully used IMPORTRANGE to extract data.

If you liked this article, do check our guide to adding a dropdown menu and other tricks in Google Sheets. If there’s any specific topic you want us to cover, let us know in the comments below!