An Easy & Comprehensive IMPORTRANGE Google Sheets Guide (2024)

By

Importing data from other documents into your current file doesn’t have to be hard. I’ll use this IMPORTRANGE Google Sheets function guide to show you how to do it in just a few minutes.

This blog post will discuss the process in further detail below. But, essentially, IMPORTANGE in Google Sheets can be boiled down to these few steps:

  1. Type the IMPORTRANGE function into an empty cell.
  2. Copy and paste the URL of the sheet you want to import, followed by a comma (,) and the range_string, and press “Enter.”

Note that while you can connect two workbooks in Excel, the only way to connect two Google Sheets is by using the IMPORTRANGE Function.

Watch Video – IMPORTRANGE Google Sheets Guide

Understanding the IMPORTRANGE Google Sheets Syntax

As I guide you through how to use IMPORTRANGE in Google Sheets, it’s important to start with the structure of the function. Let’s break down the syntax of the IMPORTRANGE function to give you a better understanding of the components:

IMPORTRANGE(spreadsheet_url, range_string)

  • spreadsheet_url – The URL of the source spreadsheet (the Google Sheets spreadsheet) from which you want to import the data. This URL needs to be within double quotes. You can also have the URL in a cell and then use the cell reference.
  • range_string – The range of cells that you want to import. Note that this must be in the following format: “[sheet_name!]range”.
    • For example, if you want to import the cells A1:C10 from a sheet named Test, the format would be “Test!A1:C10”
    • If you do not specify the sheet name, the formula will assume that you must import the data from the first sheet of the Google Sheets document.
    • You can also have this text in a cell and use the cell reference as the second argument.

Ready for some practice? Let’s see an example of how to use this function in Google Sheets.

Quick Guide: IMPORTRANGE Google Sheets

