IMPORTRANGE in Google Sheets: Sync Data Across Spreadsheets

IMPORTRANGE is one of Google Sheets' most powerful built-in functions. It pulls data from one spreadsheet into another, keeping everything in sync without manual copy-pasting. If you manage data across multiple spreadsheets — budgets, sales reports, inventory trackers — this function will save you hours every week.

This article covers everything from basic syntax to advanced combinations with QUERY, FILTER, and VLOOKUP. You'll also learn how to troubleshoot every common error and work around the function's limitations.

What Is IMPORTRANGE?

IMPORTRANGE imports a range of cells from one Google Sheets spreadsheet into another. The data stays connected — when the source spreadsheet updates, the destination updates too (usually within a few minutes).

This matters for three reasons:

• Data consolidation — Combine regional reports, team trackers, or department budgets into a single master sheet.
• Access control — Share a summary spreadsheet without giving everyone access to the raw data.
• Real-time sync — Changes in the source propagate automatically. No more emailing CSV exports.

The basic syntax is straightforward:

=IMPORTRANGE(spreadsheet_url, range_string)

• spreadsheet_url — The full URL of the source spreadsheet (or just the spreadsheet key/ID).
• range_string — The sheet name and cell range to import, written as a string like "Sheet1!A1:D100".

Both parameters must be enclosed in quotes (or reference cells that contain these values). Here's a real example:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ/edit", "Sales!A1:E50")

This pulls cells A1 through E50 from the "Sales" tab of the specified spreadsheet.

How to Use IMPORTRANGE Step by Step

Basic Import from Another Spreadsheet

Let's walk through a concrete scenario. You have a spreadsheet called "North Region Sales" and you want to pull its data into a "Company Dashboard" spreadsheet.

1. Open the source spreadsheet ("North Region Sales") and copy the full URL from your browser's address bar.
2. Open your destination spreadsheet ("Company Dashboard").
3. Click into the cell where you want the imported data to start (e.g., cell A1).
4. Enter the formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ/edit", "Sheet1!A1:E50")

5. Press Enter. You'll see a #REF! error the first time — this is expected.
6. Hover over the error cell. A tooltip appears saying "You need to connect these sheets." Click "Allow access".
7. The data loads. The imported range spills across multiple cells starting from your formula cell.

The "Allow access" permission is a one-time step per source-destination spreadsheet pair. Once you grant it, any IMPORTRANGE formula in the destination spreadsheet can pull data from that source without asking again. The permission persists even if you delete the original formula.

Tip: You can use just the spreadsheet key instead of the full URL. The key is the long string between /d/ and /edit in the URL:

=IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sheet1!A1:E50")

Specifying Sheet Names and Ranges

The range_string parameter follows the same conventions as standard Google Sheets range references, but wrapped in quotes.

Standard sheet name:

=IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sheet1!A1:D10")

Sheet name with spaces: Use single quotes around the sheet name inside the double-quoted string.

=IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "'North Region'!A1:D10")

Entire columns: Omit row numbers to import full columns. Useful when the source data grows over time.

=IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sheet1!A:D")

Named ranges: If the source spreadsheet has a named range (e.g., "MonthlySales"), you can reference it directly — no sheet name or cell references needed.

=IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "MonthlySales")

Named ranges are the cleanest approach for IMPORTRANGE. If someone inserts columns or moves data around in the source sheet, a named range adjusts automatically. Hardcoded cell references like A1:D10 don't.

Importing from Multiple Sheets and Spreadsheets

Stacking Multiple Ranges (Curly Braces)

Google Sheets lets you combine arrays using curly braces ({ }). A semicolon (;) stacks ranges vertically (rows). A comma (,) places them side by side (columns).

Vertical stacking from different tabs in the same spreadsheet:

={IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Q1!A2:E100");
  IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Q2!A2:E100")}

This stacks Q1 data on top of Q2 data. Notice we start from row 2 (A2) to skip headers in the second range — otherwise you'd get duplicate header rows.

Vertical stacking from different spreadsheets:

={IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sheet1!A1:E100");
  IMPORTRANGE("2xYzAbCdEfGhIjKlMnOpQrStUv", "Sheet1!A2:E100");
  IMPORTRANGE("3mNoPqRsTuVwXyZaBcDeFgHiJkL", "Sheet1!A2:E100")}

This pulls data from three separate spreadsheets and stacks them into one continuous dataset. The first range includes headers (row 1); the other two skip them.

Important: All ranges being stacked must have the same number of columns. If one range has 5 columns and another has 4, you'll get an error.

Combining Data from Multiple Sources

Here's a practical consolidation pattern. Imagine your company has four regional sales spreadsheets, each with the same structure: columns for Date, Product, Units Sold, Revenue, and Region.

In your master "Company Dashboard" spreadsheet, create a tab called "All Sales" and enter:

={IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "'North Sales'!A1:E500");
  IMPORTRANGE("2xYzAbCdEfGhIjKlMnOpQrStUv", "'South Sales'!A2:E500");
  IMPORTRANGE("3mNoPqRsTuVwXyZaBcDeFgHiJkL", "'East Sales'!A2:E500");
  IMPORTRANGE("4pQrStUvWxYzAbCdEfGhIjKlMnO", "'West Sales'!A2:E500")}

