Skip to Content

How to use IMPORTRANGE in Google Sheets [2024] – 3 Examples

The IMPORTRANGE is one of the most powerful functions in Google Sheets. It allows users to extract information from other spreadsheets. To access the function, go to the “Insert” tab of the main menu and click “Function.” You will find the formula in the “Web” category.

Have you ever encountered a situation where you had to import data from a different workbook?

That’s where the IMPORTRANGE function comes into play. It gets the job done within a few seconds.

The function aims at data integration by importing information from one spreadsheet to another. Users can seamlessly collaborate by sharing and working on the same Google Spreadsheet.

In this article, we will learn how to use IMPORTRANGE in Google Sheets. Make sure to read the article till the end because you will understand how this function enhances collaboration, streamlines workflows, and empowers informed decision-making.

Download the Example Google Sheets

Before we move down the article, make sure to create a copy of the Google Sheets we used to demonstrate the IMPORTRANGE function.

Make A Copy!

The Google Sheet contains various sheets discussed below. You can practice the things we are about to discuss in this article.

If you have your own dataset ready to learn the IMPORTRANGE function, skip downloading the above file.

Explained: The IMPORTRANGE Function

The IMPORTRANGE function acts as a bridge between two sheets from the same or different workbooks.

It grabs the data from the source and displays the same in the destination sheet.

The general syntax for the function is as follows,

=IMPORTRANGE(spreadsheet_url, range_string)

It’s a pretty straightforward function in Google Sheets. But make sure you replace the arguments with the proper information, as discussed below.

  • spreadsheet_url” – It is the URL or address of the source spreadsheet from where you wish to import the data.
  • range_string” – Here, you can define the exact range you wish to import from the source spreadsheet. Users can either import the entire spreadsheet or a range of cells. 

Note that you must enclose the values in double quotations (“”). It is applicable for both “spreadsheet_url” and “range_string” arguments.

How to Use Google Sheets IMPORTRANGE Function

Anyone can use the IMPORTRANGE function without having a prior experience with Google Sheets to pull the data from the source spreadsheet.

In this section, let us discuss the steps to use the IMPORTRANGE function.

Consider the following data, which includes mobile sales across offline and online channels. Let’s assume that it is the source data that we need to import to another spreadsheet called the destination.

How to use IMPORTRANGE in Google Sheets

Now, without any further ado, let’s start.

STEP #1 – Get the Spreadsheet Link

It all starts with copying the spreadsheet link. The steps are pretty straightforward. However, just copying the link is not enough.

You need to grant permission to view or edit the spreadsheet.

Let’s first understand how to get the link, and then we will look at allowing the view permissions.

  • Open the source Google Sheet
  • Hover to the address bar of your browser
  • Copy the entire link

Make sure you copy the link with a unique ID assigned to the spreadsheet. Refer to the following image.

If you are unaware, then note that it is a combination of numbers assigned to each sheet in Google Sheets. Simply put, it is used to identify sheet.

Now, let’s look at the steps to allow the permissions.

  • Open the source Google Sheet
  • Hover to the upper-rightmost corner of the screen
  • Click the “Share” button
  • A new popup will be displayed on your screen, as shown below
  • Click the dropdown below the “General access” option
  • Choose the “Anyone with the link” option from the list
  • Press the “Done” button

We have assigned the view permission to everyone who will click on the link we copied above. If you have specific emails to share the access, then enter them in the empty box as displayed in the following screenshot.

STEP #2 – Decide the Range

As mentioned earlier in this article, you can either import the entire sheet or a specific range.

For example, our data in the spreadsheet has a range A1:E20. Refer to the following image.

It means that instead of the entire sheet, we plan to import the table with the range “A1:E20“.

In your case, to import the entire sheet, you need to use “A:Z” as the range.

Also, apart from the range, we need to put the spreadsheet name in the place of the “range_string” argument of the IMPORTRANGE function. Refer to the following example.

Test – IMPORTRANGE!A1:E20

It is a standard format using the following syntax,

“SheetName!Range” 

  • SheetName is the exact name of the source spreadsheet
  • The Range can be the cell or table range and even the entire sheet range as “A:Z”

As you can see in the above syntax, using the punctuation mark (!) between both arguments is compulsory.

STEP #3 – Import Data

This is the final step. We will combine the link copied in Step 1 and the range decided in Step 2 with the IMPORTRANGE function.