I’ll dive into some specific examples to make it clearer. But the basics of using the IMPORTRANGE function in Google Sheets can be broken down into a few easy steps.

  1. Copy a URL from your browser address bar that you want to import
  2. Type =IMPORTRANGE( into an empty cell in your blank spreadsheet and paste the URL inside quotation marks
  3. Type a comma (,), then specify the range inside quotation marks eg, “Sheet1!B5:C20” and press “Enter
  4. Click the #REF error and click “Allow Access

Don’t quite understand? Let’s take a more in-depth look with some example data points.

Example 1: How To Use IMPORTRANGE in Google Sheets Using URLs or Spreadsheet Keys

Suppose you have different Google Sheets with students’ test scores in different subjects (i.e., one sheet for Math scores and one for English and so on).

If you want to combine all these sheets and have the data in the same sheet, you can use the IMPORTRANGE function. Just follow the steps below.

Step 1: Before you use the formula, you need to get the spreadsheet URL of the Google Sheets from where you want to import the data.

You can find the URL of the spreadsheet in the address bar of the browser when that Google Sheets document is open.

IMPORTRANGE Google Sheets - Copy the URL

Alternatively, you can also copy the short key from the URL instead of the full URL (as shown below). For this tutorial, I will use the short key in the formula.

IMPORTRANGE Function in Google Sheets - Short Key

Step 2: Type the function and paste the URL or short key to add the first argument to the syntax inside of double quotes.

IMPORTRANGE USL inside double quotes

Step 3: Type a comma (,) and specify the range to import in double quotes.

IMPORTRAGNE inside double quotes

Below is an example of the full IMPORTRANGE formula in Google Sheets that will allow you to import the names of the students and their scores into the current sheet:

=IMPORTRANGE("1G_XEiSnUu0o8kmbkNVlQgTIjoE5lGqErRK3TlNfR1oI","'Math Score'!A2:B10")

Note that the second argument of this formula is “‘Math Score’!A2:B10″. In this argument, you must specify the sheet name and the range (enclosed in double quotes).

Step 4: When you enter this formula for the first time, you will see the #REF! Error in the sheet. On hovering over the cell, you will see a prompt asking you to “Allow access.”

Allow access button in Google Sheets

Click on the green button, and it will give you the result. Note: This happens only once for a URL. Once you have allowed access, it will not ask for permission again.

Result of the IMPORT Range function in Google Sheets

This can be helpful if you’re trying to get the data from multiple sheets. For example, in this case, you can get the scores of all the subjects from different Google Sheets into a single single destination spreadsheet.

Example 2: Import a Range From Another Sheet Using Named Ranges

If you have to import ranges from several variables or sources, it can be easy to lose track of what’s what. In this case, you can name the ranges to make it easier. To do this, follow the process below:

Step 1: In the sheet you want to import from, highlight the range and right-click it, then navigate to “More cell actions” > “Define named range.”

Naming ranges example

Step 2: In the pop-up menu, name the range.

Named ranges pop up menu

Step 3: Use the Google Sheets import range function with the named range as the range_string in the new spreadsheet. In our example, it could be something like:

=IMPORTRANGE("1G_XEiSnUu0o8kmbkNVlQgTIjoE5lGqErRK3TlNfR1oI","'NamedRange1")
Import range example

Step 4: Press “Enter,” then Click the #REF error and click “Allow Access.”

Allow access button in Google Sheets

A Few Notes and Tips for Using IMPORTRANGE in Google Sheets

Once the sheets are connected (as you allowed access the first time you used the formula), any update done in the sheets will automatically be reflected as the result of the formula. For example, editing a student’s score on the original spreadsheet will automatically change the sheet with the formula.

When using the IMPORT RANGE formula, make sure enough empty cells are available to accommodate the result of the formula. If you have some data in a cell that overlaps with the cell needed for the formula result, the formula will return an error. However, it helps to identify the error by letting you know the issue when you hover over the cell.

Error in cell while using the IMPORTRANGE formula in Google Sheets

It may get a bit confusing if you’re importing the data from many sheets. It’s a good practice to create named ranges in the source sheet and then use the named range. For example, instead of ‘Math Score’!A2:B10, you can use ‘Math Score’!Data

If you expect data to be added to the source sheet, pull the entire column instead of pulling a specific range. For example, instead of ‘Math Score’!A2:B10, use ‘Math Score’!A:B. This will pull the data from all of the A and B columns. Adding more data to the source sheet will automatically be updated in the destination sheet.

What Can You Use IMPORTRANGE For?

  • Data Importation: Import data from one Google Sheets document to another.
  • Cross-Sheet References: Reference and import specific cell ranges between different sheets.
  • Real-time Updates: Automatically update imported data when the source changes.
  • Consolidating Information: Combine data from multiple sheets for analysis.
  • Collaborative Work: Collaborate by sharing and importing data between team members.
  • Dynamic Dashboards: Create dynamic summaries by pulling data from various sheets.
  • Centralized Reporting: Build centralized reports by aggregating data from different sources.
  • Selective Data Import: Import specific ranges instead of entire sheets.
  • Data Aggregation: Aggregate data from different sources for comprehensive analysis.
  • Educational and Training: Gather and consolidate student data for grading or assessment.

Related: How to Use IMPORTHTML in Google Sheets

Frequently Asked Questions

How Do I Use IMPORTRANGE in Google Sheets?

  1. Find the URL from your browser address bar to import
  2. Enter =IMPORTRANGE( into an empty cell and paste the URL inside quote marks
  3. Type a comma (,), then specify the range inside quotation marks eg, “Sheet2!B6:C18 and press enter
  4. Click the #REF error and click “Allow Access

What Is IMPORTRANGE in Google Sheets?

IMPORTRANGE is a function that allows you to bring data over from one spreadsheet to another without manually entering it. It is better than copy-pasting, as it automatically updates.

Does IMPORTRANGE Automatically Update Google Sheets?

Any changes to the initial sheet will change the imported data. That’s why we use IMPORTRANGE so frequently. It automatically updates connected sheets.

Is There a Limit on IMPORTRANGE in Google Sheets?

Yes, there can be a maximum of 50 cross-workbook reference formulas.

How Do I Filter IMPORTRANGE in Google Sheets?

You should use the QUERY function to filter data from imported data. We made a detailed guide on the subject.

How Do I Use Multiple IMPORTRANGES in Google Sheets?

Follow the standard procedure for importing a range, but make sure each time you use IMPORTRANGE, there is enough space in the sheet to display the data.

Can You IMPORTRANGE With Formatting?

No, you can only import the data itself. If you want the import to look like the old sheet, you’ll have to format data in your new sheet.

What Is the Best Alternative to IMPORTRANGE in Google Sheets?

You can use LOOKUP functions across worksheets instead of IMPORTRANGE.

Wrapping Up

I hope you have found this IMPORTRANGE Google Sheets guide helpful.  Now, you should be able to replicate data from one spreadsheet onto another, automatically update the imported data, and manipulate the original data as you please.

Interested in learning more about Google Sheets functions and formulas? Check out this online Udemy course! It covers basic and advanced functions, how to use them, and a step-by-step guide allowing you to transition from Microsoft to Google products easily.

Related:

Fact Checked By Cindy Wong

Popular Posts

3 thoughts on “An Easy & Comprehensive IMPORTRANGE Google Sheets Guide (2024)”

  1. I need to be able to use the IMPORTRANGE function to pull data from one spreadsheet to another, but place that data in a row below where the IMPORTRANGE function lives. The rows of data being pulled from another spreadsheet are moved from one sheet to another within the new spreadsheet based on an entry in a column using code in script editor. Everything works well until the row with the IMPORTRANGE function is moved – then the rest of the data below it disappears!

  2. i have successfully created a master spreadsheet, which consists of multiple tabs of import range spreadsheets. i need to be able to edit the master though, to make updates.
    also, i need to be notified when the individual imported sheets are edited.

    is there any way to receive notifications on the master (which consists of 40 tabs of import range sheets)?
    is there any way i can edit the master.
    appreciate any help you can offer.
    ~e

  3. Pull the below data from ‘Teacher Data’ File where condition is ‘Overall Performance is Below 50%’ by using importrange function?

Comments are closed.