Google Sheets: Explore the IMPORTRANGE Function

You can use the IMPORTRANGE function to import data from one Google Sheets file to another. Note that this function works specifically between Google Sheets files, not Excel files opened in Google Sheets.

While you can also use the IMPORTRANGE function to import data between sheets within the same file, I don’t recommend this approach. It’s more efficient to use ARRAYFORMULA or QUERY for such tasks. I use this feature (importing within the same file) primarily for experimenting with IMPORTRANGE without needing multiple files.

In this tutorial, we will explore both basic and advanced applications of the IMPORTRANGE function in Google Sheets.

IMPORTRANGE Function: Understanding the Syntax

Syntax:

IMPORTRANGE(spreadsheet_url, range_string)

Arguments at a Glance:

spreadsheet_url: The URL of the spreadsheet from which the data will be imported.
You can hardcode the URL within the formula (enclosed in double quotes) or use a cell reference containing the URL. We will explore this with examples below.
range_string: The cell range to import, in the format "sheet_name!range" (e.g., "Sheet1!B1:N100" or "B1:N100").
If you omit the sheet name, the formula will import data from the first sheet in the source file. You can also use a named range like "SalesData" or a structured table reference like "Table1[#ALL]".

How to Copy the Spreadsheet URL

To use the IMPORTRANGE function, follow these steps to copy the URL of the source sheet:

Open the source file, the sheet you want to import data from.
Click within the address bar to select the URL.
Press Ctrl+C (Windows) or ⌘+C (Mac) to copy the URL, or right-click and select Copy.

How to Use the IMPORTRANGE Function in Google Sheets

Assume there are two Google Sheets files: File 1 (MasterFile) and File 2 (ResultSheet).

The data in MasterFile is located in the range Class Data!A:F, and we want to import it into ResultSheet.

Open the MasterFile and copy the sheet URL as explained above.
Go to the destination file named ResultSheet.
In cell A1, enter the following formula:
=IMPORTRANGE("paste the copied URL here","Class Data!A:F")

Example of Using the IMPORTRANGE Function in Google Sheets

If you see a #REF! error instead of imported data, hover over the error and click on Allow access.

Allow Access for #REF Error in IMPORTRANGE Function

Alternatively:

Copy the URL from the source sheet.
Paste the copied URL in cell A1 in the destination sheet.
Enter the range reference (Class Data!A:F) in cell A2.
In cell B2, enter the following IMPORTRANGE formula:
=IMPORTRANGE(A1, A2)

Don’t forget to Allow access when prompted.

This is a quick way to use the IMPORTRANGE function without hardcoding the URL and range in a single formula.

Can I Use Named Ranges as range_string?

Yes! If you have a named range like “SalesData”, you can use it as the range_string. When hardcoding, enter it within double quotes, just like a standard range string.

Example:

=IMPORTRANGE("paste the copied URL here","SalesData")

Can I Use Structured Table References as range_string?

Yes! Google Sheets now supports structured table references in the IMPORTRANGE function.

Assume you have a table named Table1 in the MasterFile (source file) with column names Item, Qty, and Status. You can use structured table references in the range_string like this:

"Table1[#ALL]" – Imports the entire table, including headers.
"Table1" – Imports the entire table without headers.
"Table1[Status]" – Imports only the Status column (without headers).

Pro Tip: If you want to extract specific data, import the entire table with headers and use the FILTER function to retrieve the columns you need based on matching headers.

Fixing #REF! Errors in the IMPORTRANGE Function

If clicking “Allow access” doesn’t solve the #REF! error in the IMPORTRANGE function, here are some steps you can take:

Ensure you’re using the correct URL if you are the owner of the source sheet.
If you’re not the owner, ask the sheet owner to share access with you.
Check for errors in the range_string parameter by hovering over the error message.

If permissions and parameters are correct and you still encounter problems:

Reload the spreadsheet.
Clear your browser’s cache and cookies.

IMPORTRANGE Resrouces

We have plenty of IMPORTRANGE function resources to help you learn advanced data manipulation techniques with imported data. Here they are:

