The Complete IMPORTRANGE Function Guide for Google Sheets (2026) — Cross-Workbook Data Live
Home› Google Sheets› Guides› IMPORTRANGE Complete Guide
Google Sheets — Native Function

The complete IMPORTRANGE guide for Google Sheets (2026)

IMPORTRANGE is Google Sheets' bridge between workbooks. It lets one Sheet pull live data from another Sheet — updates flow automatically, no manual copying, no downloads. It's the foundation of single source of truth architectures where master data lives in one file and dozens of downstream dashboards import views of it. This is the complete guide: authorization, six essential patterns, security, common errors, quotas, and the powerful IMPORTRANGE + QUERY combination. Free live-demo workbook included.

📊 Free live-demo workbook

Every formula in this guide uses a real public workbook. Copy any formula, paste into your blank Sheet, click "Allow Access" — live data flows in.

Open Source Workbook

What is IMPORTRANGE?

IMPORTRANGE is a Google Sheets function that pulls a range of cells from one Sheet into another. The pulled data stays live — when the source updates, so does the destination. It's the cell-formula equivalent of a live database view.

The mental model that unlocks IMPORTRANGE is straightforward:

Source Workbook
RegionAmount
North$897
South$2,500
East$998
West$3,000
→
IMPORTRANGE
Your Workbook
RegionAmount
North$897
South$2,500
East$998
West$3,000

Data lives in one workbook. A formula in another workbook pulls it in. Both workbooks display the same values. Change the source, and the destination updates automatically. That's IMPORTRANGE.

What makes it foundational for real spreadsheet architecture is what happens at scale. A master customer list becomes one source workbook. Sales dashboards, marketing dashboards, customer success dashboards, and executive reports all IMPORTRANGE from it. When a customer's tier changes, all six downstream dashboards reflect the change automatically. No email chains. No stale data. No parallel spreadsheets drifting apart.

💡 The single-source-of-truth pattern

Before IMPORTRANGE, teams typically maintained parallel copies of the same data — a sales team's customer list, a finance team's customer list, a support team's customer list. They inevitably drifted. With IMPORTRANGE, one master workbook feeds all downstream views. One place to update, everywhere reflects. This is why serious Sheets teams organize their data around IMPORTRANGE from day one.

Why it matters — the architecture

To appreciate IMPORTRANGE, look at what teams do without it. The typical pattern before IMPORTRANGE:

Someone maintains "the master file". Every Monday morning, they email a copy to five people. Those five people each keep their own copy on their computer, on their Drive, or as an attached file in Slack. Some make edits. Others don't. Nobody is entirely sure which copy is current. Over weeks, five slightly different truths coexist.

With IMPORTRANGE, the master file is a Google Sheet. The other five people don't get copies — they get workbooks that IMPORTRANGE from the master. Their workbooks show live data from the master. Anyone can add their own local calculations, dashboards, or charts on top, but the underlying data always reflects the master. The concept of "the current version" becomes obvious: the master.

The architectural benefits compound:

Zero manual sync. Master updates flow to every downstream workbook automatically (with a short cache delay).

Selective sharing. Downstream workbooks can filter, aggregate, or lookup subsets of master data. A regional manager's dashboard IMPORTRANGE-pulls only their region's rows. An executive dashboard aggregates across regions. Each stakeholder sees exactly what they need.

Clean permissions. Access to the master workbook determines who can see the underlying data. Downstream workbooks can be shared more broadly for their local dashboards without exposing the raw master.

Simple rollbacks. If bad data enters the master, roll back the master file. Every downstream workbook self-corrects on next refresh.

Anatomy of IMPORTRANGE

IMPORTRANGE has the simplest possible syntax — just two arguments:

=IMPORTRANGE(source_url, range_string)

Argument 1 — source_url: The full URL of the source workbook, in quotes. You can copy this from the address bar of the source workbook — the full https://docs.google.com/spreadsheets/d/.../edit string. Google Sheets also accepts just the spreadsheet key (the long ID between /d/ and /edit) if you prefer shorter formulas.

