HomeGoogle DocsSpreadsheetGoogle Sheets Importrange Function - Basic to Advanced Use Tips

Google Sheets Importrange Function – Basic to Advanced Use Tips

Published on

If you don’t want to give others direct access to your Google Sheets file but still want to share the data, you can use the IMPORTRANGE function. This function imports data from another Google Sheets file, using the file’s URL and sheet name with range. Any changes you make to the source sheet will be reflected in the imported ranges in the destination sheet.

However, it is important to note that you should not make changes to the imported range of cells in the destination file. Doing so will break the formula.

You can also use the IMPORTRANGE function to import data from one sheet to another within a file. However, I do not recommend doing this, as it is more efficient to use ARRAYFORMULA or QUERY to accomplish this task.

I usually use the “within one sheet” feature to experiment with new things in IMPORTRANGE, as I don’t need two files to test.

In this tutorial, we will learn from basic to advanced use of the IMPORTRANGE function in Google Sheets.

IMPORTRANGE Function Syntax and Arguments

Syntax: IMPORTRANGE(spreadsheet_url, range_string)

Arguments at a Glance:

spreadsheet_url: The URL of the spreadsheet from which the data will be imported.

Note: You can hardcode the URL within the formula, or it can be a cell reference containing the URL. In the former case, i.e., when you hardcode, you must place double quotation marks on both sides. We will see this with IMPORTRANGE examples below.

range_string: The cell range to import in the following text format “sheet_name!range” (e.g. "Sheet1!B1:N100" or "B1:N100")

If you omit sheet_name, the formula will import the data from the first sheet in the source file.

Here, the above note is also applicable. However, instead of “URL,” read “cell range,”

Copying the Spreadsheet URL

Here are the steps to copy the spreadsheet URL to use within the IMPORTRANGE function in Google Sheets:

  1. Open the source file (the sheet from which you want to import data) in your favorite browser, such as Safari, Chrome, etc.
  2. Click within the address bar to select the URL. If you see the URL in the address bar highlighted, that means it’s selected.
  3. Press Ctrl+C on Windows or ⌘+C on Mac. Alternatively, you can right-click and select Copy.
Copying Sheet URL - Safari

Basic Use of Google Sheets IMPORTRANGE Function

We have already seen the purpose of the IMPORTRANGE function in Google Sheets. As the function name suggests, it is used to import data from Google Sheets.

What are the practical uses of importing data?

The practical uses of importing data vary from user to user. However, one common use case is to keep our master Google Sheets file intact and share a portion of content from it.

We will see more advanced uses of IMPORTRANGE later. For now, here is a basic example to get you familiar with the syntax.

Example

There are two Google Sheets: File 1, masterfile and File 2, Result sheet.

Our data is in the masterfile, and the data range is Class Data!A:F. We want to copy this data into the resultsheet.

Approach 1

  1. Open masterfile and copy the sheet URL as explained above.
  2. Then go to the destination file named resultsheet.
  3. In cell A1, enter =IMPORTRANGE(" and paste the copied URL. Close the quotation marks. Place a comma, and enter the range to copy within quotation marks.

Example:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=0","Class Data!A:F")
Google Sheets Importrange Example

The formula may return a #REF error instead of importing data.

Hover your mouse pointer over it and click on “Allow access.”

importrange error

Approach 2

  1. Copy the URL from the source sheet.
  2. Paste the copied URL in cell A1 in the destination sheet.
  3. Enter the range reference Class Data!A:F in cell A2.
  4. Enter the following IMPORTRANGE formula in cell B2.

Don’t forget to “Allow access.”

=IMPORTRANGE(A1,A2)

This is one of the fastest ways to use an IMPORTRANGE formula.

If clicking “Allow access” does not solve the IMPORTRANGE #REF! error, what should I do?

There are certain things you can do to solve the #REF error problem in the IMPORTRANGE function used in Google Sheets.

If you are the owner of the source sheet, make sure that you are using the correct URL.

If you are not the owner of the source sheet, ask the owner of the sheet to share the sheet with you.

Hover your mouse pointer over the error and read the tooltip. The error may associate with the range_string parameter.

If you have checked everything and have the necessary permission to import and still having problems, do the following:

  1. Reload the spreadsheet.
  2. Clear the cache and cookies in your browser.

Similar: Different Error Types in Google Sheets and How to Correct It

IMPORTRANGE Function Advanced Usage Tips in Google Sheets

The IMPORTRANGE function can be used with other functions to perform more complex data manipulation tasks. Some of the functions that you can combine with IMPORTRANGE are VLOOKUP, FILTER, SUMIF, and QUERY.

In this advanced IMPORTRANGE usage, the QUERY function stands out from the rest as it can perform advanced data manipulation in Google Sheets. For example, you could use the IMPORTRANGE function to import data from a source sheet, and then use the QUERY function to filter, sort, or aggregate the data.

  1. How to Use Query with Importrange in Google Sheets.
  2. How to Use IMPORTRANGE Function with Conditions in Google Sheets.

When you use an IMPORTRANGE formula as the range in lookup functions such as FILTER and XLOOKUP, use the LET function to assign a name to it. This will make your formulas more readable and easier to maintain.

Please see an example of this in my latest tutorial titled IMPORTRANGE Within FILTER Function in Google Sheets. You can also find another example at How to Vlookup Importrange in Google Sheets.

Here are some advanced tips and tricks for working with the IMPORTRANGE function.

  1. How to Freeze Cell in Importrange in Google Sheets.
  2. Importrange Named Ranges in Google Sheets.
  3. Dynamic Sheet Names in Importrange in Google Sheets.
  4. Relative Cell Reference in Importrange in Sheets.
  5. Sumif Importrange in Google Sheets – Examples.

Conclusion

Do you want to know how to use the IMPORTRANGE function to import data from one sheet to another within the same Google Sheets spreadsheet?

The URL-based import method is not necessary in this case, as the data is being pulled from different sheets within the same file.

Instead, you can use the following array formula:

={'Class Data!A:F'!A:F}

This will import the range A1:F from the Class Data sheet into another sheet in the same file.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

24 COMMENTS

  1. 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?

  2. 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.

  3. 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!

  4. 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")

  5. 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"))

    • 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,

  6. 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!

    • 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,

  7. 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!

  8. 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.

    • 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

    • 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?

  9. 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?

  10. 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 🙂

LEAVE A REPLY

Please enter your comment!
Please enter your name here