Let’s get started,

  • Open a new Google Sheet by clicking here
    (Note that we are creating a new Google Sheet, which will act as the destination spreadsheet)
  • Select the desired cell
  • Type “=importrange
  • Replace the spreadsheet_url argument with the link copied in the Step 1
    (Make sure to use the double quotation marks; otherwise, the formula won’t work as expected at the end)
  • Press “,” to move to the next argument of the IMPORTRANGE function
  • Next, replace the range_string argument with the “Test – IMPORTRANGE!A1:E20
    (Here, the “Test IMPORTRANGE” is the name of the spreadsheet from where we wish to import the data and “A1:E20” is the table range)
  • Close the parentheses using the “)
  • Press the “Enter” key

Our final formula should be as follows,

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dTROlhtTYg7YXcDKCMC19lTLRdX3KQ6qqTltiEwwh8Q/edit#gid=746660765","Test IMPORTRANGE!A1:E20")

Note that using the double quotation marks is necessary for the IMPORTRANGE formula to work. 

Google Sheets will instantly import the table, as shown in the above GIF. The time depends upon the size of the data you are importing.

If you are facing any errors, refer to the last section of the article, where we have discussed all the possible errors while using the IMPORTRANGE function.

How to Combine QUERY with IMPORTRANGE Function

The IMPORTRANGE function pulls the entire data from the range defined by the users.

But what if you wish to pull filtered data from the source sheet?

In this case, you can combine the IMPORTRANGE with the QUERY function in Google Sheets.

Explained: The QUERY Function

The primary use of the QUERY function is to combine various formulas in Google Sheets for sorting and filtering the data.

It is similar to SQL (Structured Query Language) query.

The general syntax for the function is as follows,

Here’s how you need to deal with each argument of the QUERY function.

  • data” – Here, you need to put the table range that needs to be filtered or sorted.
  • query_string” – It needs to be replaced with the query to filter and sort data. The function supports various clauses, arithmetic operators, and aggregators.
  • headers” – This optional argument is used to tell the function to treat the first rows as the header for the table. You can replace it with the “1” number.

We will replace the “data” argument with the table imported using the IMPORTRANGE function.

Now, let’s start.

Consider the following example where we have smartphone sales data. It displays the number of handsets sold offline and online.

Our task is to import the table into a new spreadsheet and filter it to display the sales of Google smartphones. Refer to the following image for the expected output.

Here are the steps:

  • Open the new Google Sheet by clicking here
    (This is the destination spreadsheet where we will import the data)
  • Select the desired cell
  • Type “=query
  • Choose the first option from the popup
  • Now, type “importrange” in the place of the “data” argument

    (As the IMPORTRANGE is a function in Google Sheets, you will see the popup as shown above)
  • Select the first option from the popup
  • For the “spreadsheet_url” argument of the IMPORTRANGE function, copy and paste the spreadsheet URL
    (Make sure to allow the view or edit permission for the source spreadsheet. You can refer to the above section of the article for the steps)
  • Press “,” to move to the next argument of the IMPORTRANGE function
  • Put “Main!A1:E20” in the place of the “range_string” argument of the IMPORTRANGE function
    (Please use the double quotation marks. The “Main” keyword represents the sheet name from the source Google Sheet)
  • Complete the bracket for the IMPORTRANGE function using “)
  • Press “,” to move to the next argument of the QUERY function
  • Next, type “Select * Where Col1=‘Google’
    (Make sure to use the double quotation marks. Also, we are using different clauses and arithmetic operators to create a query to filter the data from the imported table)
  • Type “,” to move to the next argument
  • Let’s put “1” in the place of the header argument of the QUERY function
  • Complete the parentheses using the “)” on your keyboard
  • Press the “Enter” key

Here’s how the final formula looks,

It is the best way to import and filter the source data across various spreadsheets. Note that you can also sort the data, but the knowledge of clauses, arithmetic operators, and aggregators is necessary.

Consider reading our How to Use The QUERY Function in Google Sheets (11 Examples) article to learn more about the tips and tricks to use the function to sort and filter data.

Benefits of using the IMPORTRANGE Function Google Sheets

A simple, straightforward syntax connects one spreadsheet with another to take the team collaboration to the next level.

It ensures the import of the latest data and enables real-time updates.