Argument 2 — range_string: The range you want to pull, formatted as "SheetName!A1:D10" in quotes. Include the sheet name, then !, then the range. Use A:A to import an entire column, A:H for eight columns, Sheet1!A2:H51 for a specific rectangular range.

That's the whole function. Everything else is knowing which URL to use, which range makes sense, and how to combine IMPORTRANGE with other functions.

Both arguments are strings — quote them

Beginners often forget the quotes around one or both arguments. =IMPORTRANGE(A2, "Sales!A:H") where A2 contains a URL is valid (Sheets treats the cell reference as text). But =IMPORTRANGE(https://..., Sales!A:H) without any quotes fails immediately. When in doubt, wrap both arguments in double quotes.

The authorization flow

The first time you IMPORTRANGE from a specific source workbook, Sheets will ask for permission. This is a one-time authorization per source-destination pair, and it trips up nearly every new user. Here's the flow:

First-Time IMPORTRANGE Authorization
1
Paste formula
#REF!
Cell shows an error. Don't panic — this is expected on first use.
→
2
Hover the error
Allow access?
A popup appears asking permission to connect the two workbooks.
→
3
Click "Allow"
Data flows in
Data streams in. Every future IMPORTRANGE from this source works immediately.

This authorization is granted per user, per source-destination pair. You authorize once, and every IMPORTRANGE you write from that source into that destination works immediately after. If someone else opens the destination workbook, they may need to authorize separately (depending on their access to the source).

Critically, you can only authorize IMPORTRANGE from a source you have permission to view. If someone shares only the destination workbook with you but not the source, IMPORTRANGE returns #REF! and no authorization popup appears — Sheets refuses to connect to files you can't see.

Sample datasets in the source workbook

Every formula in this guide points at a real public source workbook containing three datasets. Here's what the primary dataset (Sales_Master) looks like:

Sales_Master — first 5 of 50 rows
A: Date B: Order_ID C: Region D: Rep E: Product F: Category G: Qty H: Amount
2026-03-14ORD-1000NorthAli KhanAnalytics ProSoftware3$897
2026-04-02ORD-1001SouthSara AhmedConsulting PackageServices1$2,500
2026-05-18ORD-1002EastJames MillerDashboard SuiteSoftware2$998
2026-06-30ORD-1003WestLisa WongTraining SessionServices4$3,000
2026-07-12ORD-1004CentralDavid ChenExcel TemplatesTemplates5$395

Copy any formula from this guide into a blank Google Sheets doc and it will pull from these datasets. First-time you'll see the authorization popup — click Allow, and data flows in.

Pattern 1

Full sheet import

The simplest use of IMPORTRANGE: pull an entire sheet into your workbook. Perfect for creating mirror copies where downstream views need access to the complete dataset.

Import the entire Sales_Master sheet
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QPA0Iv1gZQanvM4iMMkzv9RtpjFjeBMZ/edit", "Sales_Master!A:H")
ResultAll 50 rows and 8 columns of Sales_Master spill into your destination workbook, starting from wherever you place the formula.

The formula returns an array of 400 cells (50 rows × 8 columns) that spills from the cell containing the formula. The header row imports as-is. Add columns of your own calculations to the right, and they'll compute against the imported data.

If the source workbook adds new rows, they appear in your destination on the next refresh (usually within 30 minutes, or immediately if you edit anything in the destination). This is what makes IMPORTRANGE feel magical — the imported range grows and shrinks with the source.

The spill range must be empty

IMPORTRANGE needs empty cells to spill into. If any cell in the spill area contains data, you get #REF! until you clear it. Plan destination workbooks with a dedicated "raw import" sheet that IMPORTRANGE owns entirely, then reference from other sheets for your calculations and dashboards.

Pattern 2

Specific range import

Often you don't want the whole sheet — just specific columns or a specific window. Narrower ranges import faster, use less quota, and keep your destination workbook cleaner.

Import only Region, Rep, and Amount columns
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QPA0Iv1gZQanvM4iMMkzv9RtpjFjeBMZ/edit", "Sales_Master!C:H")
ResultOnly columns C through H arrive — you get Region, Rep, Product, Category, Qty, Amount. Date and Order_ID stay in the source.

Note the syntax: Sales_Master!C:H pulls columns C through H (six columns). You can also pull specific cell ranges like Sales_Master!A2:H51 to get exactly 50 data rows without the header, or Sales_Master!A1:H10 to preview just the first 10 rows.

Import just the header row
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QPA0Iv1gZQanvM4iMMkzv9RtpjFjeBMZ/edit", "Sales_Master!A1:H1")
ResultOnly the header labels appear — useful for building templates that inherit source column names.
Pattern 3

Filtered import with QUERY

Here's where IMPORTRANGE becomes truly powerful. Combine it with QUERY to filter, sort, aggregate, and reshape data as it arrives. Instead of pulling 50 rows and filtering downstream, pull only the rows you need in a single formula.

Import only North region rows
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QPA0Iv1gZQanvM4iMMkzv9RtpjFjeBMZ/edit", "Sales_Master!A:H"), "SELECT * WHERE Col3 = 'North'", 1)
ResultOnly Northern transactions arrive. Approximately 10-12 rows out of the 50 in the source.

Important syntax note: when wrapping IMPORTRANGE with QUERY, column references change from letters (A, B, C…) to Col1, Col2, Col3. This is because IMPORTRANGE returns an array without column-letter identity — QUERY needs positional references. Col1 is the first imported column, Col2 the second, and so on.

Total sales by region — one formula, one output
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QPA0Iv1gZQanvM4iMMkzv9RtpjFjeBMZ/edit", "Sales_Master!A:H"), "SELECT Col3, SUM(Col8) GROUP BY Col3 LABEL SUM(Col8) 'Total Revenue'", 1)
ResultFive-row aggregation table with total revenue by region — pulled and computed across workbooks
RegionTotal Revenue
Central$18,450
East$24,120
North$31,890
South$19,750
West$26,220

Think about what this single formula does: pulls 50 rows from another workbook, groups by region, sums the amounts, labels the output columns, and returns a five-row summary — all in one cell. In a pre-IMPORTRANGE world, this required copying data manually, then writing SUMIFS across a UNIQUE list of regions. Now it's one expression that recalculates automatically as the source workbook updates.

The two most-used IMPORTRANGE patterns

Pattern 3 (IMPORTRANGE + QUERY) and Pattern 4 (IMPORTRANGE + VLOOKUP/ARRAYFORMULA) together power almost every real-world IMPORTRANGE use case. Master these two combinations and you can build sophisticated cross-workbook dashboards with just a handful of formulas. The QUERY pillar covers query syntax in depth if you need a refresher.

Pattern 4

Cross-workbook lookups

Say your customer list lives in one workbook and your sales transactions live in another. You need to join them — attach customer tier, region, or contract terms to each transaction. IMPORTRANGE inside VLOOKUP or XLOOKUP makes this trivial.

Look up product category for local order data
=ARRAYFORMULA(VLOOKUP(A2:A50, IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QPA0Iv1gZQanvM4iMMkzv9RtpjFjeBMZ/edit", "Products_Master!B:D"), 3, FALSE))
ResultFor every product name in your local column A, the matching category from the source's Products_Master arrives — 49 lookups in one formula.

Break down what this does. VLOOKUP's second argument is normally a local range like Products!B:D. Here it's IMPORTRANGE(...) — a live pull from another workbook. VLOOKUP does the matching against that imported table. ARRAYFORMULA broadcasts across the 49 rows of your local data. One formula, 49 cross-workbook lookups.

The ARRAYFORMULA pillar covers this pattern in depth — it's one of the most valuable combinations in Google Sheets. Together, IMPORTRANGE + ARRAYFORMULA + VLOOKUP form the "cross-workbook enrichment" pattern used in nearly every real Sheets architecture.

Pattern 5

Multi-source stacking

Sometimes you need to combine data from multiple source workbooks — for example, sales data from different regional offices, each maintaining its own workbook. Curly braces let you stack multiple IMPORTRANGE outputs into a single unified range:

={IMPORTRANGE("https://docs.google.com/spreadsheets/d/URL_1/edit", "Sales!A2:H100"); IMPORTRANGE("https://docs.google.com/spreadsheets/d/URL_2/edit", "Sales!A2:H100"); IMPORTRANGE("https://docs.google.com/spreadsheets/d/URL_3/edit", "Sales!A2:H100")}

The semicolons stack vertically — rows from each workbook append below the previous. All three ranges must have the same number of columns (eight here) for the stack to work. Use commas instead of semicolons for horizontal (side-by-side) stacking.

This is how consolidated regional dashboards work — each regional team maintains their own source workbook with the same schema, and a central "roll-up" workbook stacks them all into one unified view. Then a QUERY on top aggregates across the whole thing.

Note the range starts at row 2

When stacking, always start at row 2 (e.g., A2:H100) to skip the header row. Otherwise you get three header rows in your stacked output. Then, separately import a single header row from one source to appear once at the top of your destination.

Pattern 6

Aggregation across workbooks

Simple aggregations like SUM, COUNT, and AVERAGE work directly against IMPORTRANGE output — no ARRAYFORMULA needed. Perfect for KPI cards that summarize cross-workbook data in a single cell.

Total revenue across all imported sales
=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QPA0Iv1gZQanvM4iMMkzv9RtpjFjeBMZ/edit", "Sales_Master!H:H"))
ResultA single number — the total of every value in the Amount column of the source workbook. Around $120,000 depending on the current source data.
Count of transactions in the source
=COUNTA(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QPA0Iv1gZQanvM4iMMkzv9RtpjFjeBMZ/edit", "Sales_Master!A:A")) - 1
Result50 — the row count of transactions (subtracting 1 for the header row). Ideal for KPI cards.

These single-cell KPIs are the workhorses of Sheets executive dashboards. One cell for total revenue, one for transaction count, one for average deal size — each pulling live data from the master. Change the master and the executive view updates automatically.

Common IMPORTRANGE errors and fixes

IMPORTRANGE has a small number of characteristic errors. Here's what they mean and how to fix each:

ErrorWhat it meansHow to fix
#REF! with "Allow access" popup First-time authorization needed Click the cell, hover over the error, click "Allow access" in the popup. This authorizes IMPORTRANGE from this source into this destination for you specifically.
#REF! without popup Result would overwrite existing data Clear cells in the spill area. IMPORTRANGE needs empty space to spill its output into.
#ERROR! Malformed URL or range string Check that URL is complete and in quotes. Check that range uses correct sheet name (case-sensitive) and range syntax like Sheet1!A1:D10.
#N/A Source workbook or sheet doesn't exist Verify the URL still resolves to a real workbook. Check that the sheet name hasn't been renamed or deleted in the source.
"Loading..." indefinitely Source is very large or hitting quota limits Reduce the range you're importing. Check if you have many IMPORTRANGE formulas competing for quota. Wait a few minutes and retry.
Empty cells appear as 0 in calculations IMPORTRANGE treats empty cells as blanks, which arithmetic converts to 0 Wrap calculations in IF checks: =IF(cell="", "", cell*2). Or filter out empty rows with QUERY before calculating.
The "You don't have permission" trap

If the source workbook is later revoked from you (or if the source owner tightens sharing), your IMPORTRANGE formulas break with #REF! and there's no way to re-authorize — you simply no longer have access. The fix requires the source owner to re-share with you. This is why IMPORTRANGE architecture depends on stable sharing policies at the source workbook.

Performance, limits, and quotas

IMPORTRANGE is powerful but subject to Google's usage limits. Understanding these prevents surprises as your architecture scales.

Per-workbook limit: A destination workbook can contain up to 50 IMPORTRANGE formulas simultaneously. This sounds like a lot until you're building a dashboard with 20 KPI cards, each with its own IMPORTRANGE — you hit the ceiling faster than expected. The workaround: import one large range once, then reference from other cells locally.

Cache refresh: IMPORTRANGE data caches on Google's servers and refreshes approximately every 30 minutes. Opening the destination workbook or editing any cell forces an immediate refresh. This means "live" is really "live within 30 minutes" for most cases — usually fine, but not real-time.

Cell limits: A single IMPORTRANGE can return up to about 10,000,000 cells (Google's overall spreadsheet limit) but practical performance degrades well before that. IMPORTRANGE ranges over 100,000 cells often feel sluggish. Split into multiple smaller imports if you can, or use QUERY to filter down before the data lands.

Concurrent requests: Google enforces per-user request quotas that IMPORTRANGE draws from. Very heavy IMPORTRANGE usage (dozens of workbooks all pulling from the same source simultaneously) can hit quota limits and show "Loading..." for extended periods. For production scenarios at this scale, consider Google's Data Connectors.

Security considerations

IMPORTRANGE has a specific security model worth understanding before building anything sensitive.

The source workbook's permissions still apply. If you don't have view access to a source workbook, no amount of IMPORTRANGE syntax will let you see its data. Google enforces this at the platform level — IMPORTRANGE is not a permissions bypass.

Downstream workbook access = source data access. Anyone who can view your destination workbook can see the imported cells, regardless of whether they have direct access to the source. This is the key security implication: sharing an IMPORTRANGE-based dashboard effectively shares the underlying source data.

Revocation cascades. If a user loses access to the source workbook, their IMPORTRANGE formulas in destination workbooks stop working — showing #REF! instead of stale cached data. This makes IMPORTRANGE self-correcting when permissions change.

Best practices for sensitive data: Keep master data in tightly-controlled source workbooks. Use IMPORTRANGE + QUERY to expose only necessary columns or rows to downstream workbooks. If confidentiality matters, don't share destination workbooks more broadly than the source workbook itself would allow.

IMPORTRANGE vs alternatives

For dual-platform teams, here's how IMPORTRANGE compares to cross-file solutions in other tools:

ApproachPlatformBest for
IMPORTRANGEGoogle SheetsLive cross-workbook data in a cell formula. Simple, no scripting.
Data ConnectorsGoogle SheetsBigQuery, Salesforce, or database connections. Enterprise scale.
Apps ScriptGoogle SheetsComplex logic, scheduled syncs, external APIs, custom transformations.
Power QueryExcelMulti-file ETL with UI-driven transformations. Requires manual refresh.
External workbook linksExcelSimple cell-to-cell references across files. Fragile with file paths.
Database connectionBothSerious production analytics — proper SQL databases beat spreadsheets.

IMPORTRANGE wins on simplicity for team-scale data sharing. It struggles with real-time requirements, very large datasets, or complex transformations. For those, Data Connectors, Apps Script, or a proper database beat IMPORTRANGE.

When NOT to use IMPORTRANGE

IMPORTRANGE isn't right for every cross-workbook scenario. Use something else when:

You need sub-minute data freshness. IMPORTRANGE's 30-minute cache means truly live data (stock prices, live inventory) needs Apps Script or a Data Connector instead.

Source workbook is very large. Pulling 500,000+ cells across workbooks pushes IMPORTRANGE to its limits. Consider BigQuery or a database.

Data needs heavy transformation. If you're doing complex reshaping, joining across many sources, or applying business logic, Apps Script or Power Query handle it better than a chain of IMPORTRANGE + QUERY + ARRAYFORMULA formulas.

Users need offline access. IMPORTRANGE requires internet connectivity. If team members work offline, the destination workbook will show stale or missing data. Downloaded copies are the pragmatic answer here — sacrificing "live" for "reliable".

Cross-platform sharing (with Excel users). IMPORTRANGE formulas don't survive export to Excel. If your team is genuinely dual-platform, either standardize on Google Sheets or use a shared database that both tools can query.

Frequently asked questions

What does IMPORTRANGE do in Google Sheets?

IMPORTRANGE pulls a range of cells from one Google Sheets workbook and displays them live in another workbook. Changes to the source workbook automatically flow to the destination. Syntax: =IMPORTRANGE(source_url, range_string). It's the foundation of "single source of truth" architectures where master data lives in one file and downstream dashboards import views of it.

Why does IMPORTRANGE show #REF the first time I use it?

This is expected behavior. The first time you IMPORTRANGE from a specific source workbook, Sheets asks for permission before pulling data across workbook boundaries. Click the cell showing #REF!, hover over the error, and click "Allow access" in the popup. This authorization is one-time per pair of workbooks and per user — subsequent IMPORTRANGE formulas from the same source work immediately.

Does IMPORTRANGE work in Excel?

No. IMPORTRANGE is native to Google Sheets. Excel achieves similar results with Power Query (connecting to files) or workbook links, but neither offers the same live-formula simplicity. Excel's approach requires the UI-based Power Query editor rather than a cell formula. For cross-file live data in Excel, Power Query with scheduled refresh is the closest equivalent.

Can I use IMPORTRANGE without granting access?

No. Cross-workbook access always requires explicit user authorization. This is a security feature — Google prevents workbooks from silently pulling data from other files without user consent. If you own both workbooks, you still need to authorize on the destination side. The "Allow access" step happens exactly once per source-destination pair.

How do I combine IMPORTRANGE with QUERY?

Wrap IMPORTRANGE inside QUERY: =QUERY(IMPORTRANGE(url, "Sheet!A:H"), "SELECT Col1, Col2 WHERE Col3 = 'North'"). Important: column references change from letters (A, B) to Col1, Col2, Col3 because IMPORTRANGE returns an array, not sheet columns. This combination is one of the most powerful patterns in Google Sheets — enabling filtered, sorted, aggregated views across workbooks in a single formula.

What are IMPORTRANGE's limits and quotas?

Google enforces limits including a maximum of 50 IMPORTRANGE formulas per destination workbook and a source workbook cache that refreshes approximately every 30-60 minutes. Very large ranges (100,000+ cells) may hit performance limits. For production dashboards with high refresh needs, consider Google's Data Connectors, Apps Script, or restructuring your architecture with fewer, larger IMPORTRANGE ranges instead of many small ones.

Is IMPORTRANGE secure? Who can see my data?

IMPORTRANGE respects the source workbook's sharing permissions. If the source is view-only to specific people, only those people can see data pulled via IMPORTRANGE — even in a destination workbook. However, once someone has access to a destination workbook that uses IMPORTRANGE, they can potentially see everything from the source range regardless of source permissions. Treat IMPORTRANGE destinations as extensions of the source's permission set.

How do I stack IMPORTRANGE from multiple workbooks?

Use curly braces with semicolons for vertical stacking: ={IMPORTRANGE(url1, "Sheet!A:H"); IMPORTRANGE(url2, "Sheet!A:H")}. This combines rows from both workbooks into one output. For horizontal stacking (side-by-side), use commas instead: ={IMPORTRANGE(url1, "A:C"), IMPORTRANGE(url2, "A:C")}. Make sure ranges have compatible dimensions — same column count for vertical, same row count for horizontal.

How often does IMPORTRANGE update?

IMPORTRANGE updates automatically when the source workbook changes, but with a cache delay of approximately 30 minutes for most cases. Opening the destination workbook or making any change forces a refresh. For truly real-time data (sub-minute updates), consider Apps Script with triggers or a proper database. For most business dashboards updated hourly or daily, IMPORTRANGE's refresh rate is sufficient.

Can I import from a workbook I don't own?

Yes, as long as the source workbook is shared with you (view access is sufficient — you don't need edit permission on the source). Public workbooks and "anyone with the link" shared files work immediately. The authorization flow simply confirms your identity has access to the source. If the source is later revoked from you, your IMPORTRANGE formulas break with #REF!.

Related resources

· · ·

Bottom line: IMPORTRANGE turns Google Sheets into a distributed data platform. Master data lives in one place. Downstream views IMPORTRANGE + QUERY the slices they need. One place to update, everywhere reflects. It's the architectural foundation of serious Sheets work.