Don’t forget to share this post

If you are working or collaborating on Google Sheets and wish to integrate data between spreadsheets, you can use the IMPORTRANGE formula in Google Sheets. Before you continue, you may want to look through our related article How to reference another sheet in Google Sheets as referencing is a crucial part when using the IMPORTRANGE formula.

This article will explain the IMPORTRANGE Google Sheets syntax formula, how to import a specific range of data from one Google Sheet to another, how to import data from several sheets or tabs into a Google Sheet, and how to combine the Google Sheet QUERY and VLOOKUP formulas with IMPORTRANGE.

In a few simple steps, you will be able to integrate data range(s) between Google Sheets without the need to use third-party software.

Syntax of the IMPORTRANGE formula

The IMPORTRANGE formula syntax is very straightforward, but we’ll explain each parameter below:

=IMPORTRANGE("spreadsheet_url","range_string")
  • “spreadsheet_url”: The URL or ID of the spreadsheet you wish to import data from. 
  • “range_string”: Specifies the data you wish to import; it should include the name of the sheet and the range of cells.

How to use IMPORTRANGE in Google Sheets?

It should first be noted that in order to use the IMPORTRANGE function in Google Sheets you need to know the URL. All files saved on your Google Drive have a unique URL, which is what allows you to ‘refer’ to the spreadsheet. Depending on the data you wish to refer to, you can specify the page or range within the importing spreadsheet.

Let’s say we want to import students scores from file ‘Assignment Scores’ into ‘Full Student List’:

  1. 1. Go to the Google Drive folder where your exporting spreadsheet is, and right click to “Get link”.
How to use IMPORTRANGE in Google Sheets Get Link
  1. 2. Make sure you click on “Copy link” too.
How to use IMPORTRANGE in Google Sheets Copy Link
  1. 3. Another simpler and faster way is to open the exporting spreadsheet and click on the address or URL bar to select the link.
How to use IMPORTRANGE in Google Sheets Copy from URL bar
  1. 4. Now that the link is copied, go to the importing spreadsheet and paste it in the spreadsheet_url variable of the IMPORTRANGE formula.
How to use IMPORTRANGE in Google Sheets Paste in Formula
  1. 5. Then, the range_string can be added in two ways. First, you simply select the range you wish to import and then copy the range from the far-left. Here, E2:E41.
How to use IMPORTRANGE in Google Sheets Select range
  1. 6. A second way is to name the range and save it for further use. For this, you would need to click on the arrow next to E2:E41 and click on “Manage named ranges”.
How to use IMPORTRANGE in Google Sheets Manage ranges
  1. 7. Add the name of your range to the field in the pop-up window. In this example, I'll name it 'Score'. Check your range and name are correct before clicking Done.
How to use IMPORTRANGE in Google Sheets Name Range
  1. 8. Now, try typing in ‘Score’ as your range_string. Remember to add speech marks on either side as shown below.
How to use IMPORTRANGE in Google Sheets Score in Formula
  1. 9. Now press the return key. You may get a message asking you to connect these sheets. Simply click on “Allow access” to connect them.
How to use IMPORTRANGE in Google Sheets Allow access

NOTE: Don’t forget that if multiple users are working on Google Sheets, you will need to allow access in order for the IMPORTRANGE to function properly.

  1. 10. Once you allow access to the other file, it should start loading. Depending on the amount of data you imported, it may take more than a few seconds.
How to use IMPORTRANGE in Google Sheets Imported Range

We would like to remind users that the IMPORTRANGE function does not import the data with formatting; it will only import the values.

How to VLOOKUP From Another Google Sheet or Workbook (+ Examples)

Sometimes our data may be spread out among different Google sheets or workbooks. Here's how to VLOOKUP from another sheet or workbook

READ MORE
How to VLOOKUP From Another Google Sheet or Workbook Examples

How to use IMPORTRANGE from multiple sheets?

You can continue to use the IMPORTRANGE formula to import a range from another tab in that same Google spreadsheet or from multiple Google sheets.

  1. 1. Let’s say the exporting file has two tabs, Sheet 1 and Sheet 2, each containing scores from two different assignments. As before, you want to import the cells containing scores for both to another Google sheet.