24 COMMENTS

Ameya Raje February 28, 2020 At 4:20 pm

Hi,

I have a Google Sheet from where I import the columns using Query and Importrange functionality into another Google Sheet.

I mark some comments in the new Google Sheet against the rows that are imported. But when the new rows get amended (using Query and Importrange) the comments marked for these also are shuffled. Any way I can lock those comments or those rows?

- Prashanth February 29, 2020 At 8:54 am
  
  Hi, Ameya Raje,
  
  That’s only possible with a common ID column as detailed in this guide – Align Imported Data with Manually Entered Data in Google Sheets.
  
andrew January 23, 2020 At 3:41 am

Hi,

Is there a way that I could stop an importrange from updating? or someway to only reload the data with a button press or a checkbox tic? I have a file with a schedule that I am mirroring to another workbook and I don’t want others to see the changes before they’re ready.

- Prashanth January 23, 2020 At 9:08 am
  
  Hi, Andrew,
  
  We can control the import with a tickbox inserting in the source file. I’ll test it myself and update you very soon.
  
- Prashanth January 23, 2020 At 5:05 pm
  
  How to Control Reloading of Importrange from the Source File in Google Sheets
  
Lili October 10, 2019 At 10:49 pm

Hi!

Is there a way to import data from one sheet to another sheet only when the checkbox is marked?

If so, how do I do it?

Thanks for all your help, these tutorials are great!

- Prashanth October 11, 2019 At 11:06 am
  
  Hi, Lili,
  
  Checkbox marked on which Sheet. Source sheet or the Sheet containing the Importrange formula?
  
  Please clarify.
  
  Best,
  
William May 27, 2019 At 7:28 pm

Hello,

I have multiple spreadsheets that are tabbed “Wk1” – “Wk52”, and I’m using importrange within the sheets.

However, I am trying to figure out a way to make the range_string part of the formula grab a cell within the sheet that I have the formula in so that it knows what tab I want data from.

Not sure if this is possible?

So if I have a cell D3 with the value 21 in my current sheet (because I want data from Wk21), how could I correctly input the formula? I’m trying similar to this so far, but with no luck –

=importrange("url","Wk"D3"!A2")

- Prashanth May 28, 2019 At 8:37 am
  
  Hi, William,
  
  Your formula to get dynamic tab names in Importrange must be as follows.
  
  =importrange("url","Wk"&D3&"!A2")
  
  I think you have missed my tutorial Dynamic Tab Names in Importrange.
  
  There you can find a similar solution using named ranges.
  
  Best,
  
Desmond LEE May 5, 2019 At 6:29 am

Hi There,

Importrange shows only the header in the Query. When I delete the query and manually type value everything is pulled to another sheet.

This is the query.

=QUERY('WO Start Rev 03 -1'!A:J, "SELECT A,C,I,F WHERE C MATCHES '"&JOIN("|",C:C)&"'")

This is the importrange formula.

=IF(ISERROR(ImportRange("URL","QA")), IF(ISERROR(ImportRange("URL","QAB")),ImportRange("URL","QAC"), ImportRange("URL","WO!Q:q")),ImportRange("URL","WO!q:Q"))

- Prashanth May 5, 2019 At 9:19 am
  
  Hi, Desmond LEE,
  
  The Query formula must be in this pattern. See the TEXTJOIN function used instead of JOIN.
  
  =QUERY(A1:D,"SELECT A,B,C,D WHERE C MATCHES "&"'"&textjoin("|",true,G1:G)&"'")
  
  When you replace A1:D (data) with the above importrange, you must change the column identifiers in SELECT clause and as well as in WHERE clause.
  
  For example, you must use the formula as below.
  
  =QUERY(importrange formula here,"SELECT Col1,Col2,Col3,Col4 WHERE Col3 MATCHES "&"'"&textjoin("|",true,G1:G)&"'")
  
  I have updated my post to include additional resources at the bottom. Please go thru’ that too for some awesome importrange related tips.
  
  Best,
  
