IMPORTRANGE for other spreadsheets in Google Sheets - Step-by-Step Guide

Introduction

IMPORTRANGE lets you bring data from one Google Sheet into another automatically. This helps when you want to use or analyze data stored in a different spreadsheet without copying it manually.

When you want to see sales data from a shared spreadsheet inside your own report sheet

When your team updates a master list and you want your sheet to always show the latest version

When you manage multiple project sheets and want to combine key info into one summary sheet

When you want to avoid copying and pasting data that changes often

When you want to keep your data organized but still connected across files

Steps

Step 1: Open

- the Google Sheet where you want to import data

You see the sheet ready to receive data

Step 2: Click

- a cell where you want the imported data to start

The cell is selected and ready for formula input

Step 3: Type

- the formula bar

You start entering the IMPORTRANGE formula

💡 The formula looks like this: =IMPORTRANGE("spreadsheet_url", "range_string")

Step 4: Enter

- the full URL of the source spreadsheet inside quotes as the first argument

The formula knows which spreadsheet to get data from

Step 5: Enter

- the range of cells to import as the second argument, in quotes, like "Sheet1!A1:C10"

The formula knows which cells to bring in

Step 6: Press

- Enter key

The formula runs and shows #REF! with a prompt to allow access if this is the first time

Step 7: Click

- Allow access button that appears in the cell

The data from the other spreadsheet appears in your sheet

Before vs After

Before

Cell A1 is empty in your current sheet

After

Cell A1 and following cells show data imported from the other spreadsheet's specified range

Settings Reference

Spreadsheet URL

📍 First argument of IMPORTRANGE formula

Tells IMPORTRANGE which spreadsheet to pull data from

Default: None

Range string

📍 Second argument of IMPORTRANGE formula

Specifies which cells to import from the source spreadsheet

Default: None

Access permission

📍 Prompt shown in the cell after first use

Grants permission for your sheet to read data from the other spreadsheet

Default: No access until allowed

Common Mistakes

Not putting the spreadsheet URL inside quotes

IMPORTRANGE requires the URL as a text string, so missing quotes causes an error

Always put the URL inside double quotes, like "https://docs.google.com/spreadsheets/d/abc123..."

Using a wrong or misspelled sheet name in the range string

IMPORTRANGE cannot find the range and returns an error if the sheet name does not exist

Check the exact sheet name in the source spreadsheet and type it exactly, including spaces

Not clicking Allow access when prompted

Without permission, IMPORTRANGE cannot show data and will keep showing #REF! error

Click the Allow access button in the cell to grant permission for data import

Summary

IMPORTRANGE imports data from another Google Sheet using its URL and a cell range.

You must allow access permission the first time you use IMPORTRANGE with a new spreadsheet.

Make sure to use correct quotes and exact sheet names to avoid errors.

Practice

1. What does the IMPORTRANGE function do in Google Sheets?

easy

A. It imports data from one Google Sheet to another using a URL and range.

B. It exports data from Google Sheets to a CSV file.

C. It creates a chart based on selected data.

D. It sorts data within the same sheet.

Solution

1. Step 1: Understand the purpose of IMPORTRANGE

   The IMPORTRANGE function is designed to pull data from a different Google Sheet using its URL or key and a specified range.

2. Step 2: Compare options with the function's purpose

   Only It imports data from one Google Sheet to another using a URL and range. correctly describes this behavior. Options A, B, and D describe unrelated functions.

3. Final Answer:

   It imports data from one Google Sheet to another using a URL and range. -> Option A

4. Quick Check:

   IMPORTRANGE imports data between sheets [OK]

Hint: Remember: IMPORTRANGE pulls data from another sheet [OK]

Common Mistakes:

• Confusing IMPORTRANGE with export functions
• Thinking it sorts or charts data
• Assuming it works within the same sheet only

2. Which of the following is the correct syntax to import cells A1 to B5 from another spreadsheet using IMPORTRANGE?

easy

A. =IMPORTRANGE(spreadsheet_url, "A1:B5")

B. =IMPORTRANGE(spreadsheet_url, A1:B5)

C. =IMPORTRANGE("spreadsheet_url", A1:B5)

