# IMPORTRANGE in Google Sheets: pull data from another spreadsheet

> IMPORTRANGE copies a range from another Google Sheets spreadsheet into the one you are working in, and keeps it up to date.

Source: https://spreadsheetpractice.com/google-sheets/importrange · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** IMPORTRANGE copies a range from another Google Sheets spreadsheet into the one you are working in, and keeps it up to date.
- **Syntax:** `=IMPORTRANGE(spreadsheet_url, range_string)`
- **Returns:** The values of the range, spilling down and to the right from the formula cell. Formatting does not come across, only values.
- **In Excel:** Excel has no IMPORTRANGE. To pull data from another workbook, use an external reference such as ='[Sales.xlsx]Orders'!A1, or load it with Power Query (Data > Get Data) and refresh it.

IMPORTRANGE copies a range from another Google Sheets spreadsheet into the one you are working in, and keeps it up to date. You give it the other file's URL (or just its key) and the range as text. The first time two files are linked, someone has to click Allow access.

## Syntax

`=IMPORTRANGE(spreadsheet_url, range_string)`

| Argument | Required? | What it is |
|---|---|---|
| `spreadsheet_url` | Required | The other spreadsheet's full URL, or just its key: the long ID between /d/ and /edit in the URL. Put it in double quotes, or point at a cell that holds it. |
| `range_string` | Required | The range to import, as text in double quotes, such as "Orders!A1:F13". Add the tab name and an exclamation mark; put single quotes around tab names with spaces, as in "'Q3 Orders'!A1:F". Without a tab name, IMPORTRANGE reads the first tab. |

## The source spreadsheets

### Regional sales 2026

URL: `https://docs.google.com/spreadsheets/d/1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz/edit`

#### Tab Orders

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Order | Rep | Region | Product | Units | Revenue |
| 2 | S-1001 | Ana Ruiz | East | Widget | 40 | 2080 |
| 3 | S-1002 | Raj Patel | West | Gadget | 25 | 2225 |
| 4 | S-1003 | Lee Chen | East | Gizmo | 12 | 1740 |
| 5 | S-1004 | Ana Ruiz | North | Gadget | 30 | 2670 |
| 6 | S-1005 | Kim Ortiz | West | Widget | 55 | 2860 |
| 7 | S-1006 | Raj Patel | East | Widget | 18 | 936 |
| 8 | S-1007 | Lee Chen | South | Gadget | 22 | 1958 |
| 9 | S-1008 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| 10 | S-1009 | Ana Ruiz | West | Widget | 35 | 1820 |
| 11 | S-1010 | Mo Diaz | North | Gizmo | 14 | 2030 |
| 12 | S-1011 | Raj Patel | South | Widget | 20 | 1040 |
| 13 | S-1012 | Kim Ortiz | North | Gadget | 16 | 1424 |

#### Tab Q3 Orders

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Order | Rep | Region | Product | Units | Revenue |
| 2 | S-1013 | Mo Diaz | East | Gizmo | 28 | 4060 |
| 3 | S-1014 | Ana Ruiz | West | Gadget | 19 | 1691 |
| 4 | S-1015 | Lee Chen | North | Widget | 44 | 2288 |
| 5 | S-1016 | Kim Ortiz | South | Gizmo | 11 | 1595 |
| 6 | S-1017 | Raj Patel | West | Widget | 27 | 1404 |

### Price list

URL: `https://docs.google.com/spreadsheets/d/1Fp3XqL8rT2vN6yB0cK4wM9hZ5jD7sG1aEuRoVi8/edit`

#### Tab Prices

|  | A | B | C |
|---|---|---|---|
| 1 | SKU | Item | Price |
| 2 | TB-110 | Desk lamp | 39 |
| 3 | TB-220 | Monitor arm | 89 |
| 4 | TB-330 | Laptop stand | 54 |
| 5 | TB-440 | USB hub | 29 |
| 6 | TB-550 | Desk mat | 24 |
| 7 | TB-660 | Webcam | 72 |
| 8 | TB-770 | Cable tray | 18 |

## The sample data

Every example below runs on this sheet; results are calculated by the formula engine in Google Sheets mode.

|  | A | B |
|---|---|---|
| 1 | Look up SKU | TB-220 |
| 2 | Sales file | https://docs.google.com/spreadsheets/d/1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz/edit |
| 3 | Region | West |