How to use IMPORTRANGE from multiple sheets Import 2 tabs
  1. 2. Note that, in this case, the IMPORTRANGE formula will have to include the other tab as well. We can do this by adding curly brackets {} to start and end the formula. The syntax also becomes a little more complex.
={IMPORTRANGE("spreadsheet", “range_string”), IMPORTRANGE("spreadsheet", "range_string")}
How to use IMPORTRANGE from multiple sheets IMPORTRANGE Formula
  1. 3. Below, you will see how the data has been imported horizontally, given that we used “,” (in red) instead of “;” to separate both IMPORTRANGE formulas.
={IMPORTRANGE("spreadsheet", “range_string”), IMPORTRANGE("spreadsheet", "range_string")}
How to use IMPORTRANGE from multiple sheets Horizontal import

NOTE: If you want to import vertically, i.e. the two sets of ranges in one same column, you simply need to substitute the comma “,” for a semicolon “;”:

={IMPORTRANGE("spreadsheet", “range_string”); IMPORTRANGE("spreadsheet", "range_string")}
  1. 4. Make sure that the sheet or tab name is correctly input. It is one of the most common errors and you will receive an error message indicating that the formula cannot find the range or the sheet for the imported range you selected. It may also simply return an error message like this.
How to use IMPORTRANGE from multiple sheets Formula parse error
Linking Google Sheets How to Reference Another Sheet
Linking Google Sheets: How to Reference Another Sheet?

Sometimes you have to reference or merge data from multiple sheets or spreadsheets. Here's how to easily link multiple Google Sheets

READ MORE

How to combine IMPORTRANGE and other functions in Google Sheets?

There may be situations in which you want to filter the data imported into your Google Sheet. In this case, there are two formulas you can combine with IMPORTRANGE; QUERY and VLOOKUP.

How to combine Google Sheets QUERY and IMPORTRANGE?

You can combine the QUERY function with IMPORTRANGE for the following reasons:

  • Import a specific range.
  • Disregard specific rows.
  • Modify column names.
  • Format values in cells and columns.
  • Filter data, sort data.
  • Perform any operation related based on arithmetic, aggregate, and scalar actions.

The combined syntax is:

=QUERY(IMPORTRANGE("spreadsheet", "range_string"), "query_string")

How to combine Google Sheets VLOOKUP and IMPORTRANGE?

You can combine VLOOKUP and IMPORTRANGE in Google Sheets to import data according to a search criteria.

The combined syntax is:

=VLOOKUP(search_key,IMPORTRANGE("spreadsheet","range_string"),index,[sorted_boolean])

This formula is a bit more complex as it requires understanding the following parameters:

  • search_key: This would refer to the cell range you will perform the search criteria on.
  • index: The column index to return the value from
  • sorted_boolean: The TRUE (sorted)/FALSE boolean indicates whether the indexed column is sorted or not.

How can Layer help?

Layer is a spreadsheet platform that works on top of Excel and Google Sheets. It allows you to easily manage and automate spreadsheet workflows. Using Layer, you can:

  • Upload or connect your existing Excel or Google Sheets-based budget.
  • Share different sheets or even cell ranges of your spreadsheet with various stakeholders or departments involved in the budgeting process.
  • Automate your communication flows and keep track of your data submissions, contributors, and deadlines.
  • Review every single change made and decide which ones to merge with your spreadsheet or discard.
  • Eliminate errors in your budget or manually copying and pasting data across files.

Sign up and schedule an onboarding call to get started with Layer right now.

Conclusion

We recommend using the IMPORTRANGE function for sporadic data imports. For collaborative work that requires constant data imports or more advanced integration between multiple spreadsheets, you might need to use third-party software. This is because having too many IMPORTRANGE formulas within a Google Sheet can increase loading time and hinder your team’s productivity levels. Nonetheless, we have also illustrated the syntax for IMPORTRANGE used in combination with QUERY and VLOOKUP.

One of the main advantages of using Google Sheets is that the IMPORTRANGE formula will update automatically. However, users have witnessed a set of drawbacks or common errors that we would like to remind you of:

  • You must keep your Google Sheets files open in order for the IMPORTRANGE formula to work. Otherwise, you will find that data is not updating properly.
  • You must save uploaded Excel files as Google Sheets so that the IMPORTRANGE formula does not delay the loading process.
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 28 2022, Updated Feb 20 2022