Now you have a unified dataset you can pivot, chart, or query — all updating automatically as regional teams enter new data.

Performance note: Each IMPORTRANGE call in the curly braces is a separate network request. Four calls is fine. Forty calls will make your spreadsheet sluggish. If you're consolidating more than 5-6 sources, consider using an intermediate aggregation spreadsheet or a tool like Unlimited Sheets for more efficient data pipelines.

Powerful Combinations with Other Functions

IMPORTRANGE on its own imports raw data. Wrapping it with QUERY, FILTER, or VLOOKUP lets you transform, filter, and look up data on the fly — no intermediate sheets required.

QUERY + IMPORTRANGE

The QUERY function uses a SQL-like syntax to filter, sort, and aggregate data. Combined with IMPORTRANGE, it's the most powerful way to work with external data in Google Sheets.

=QUERY(IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sales!A1:E500"),
       "SELECT Col1, Col2, Col4 WHERE Col4 > 1000 ORDER BY Col4 DESC")

This imports the Sales sheet and then filters it to show only rows where column 4 (Revenue) exceeds 1000, sorted from highest to lowest.

Critical syntax detail: When QUERY wraps IMPORTRANGE, you can't use column letters (A, B, C). You must use Col1, Col2, Col3, etc. That's because IMPORTRANGE returns an array, not a sheet range — arrays don't have column letters.

More examples:

// Filter by text match
=QUERY(IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sales!A1:E500"),
       "SELECT * WHERE Col5 = 'North'")

// Aggregate: total revenue by region
=QUERY(IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sales!A1:E500"),
       "SELECT Col5, SUM(Col4) GROUP BY Col5 LABEL SUM(Col4) 'Total Revenue'")

// Top 10 products by units sold
=QUERY(IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sales!A1:E500"),
       "SELECT Col2, Col3 ORDER BY Col3 DESC LIMIT 10")

The QUERY + IMPORTRANGE combination is particularly useful when the source spreadsheet has thousands of rows but you only need a subset. Instead of importing everything and filtering locally, the QUERY reduces the data before it lands in your sheet.

FILTER + IMPORTRANGE

FILTER is simpler than QUERY and works well for straightforward row-level filtering. It uses native Sheets syntax instead of SQL-like strings.