## The syntax: a file and a range

IMPORTRANGE takes two pieces of text: which spreadsheet, and which range in it. For the spreadsheet, paste its full URL from the address bar. Both arguments go in double quotes.

You can also give just the key, the long ID between `/d/` and `/edit` in the URL. It does the same thing and keeps the formula shorter. Or keep the URL in a cell and point at the cell, so several formulas share it.

`=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz/edit","Orders!A1:F13")`

The whole Orders tab of the sales file, header row included, using the full URL

Result:

| Order | Rep | Region | Product | Units | Revenue |
|---|---|---|---|---|---|
| S-1001 | Ana Ruiz | East | Widget | 40 | 2080 |
| S-1002 | Raj Patel | West | Gadget | 25 | 2225 |
| S-1003 | Lee Chen | East | Gizmo | 12 | 1740 |
| S-1004 | Ana Ruiz | North | Gadget | 30 | 2670 |
| S-1005 | Kim Ortiz | West | Widget | 55 | 2860 |
| S-1006 | Raj Patel | East | Widget | 18 | 936 |
| S-1007 | Lee Chen | South | Gadget | 22 | 1958 |
| S-1008 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| S-1009 | Ana Ruiz | West | Widget | 35 | 1820 |
| S-1010 | Mo Diaz | North | Gizmo | 14 | 2030 |
| S-1011 | Raj Patel | South | Widget | 20 | 1040 |
| S-1012 | Kim Ortiz | North | Gadget | 16 | 1424 |

`=IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!A1:C5")`

Just the key works too: the first three columns of the first four orders

Result:

| Column 1 | Column 2 | Column 3 |
|---|---|---|
| Order | Rep | Region |
| S-1001 | Ana Ruiz | East |
| S-1002 | Raj Patel | West |
| S-1003 | Lee Chen | East |
| S-1004 | Ana Ruiz | North |

`=IMPORTRANGE(B2,"Orders!B1:B5")`

The URL lives in B2 of this sheet, so the formula points at it instead of repeating it

Result:

| Column 1 |
|---|
| Rep |
| Ana Ruiz |
| Raj Patel |
| Lee Chen |
| Ana Ruiz |

## Tab names, and when to quote them

Put the tab name in front of the range with an exclamation mark: `"Orders!A1:F13"`. If the name has a space or other punctuation, wrap it in single quotes inside the double quotes: `"'Q3 Orders'!A1:F6"`.

Leave the tab name out and IMPORTRANGE reads the first tab in the file. That works until someone reorders the tabs, so naming the tab is safer.

`=IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","'Q3 Orders'!A1:F6")`

A tab name with a space needs single quotes around it

Result:

| Order | Rep | Region | Product | Units | Revenue |
|---|---|---|---|---|---|
| S-1013 | Mo Diaz | East | Gizmo | 28 | 4060 |
| S-1014 | Ana Ruiz | West | Gadget | 19 | 1691 |
| S-1015 | Lee Chen | North | Widget | 44 | 2288 |
| S-1016 | Kim Ortiz | South | Gizmo | 11 | 1595 |
| S-1017 | Raj Patel | West | Widget | 27 | 1404 |

`=IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","A1:D4")`

No tab name: the range comes from the first tab, here Orders

Result:

| Column 1 | Column 2 | Column 3 | Column 4 |
|---|---|---|---|
| Order | Rep | Region | Product |
| S-1001 | Ana Ruiz | East | Widget |
| S-1002 | Raj Patel | West | Gadget |
| S-1003 | Lee Chen | East | Gizmo |

## Ranges that keep growing

A range like `A1:F13` stops at row 13, so new orders added below it never arrive. Leave the row number off the end, as in `Orders!A1:F`, and the import runs to the bottom of the tab. Whole columns such as `Orders!A:C` work the same way.

An open range also brings in the blank rows at the bottom of the tab, so it spills further than the data. When you feed it to QUERY, add `where Col1 is not null` to drop them.

`=IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!A1:F")`

Every row of Orders, including any added later

Result:

| Order | Rep | Region | Product | Units | Revenue |
|---|---|---|---|---|---|
| S-1001 | Ana Ruiz | East | Widget | 40 | 2080 |
| S-1002 | Raj Patel | West | Gadget | 25 | 2225 |
| S-1003 | Lee Chen | East | Gizmo | 12 | 1740 |
| S-1004 | Ana Ruiz | North | Gadget | 30 | 2670 |
| S-1005 | Kim Ortiz | West | Widget | 55 | 2860 |
| S-1006 | Raj Patel | East | Widget | 18 | 936 |
| S-1007 | Lee Chen | South | Gadget | 22 | 1958 |
| S-1008 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| S-1009 | Ana Ruiz | West | Widget | 35 | 1820 |
| S-1010 | Mo Diaz | North | Gizmo | 14 | 2030 |
| S-1011 | Raj Patel | South | Widget | 20 | 1040 |
| S-1012 | Kim Ortiz | North | Gadget | 16 | 1424 |

`=IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!B:C")`

Two whole columns: rep and region

Result:

| Column 1 | Column 2 |
|---|---|
| Rep | Region |
| Ana Ruiz | East |
| Raj Patel | West |
| Lee Chen | East |
| Ana Ruiz | North |
| Kim Ortiz | West |
| Raj Patel | East |
| Lee Chen | South |
| Kim Ortiz | East |
| Ana Ruiz | West |
| Mo Diaz | North |
| Raj Patel | South |
| Kim Ortiz | North |

## Connecting the files: Allow access

The first time a spreadsheet imports from another one, the formula shows `#REF!`. Hover over the cell and you'll see "You need to connect these sheets" with an **Allow access** button. Click it and the data appears.

To connect them you need to be able to open the source file. You only connect each pair of files once: after that, any editor of your spreadsheet can import any range from the source, and anyone who can see your spreadsheet sees the imported values, even if they can't open the source. Share the destination file with that in mind.

In the practice sheets on this page you'll see the same `#REF!` and an Allow access button before the import fills in.

## Combine several tabs or files

To stack imports on top of each other, put them in braces with a semicolon between them. They need the same number of columns. Leave the header row out of the second one so it appears only once.

A comma instead of a semicolon puts the imports side by side, which needs the same number of rows.

`={IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!A1:F13");IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","'Q3 Orders'!A2:F6")}`

Orders and Q3 Orders in one list, with one header row on top

Result:

| Order | Rep | Region | Product | Units | Revenue |
|---|---|---|---|---|---|
| S-1001 | Ana Ruiz | East | Widget | 40 | 2080 |
| S-1002 | Raj Patel | West | Gadget | 25 | 2225 |
| S-1003 | Lee Chen | East | Gizmo | 12 | 1740 |
| S-1004 | Ana Ruiz | North | Gadget | 30 | 2670 |
| S-1005 | Kim Ortiz | West | Widget | 55 | 2860 |
| S-1006 | Raj Patel | East | Widget | 18 | 936 |
| S-1007 | Lee Chen | South | Gadget | 22 | 1958 |
| S-1008 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| S-1009 | Ana Ruiz | West | Widget | 35 | 1820 |
| S-1010 | Mo Diaz | North | Gizmo | 14 | 2030 |
| S-1011 | Raj Patel | South | Widget | 20 | 1040 |
| S-1012 | Kim Ortiz | North | Gadget | 16 | 1424 |
| S-1013 | Mo Diaz | East | Gizmo | 28 | 4060 |
| S-1014 | Ana Ruiz | West | Gadget | 19 | 1691 |
| S-1015 | Lee Chen | North | Widget | 44 | 2288 |
| S-1016 | Kim Ortiz | South | Gizmo | 11 | 1595 |
| S-1017 | Raj Patel | West | Widget | 27 | 1404 |

## QUERY on imported data: Col1, Col2

IMPORTRANGE and QUERY are often used together: import the raw data, then pick, filter and total it in one formula. Because the imported data is an array, not a range in this sheet, QUERY refers to its columns by position: `Col1`, `Col2` and so on, with a capital C. Column letters give `#VALUE!` here.

The text you compare against still goes in single quotes, and you can splice in a cell, as in `"where Col3 = '"&B3&"'"`.

`=QUERY(IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!A1:F13"),"select Col2, Col4, Col6 where Col3 = 'West'",1)`

Rep, product and revenue for the West orders

Result:

| Rep | Product | Revenue |
|---|---|---|
| Raj Patel | Gadget | 2225 |
| Kim Ortiz | Widget | 2860 |
| Ana Ruiz | Widget | 1820 |