You may consider using the IMPORTRANGE function for:

  • Team Collaboration: It allows you to team up with others by sharing specific data across multiple workbooks or sheets. Simply put, you can integrate data from various resources into a single sheet for further analysis.
  • Real-Time Synchronization: Use this Function when you want to work with the latest information. For example, if any changes are made to the source data, then those changes get reflected automatically in your document. Additionally, these quick and automatic updates are reliable. The IMPORTRANGE function decreases the risk of error.
  • Cross-sheet Calculations: Users can perform various calculations and analyze data from different sheets or workbooks. You can seamlessly synchronize data between source and destination location. That data can be further used in Google Sheets formulas without any limitations. Your source data will stay safe and can be used as a backup.

These are just a few of the benefits of using the IMPORTRANGE function.

The IMPORTRANGE function is the best function offered by Google Sheets to import and display data across spreadsheets.

Limitations of using the IMPORTRANGE Function Google Sheets

While the IMPORTRANGE function is one of the powerful tools for importing data from one workbook to another, it also has certain limitations.

Let’s have a quick look at them:

  • Performance: While importing large datasets, the IMPORTRANGE function will have performance issues. This is true for frequent imports as well. The performance also depends upon the internet connection speed.
  • Limited Imports: The destination spreadsheet can include a maximum of 50 IMPORTRANGE functions. If you are dealing with complex datasets and wish to import more than 50 tables or sheets, it’s simply not possible.
  • Calculation Delays: Changes done in the source spreadsheet may not be reflected immediately in the destination sheet. This hampers the calculation time of the functions using the imported data in the destination sheet.

Apart from the above, users won’t be able to import formatting and the content of cells holding the formulas.

Errors while using Google Sheets IMPORTRANGE function

If you fail to use the IMPORTRANGE function properly, you might see an error which will prevent you from importing the data.

Let’s see some common errors related to the function and how to troubleshoot them.

#1 – Formula Parse Error

Cause: This error is seen when the entered syntax, the sheet’s name or the cell range is incorrect.

Solution: Double-check the syntax and ensure proper formatting, such as quotations, exclamation, etc. for the arguments. Also, check the sheet name and cell references are correctly inserted into the formula.

#2 – Spreadsheet cannot be found

Cause: Invalid URL or the spreadsheet doesn’t exist.

Solution: Ensure the URL is correct and the source sheet hasn’t been deleted or renamed.

#3 – You don’t have permission to access the sheet

Cause: There is no access to the referred source.

Solution: Check if you have edit permission for the source sheet and request edit access from the owner.

#4 – Cannot find range or sheet for the imported range

Cause: Wrong sheet name or the data range in the source.

Solution: Verify the sheet name and range accuracy in the source sheet.

#5 – #REF error

Cause: This occurs when the range specified in the IMPORTRANGE formula is incorrect or the permission to view the source data is missing.

Solution: Make sure to recheck the data range and get access to the source spreadsheet. 

#6 – #N/A Error 

Cause: This can be seen when no data is in the specified range.

Solution: Check for data in the specified sheet and range. If there is no data, then it will return #N/A.

FAQs – How to use IMPORTRANGE in Google Sheets

Q. Does IMPORTRANGE update automatically?

The IMPORTRANGE function updates the imported data automatically, but not in real-time.

There might be a delay of a few minutes. The update frequency is limited to about once every 30 minutes and sometimes with manual refreshes.

The data is updated every time you open the destination spreadsheet.

Q. Can IMPORTRANGE include formatting?

The IMPORTRANGE function can only import the data from the table or entire sheet.

It won’t include the formulas and formatting of the table.

If you want the data along with the formatting, make a copy of the document and use the IMPORTDATA function.

Q. What is the IMPORTRANGE limit in Google Sheets?

Only 50 cross-workbook reference functions can be used in a spreadsheet. In other words, a maximum of 50 IMPORTRANGE functions can be used per spreadsheet.

Also, for the imported data, you can include 50,000 maximum cells from the source sheet.

Conclusion

That’s it! We have discussed how to use the IMPORTRANGE function in Google Sheets to import data from one workbook to another in a few seconds.

Using the function is a breeze.

You can quickly reference data across a range of spreadsheets.

But remember, IMPORTRANGE is always good for smaller data ranges and occasional updates.

To learn excellent tips and tricks for becoming a pro at using Google Sheets, check out our blog page.