=FILTER(IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sales!A2:E500"),
        IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sales!D2:D500") > 1000)

This returns all rows where the value in column D exceeds 1000. Note that you need a second IMPORTRANGE call for the condition column — FILTER needs a separate reference for the condition array.

To avoid the double IMPORTRANGE call (which doubles the network requests), a cleaner approach is to import the data first into a helper range and then filter locally:

// Cell A1 of a helper tab:
=IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sales!A1:E500")

// Then in your analysis tab:
=FILTER(Helper!A2:E500, Helper!D2:D500 > 1000)

VLOOKUP + IMPORTRANGE

VLOOKUP lets you search for a value in the first column of a range and return a value from another column. With IMPORTRANGE, you can look up values across spreadsheets.

=VLOOKUP(A2, IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Products!A:C"), 3, FALSE)

This looks up the value in cell A2 within the Products sheet of another spreadsheet, and returns the value from column 3 (e.g., the product price). The FALSE parameter ensures an exact match.

Practical scenario: Your order tracking spreadsheet has product IDs but no product names or prices. Those live in a separate product catalog spreadsheet. Use VLOOKUP + IMPORTRANGE to enrich your order data:

// In column B, get the product name:
=VLOOKUP(A2, IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Catalog!A:D"), 2, FALSE)

// In column C, get the unit price:
=VLOOKUP(A2, IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Catalog!A:D"), 4, FALSE)

Performance tip: If you're running VLOOKUP + IMPORTRANGE on hundreds of rows, each row triggers a separate import. This will be slow. Instead, import the lookup table once into a helper tab and VLOOKUP against that local copy.

IFERROR + IMPORTRANGE

IMPORTRANGE can fail for many reasons — permissions revoked, source spreadsheet deleted, network issues. Wrapping it in IFERROR provides a graceful fallback.

=IFERROR(IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Sales!A1:E50"), "Data unavailable")

If the import fails for any reason, the cell shows "Data unavailable" instead of an ugly error. You can also use an empty string ("") for a cleaner look, or a number like 0 if downstream formulas expect numeric input.

IFERROR is especially useful in dashboards that pull from multiple sources. One broken source won't break the entire dashboard.

Troubleshooting IMPORTRANGE Errors

IMPORTRANGE has several failure modes. Here's every common error and how to fix it.

Locale issues: In some locales (e.g., most European countries), Google Sheets uses semicolons as parameter separators instead of commas. If you're in a semicolon locale, your formula looks like this:

=IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ"; "Sales!A1:E50")

Permission tip: If you can't get the "Allow access" button to appear, try these steps in order:

1. Delete the formula.
2. Type a simple version first: =IMPORTRANGE("full-url-here", "Sheet1!A1")
3. Click "Allow access" when prompted.
4. Replace the formula with your actual range.

Limitations and Best Practices

IMPORTRANGE is powerful but it has hard limits you need to plan around.

Hard limits:

• 10 MB per import — Each IMPORTRANGE call can transfer a maximum of approximately 10 MB of data.
• ~50,000 cells practical limit — While there's no official cell count limit, imports above 50,000 cells become unreliable and slow.
• Formatting is not imported — Bold, colors, borders, conditional formatting — none of it transfers. Only raw values come through.
• Formulas are not imported — IMPORTRANGE pulls the calculated values, not the underlying formulas.
• Data validation is not imported — Dropdowns, checkboxes, and validation rules don't transfer.

Performance best practices:

• Import specific ranges, not full columns. Sales!A1:E500 is much faster than Sales!A:E.
• Minimize the number of IMPORTRANGE calls. Each call is a separate network request. One import of a large range is faster than ten imports of small ranges.
• Use helper tabs. Import data once into a hidden "Data" tab, then reference that tab in your formulas.
• Avoid volatile combinations. Wrapping IMPORTRANGE in NOW(), TODAY(), or RAND() forces constant recalculation.

Refresh behavior: IMPORTRANGE refreshes automatically, but not instantly. Changes in the source typically appear in the destination within 1-5 minutes. There's no way to force an immediate refresh — the closest workaround is deleting the formula and re-entering it.

IMPORTRANGE vs Other Import Functions

Google Sheets has five IMPORT functions. Each serves a different purpose.

If you need to pull data from websites, APIs, or databases — sources that go beyond what the built-in IMPORT functions support — a Google Sheets add-on like Unlimited Sheets can handle those cases with web scraping, AI extraction, and direct API connections.

Frequently Asked Questions

How do I use IMPORTRANGE in Google Sheets?

Enter =IMPORTRANGE("spreadsheet_url", "SheetName!Range") in any cell. Replace spreadsheet_url with the full URL of the source spreadsheet and SheetName!Range with the tab name and cell range you want to import (e.g., "Sales!A1:E100"). The first time you use it, you'll need to click "Allow access" to authorize the connection.

Why does IMPORTRANGE show #REF! error?

The most common cause is that you haven't granted permission yet. Hover over the cell with the error and click "Allow access." If you've already done that, check for: a wrong or outdated spreadsheet URL, a misspelled sheet name, a deleted source spreadsheet, or insufficient sharing permissions.

Can I use IMPORTRANGE with QUERY?

Yes, and it's one of the most useful combinations in Google Sheets. Wrap IMPORTRANGE inside QUERY: =QUERY(IMPORTRANGE("url", "Sheet1!A1:E100"), "SELECT Col1, Col2 WHERE Col3 > 100"). Remember to use Col1, Col2 notation instead of column letters when querying an IMPORTRANGE result.

How many cells can IMPORTRANGE handle?

There's no official published cell limit, but there's a 10 MB data size cap per IMPORTRANGE call. In practice, imports work reliably up to around 50,000 cells. Beyond that, you may experience slow loading, timeouts, or "Result too large" errors.

Does IMPORTRANGE update automatically?

Yes. When data changes in the source spreadsheet, the destination updates automatically — typically within 1 to 5 minutes. There's no manual refresh button. If data appears stale, try deleting and re-entering the formula to force a refresh.

Can I import from multiple spreadsheets at once?

Yes. Use curly braces to stack multiple IMPORTRANGE calls into a single array. Use semicolons to stack vertically:

={IMPORTRANGE("url1", "Sheet1!A1:E100");
  IMPORTRANGE("url2", "Sheet1!A2:E100");
  IMPORTRANGE("url3", "Sheet1!A2:E100")}

All ranges must have the same number of columns. You'll need to grant "Allow access" for each source spreadsheet individually.