`=QUERY(IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!A1:F"),"select Col3, sum(Col6) where Col1 is not null group by Col3 order by sum(Col6) desc label sum(Col6) 'Revenue'",1)`

Revenue by region from an open range; is not null drops the blank rows

Result:

| Region | Revenue |
|---|---|
| West | 6905 |
| North | 6124 |
| East | 6061 |
| South | 2998 |

`=QUERY(IMPORTRANGE(B2,"Orders!A1:F13"),"select Col1, Col2, Col6 where Col3 = '"&B3&"' order by Col6 desc",1)`

The region comes from B3, so changing it changes the result

Result:

| Order | Rep | Revenue |
|---|---|---|
| S-1005 | Kim Ortiz | 2860 |
| S-1002 | Raj Patel | 2225 |
| S-1009 | Ana Ruiz | 1820 |

## FILTER and SORT on an import

SORT works on an import directly. FILTER needs the data and a condition that are the same height, so import the condition column as well.

Each IMPORTRANGE is its own formula reading the other file. When you need the same data several times, it is often simpler to import it once into a tab of your own and point the other formulas at that.

`=SORT(IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!A2:F13"),6,FALSE)`

Every order, highest revenue first. The header row is left out so it isn't sorted into the data

Result:

| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 |
|---|---|---|---|---|---|
| S-1005 | Kim Ortiz | West | Widget | 55 | 2860 |
| S-1004 | Ana Ruiz | North | Gadget | 30 | 2670 |
| S-1002 | Raj Patel | West | Gadget | 25 | 2225 |
| S-1001 | Ana Ruiz | East | Widget | 40 | 2080 |
| S-1010 | Mo Diaz | North | Gizmo | 14 | 2030 |
| S-1007 | Lee Chen | South | Gadget | 22 | 1958 |
| S-1009 | Ana Ruiz | West | Widget | 35 | 1820 |
| S-1003 | Lee Chen | East | Gizmo | 12 | 1740 |
| S-1012 | Kim Ortiz | North | Gadget | 16 | 1424 |
| S-1008 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| S-1011 | Raj Patel | South | Widget | 20 | 1040 |
| S-1006 | Raj Patel | East | Widget | 18 | 936 |

`=FILTER(IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!A2:B13"),IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!C2:C13")="East")`

Order and rep for the East orders: the region column is imported to test against

Result:

| Column 1 | Column 2 |
|---|---|
| S-1001 | Ana Ruiz |
| S-1003 | Lee Chen |
| S-1006 | Raj Patel |
| S-1008 | Kim Ortiz |

## Look up a value in another file

IMPORTRANGE can sit inside a lookup, so the table you search lives in a different spreadsheet. With VLOOKUP, import the whole table. With XLOOKUP, import the column to search and the column to return separately.

`=VLOOKUP(B1,IMPORTRANGE("1Fp3XqL8rT2vN6yB0cK4wM9hZ5jD7sG1aEuRoVi8","Prices!A2:C8"),3,FALSE)`

The price of the SKU in B1, from the price list file

Result:

`89`

`=XLOOKUP(B1,IMPORTRANGE("1Fp3XqL8rT2vN6yB0cK4wM9hZ5jD7sG1aEuRoVi8","Prices!A2:A"),IMPORTRANGE("1Fp3XqL8rT2vN6yB0cK4wM9hZ5jD7sG1aEuRoVi8","Prices!B2:B"),"Not found")`

The item name for the same SKU, with a fallback if it's missing

Result:

`Monitor arm`

## Keep imports quick

Large imports and many IMPORTRANGE formulas can make a spreadsheet slow to load, and the cells show Loading… while they fetch. Import only the columns and rows you need rather than whole tabs, and import a table once instead of in many small pieces.

IMPORTRANGE brings values, not formatting. Currency signs, colors and number formats stay behind, and dates can arrive as plain numbers until you format the column as a date.

`=IMPORTRANGE("1nV7cR2kQ8wZpL4tY6bH9sJ3mX0dF5gAeTqUKo2Pz","Orders!B1:C13")`

Two columns instead of the whole tab: less to fetch, and nothing you don't use

Result:

| Column 1 | Column 2 |
|---|---|
| Rep | Region |
| Ana Ruiz | East |
| Raj Patel | West |
| Lee Chen | East |
| Ana Ruiz | North |
| Kim Ortiz | West |
| Raj Patel | East |
| Lee Chen | South |
| Kim Ortiz | East |
| Ana Ruiz | West |
| Mo Diaz | North |
| Raj Patel | South |
| Kim Ortiz | North |