D. =IMPORTRANGE("spreadsheet_url", "A1:B5")

Solution

1. Step 1: Check the syntax requirements for IMPORTRANGE

   The first argument must be a string with the spreadsheet URL or key in quotes. The second argument must be a string with the range in quotes.

2. Step 2: Identify the correct option

   =IMPORTRANGE("spreadsheet_url", "A1:B5") correctly uses quotes around both arguments. Options A and B miss quotes around the URL or range, and C misses quotes around the range.

3. Final Answer:

   =IMPORTRANGE("spreadsheet_url", "A1:B5") -> Option D

4. Quick Check:

   Both URL and range must be quoted strings [OK]

Hint: Always put URL and range inside quotes [OK]

Common Mistakes:

• Forgetting quotes around URL or range
• Using cell references without quotes
• Using commas inside range string

3. Given the formula =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!C2:C4"), what will be the output if Sheet1 cells C2, C3, and C4 contain 10, 20, and 30 respectively?

medium

A. 10

B. [10, 20, 30]

C. Error: Access denied

D. 30

Solution

1. Step 1: Understand the range and data

   The range "Sheet1!C2:C4" includes three cells with values 10, 20, and 30.

2. Step 2: Determine IMPORTRANGE output

   IMPORTRANGE imports the entire range as an array of values, so it returns [10, 20, 30].

3. Final Answer:

   [10, 20, 30] -> Option B

4. Quick Check:

   IMPORTRANGE returns all cells in the range [OK]

Hint: IMPORTRANGE returns all cells in the specified range [OK]

Common Mistakes:

• Expecting only the first cell value
• Confusing output with error messages
• Not recognizing array output

4. You entered =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sheet1!A1:A3") but see a #REF! error. What is the most likely fix?

medium

A. Click the cell and allow access to the other spreadsheet.

B. Change the range to "A1:A2".

C. Remove quotes around the URL.

D. Use a different function like VLOOKUP.

Solution

1. Step 1: Understand #REF! error with IMPORTRANGE

   #REF! often appears when access permission to the source spreadsheet is not granted yet.

2. Step 2: Identify the fix

   Clicking the cell usually prompts a permission request to allow access. Granting access fixes the error.

3. Final Answer:

   Click the cell and allow access to the other spreadsheet. -> Option A

4. Quick Check:

   Grant access to fix #REF! error [OK]

Hint: Grant permission when #REF! appears with IMPORTRANGE [OK]

Common Mistakes:

• Changing range unnecessarily
• Removing quotes causing syntax errors
• Switching functions without reason

5. You want to import the range B2:D4 from a spreadsheet with URL https://docs.google.com/spreadsheets/d/xyz789 but only if the values in column B are greater than 50. Which formula correctly combines IMPORTRANGE and FILTER to do this?

hard

A. =IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4") > 50

B. =IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", FILTER("Sheet1!B2:D4", "Sheet1!B2:B4" > 50))

C. =FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50)

D. =FILTER("Sheet1!B2:D4", IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50)

Solution

1. Step 1: Understand combining IMPORTRANGE and FILTER

   IMPORTRANGE imports data, FILTER filters rows based on a condition. We must import the full range and separately import the column to filter on.

2. Step 2: Analyze each option

   =FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50) imports the full range and filters rows where column B values are > 50 using a second IMPORTRANGE call for column B. This is correct.
   =IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", FILTER("Sheet1!B2:D4", "Sheet1!B2:B4" > 50)) incorrectly tries to use FILTER inside IMPORTRANGE, which is invalid.
   =IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4") > 50 compares the entire imported range to 50, which is invalid.
   =FILTER("Sheet1!B2:D4", IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50) tries to filter a string range without importing it first, which is invalid.

3. Final Answer:

   =FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:D4"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/xyz789", "Sheet1!B2:B4") > 50) -> Option C

4. Quick Check:

   Use FILTER on IMPORTRANGE data with condition on imported column [OK]

Hint: Use FILTER on IMPORTRANGE twice: full range and condition column [OK]

Common Mistakes:

• Trying to FILTER inside IMPORTRANGE
• Comparing entire range directly to a number
• Filtering a range string without importing