Mel March 21, 2019 At 10:56 pm

Hi,

Is there a way to ensure that the font/size/color/bold/underline etc. import along with the content in the tab and ranges?

I used the importrange according to your example but it defaulted to the regular font, size etc.

Thanks!

- Prashanth March 22, 2019 At 2:01 pm
  
  Hi, Mel,
  
  If you want to import data and also keep the ‘characteristics’ of the data, you can follow the below workaround.
  
  1. Go to the Source file (File A).
  2. Click File > Make a copy. It will make a duplicate copy of the source file (Copy of File A).
  3. Delete all the contents in this ‘Copy of File A’.
  4. Import the content from ‘File A’ to ‘Copy of File A’.
  
  Best,
  
Tom January 26, 2019 At 2:36 am

I using Importrange with Vlookup and am getting the #REF! error as it is evaluating “to an out of bounds range.”
My formula is as follows (note I have substituted “key” for the actual key)

=VLOOKUP(A3&"", IMPORTRANGE("key","Duane!A2:P"),8,FALSE)

The only thing I can think of is that it is a permissions issue but I can’t get the “allow access” bubble to pop up. Is there something else I may be missing?

Thanks!

- Prashanth January 26, 2019 At 9:47 am
  
  Hi, Tom,
  
  The problem is the Importrange is not importing the data. So Vlookup has no column 8 to return value.
  
  First use importrange individually. Allow permission and then use it inside Vlookup as the range.
  
  Additionally, I have Vlookup + Importrange tutorial.
  
  How to Vlookup Importrange in Google Sheets [Formula Examples]
  
  Best,
  
maria November 9, 2018 At 2:29 pm

Hello! Thanks much for the guide. Is there a way to allow the person with whom we share the document by IMPORTRANGE to edit the sheet and both have live updates? Like in cloud? Thanks.

- Prashanth November 9, 2018 At 2:55 pm
  
  Hi, Maria,
  
  I fear I didn’t understand your question correctly. I guess you are talking about sharing Google Sheets file with edit access.
  
  You can share any Google Sheets file with Edit access. To do that look for the blue button labeled as Share on your Google Sheets. It’s on the top right-hand side of your screen.
  
  You can also share a file in just view or comment mode. You can see that options once you click on the said blue button.
  
  There is one more method in file sharing in Google Sheets but not official. It’s COPY mode.
  
  Share Google Sheet Files on Copy Mode
  
Peter Frost September 17, 2018 At 12:51 pm

Hi,

Is there a way to use IMPORTRANGE over multiple cells ?

Suyog August 29, 2018 At 11:51 am

If sheet is protected how we can use importrange function

- Prashanth August 29, 2018 At 4:48 pm
  
  Hi, Suyog,
  
  I don’t think it will affect the importrange. In my testing, the Importrange function works well with a protected sheet.
  
  Have you faced any issue? If then, what the error message says?
  
Tori August 17, 2018 At 1:45 am

Thanks for sharing, this is great! Is there a way to ‘lock’ this formula on the content you’re really looking for. For example, if I’m using the import range function on cell C5 in doc ‘123’ and the editor of doc ‘123’ adds a row to the top of doc ‘123’, it would bump the cell I need to C6. Any way I can ensure the import range function is smart enough to update my formula as things in the source sheet shift?

- Prashanth August 17, 2018 At 9:29 am
  
  Hi, Tori,
  
  Good question! I have this already addressed in an earlier tutorial. Here you go.
  
  https://infoinspired.com/google-docs/spreadsheet/freeze-cell-in-importrange-in-google-sheets/
  
  I hope it could be useful.
  
  Cheers!
  
Songthang July 19, 2018 At 12:55 pm

Hi,

I have a question. Could we draw a chart by directly using Importrange Data? Because I have many files but would like to show the chart in one specific sheet.

Thank you in advance 🙂

- Prashanth July 19, 2018 At 1:11 pm
  
  Hi, Songthang,
  
  You can create a chart from the imported data. I don’t find any issue in that.