## IMPORTRANGE cheat sheet

| Task | Formula | Note |
|---|---|---|
| Import a range | `=IMPORTRANGE("https://docs.google.com/spreadsheets/d/KEY/edit","Orders!A1:F50")` | Both arguments are text in double quotes |
| Use just the key | `=IMPORTRANGE("KEY","Orders!A1:F50")` | The key is the ID between /d/ and /edit |
| Keep the URL in a cell | `=IMPORTRANGE(B1,"Orders!A1:F50")` | B1 holds the URL or key |
| Tab name with spaces | `=IMPORTRANGE("KEY","'Q3 Orders'!A1:F")` | Single quotes inside the double quotes |
| First tab | `=IMPORTRANGE("KEY","A1:F50")` | No tab name reads the first tab |
| Grow with the data | `=IMPORTRANGE("KEY","Orders!A1:F")` | No end row: runs to the bottom of the tab |
| Stack two imports | `={IMPORTRANGE("KEY","Orders!A1:F");IMPORTRANGE("KEY","Q3!A2:F")}` | Same number of columns; skip the second header |
| Query the import | `=QUERY(IMPORTRANGE("KEY","Orders!A1:F"),"select Col2, Col6 where Col6 > 1000",1)` | Col1, Col2… not letters |
| Total a column | `=SUM(IMPORTRANGE("KEY","Orders!F2:F"))` | Any function that takes a range takes an import |
| Look up in another file | `=XLOOKUP(A2,IMPORTRANGE("KEY","Prices!A2:A"),IMPORTRANGE("KEY","Prices!C2:C"))` | Import the search column and the result column |

## Common IMPORTRANGE errors

| What you see | Why, and how to fix it |
|---|---|
| #REF! "You need to connect these sheets" | The two files aren't connected yet. Hover over the cell and click Allow access. It only has to be done once for each pair of files. |
| #REF! saying you don't have permission | Your account can't open the source file. Ask its owner to share it with you, at least as a viewer, then click Allow access. |
| #REF! even though the file is connected | Usually the range string. Check the tab name is spelled exactly as in the other file, that names with spaces have single quotes around them, and that the exclamation mark is there. |
| Formula parse error | Both arguments are text, so each needs double quotes: =IMPORTRANGE("KEY","Orders!A1:F"). A missing quote, or a range typed without quotes, breaks the whole formula. |
| #VALUE! from QUERY around IMPORTRANGE | QUERY can't use column letters on imported data. Use Col1, Col2 and so on, counting from the first imported column, with a capital C. |
| #REF! "Array result was not expanded" | The import needs empty cells below and to the right of the formula. Clear whatever is in the way, or move the formula. |
| Numbers or dates look wrong | IMPORTRANGE brings values without their formatting. Format the destination columns as currency, percent or date yourself. |

## Questions

### Does IMPORTRANGE update when the other file changes?

Yes. Edits in the source show up in the import on their own, though it can take a short while. You don't need to reopen either file.

### Can people see the imported data if they can't open the source file?

Yes. Once the files are connected, the imported values are part of your spreadsheet, so anyone who can view your spreadsheet sees them. Import only what everyone with access to it should see.

### Can I import from an Excel file in Google Drive?

IMPORTRANGE reads Google Sheets files. Open the .xlsx in Sheets and save it as a Google Sheets file first, then import from that copy.

### How do I import from several files at once?

Stack the imports in braces with semicolons, ={IMPORTRANGE("KEY1","Data!A1:D");IMPORTRANGE("KEY2","Data!A2:D")}, as long as they have the same number of columns. Each file has to be connected once.

### Why does QUERY say #VALUE! when I use column letters?

Imported data isn't a range in your sheet, so it has no column letters. Refer to the columns as Col1, Col2, Col3 in the order they were imported.

### IMPORTRANGE or copy and paste?

Copy and paste gives you a snapshot that goes stale. IMPORTRANGE stays linked, so a report built on it keeps up with the source without anyone pasting again.

## Practice

3 graded IMPORTRANGE exercises are on [the IMPORTRANGE guide](https://spreadsheetpractice.com/google-sheets/importrange), checked in Google Sheets mode and re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
