IMPORTRANGE + QUERY: Cross-Spreadsheet Data Pulls
Pull specific data from another Google Sheets file and filter it — all in one formula. The most powerful cross-sheet technique.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Import only Q1 2026 sales data from another spreadsheet"
=QUERY(IMPORTRANGE("spreadsheet_url","Sheet1!A1:D100"),"SELECT * WHERE Col3 >= date '2026-01-01' AND Col3 < date '2026-04-01'")IMPORTRANGE pulls the raw data from the external spreadsheet, then QUERY filters it to only Q1 2026 rows before displaying results.
Step-by-step breakdown
- IMPORTRANGE(url, range) fetches data from the other spreadsheet
- The URL must be the full Google Sheets URL or just the spreadsheet ID
- QUERY wraps the imported data to filter before displaying
- In QUERY over IMPORTRANGE, columns are referenced as Col1, Col2, etc. (not A, B, C)
- First-time use requires clicking Allow Access in the target cell
Edge cases & warnings
- First use requires manual permission grant (click the cell, click Allow)
- IMPORTRANGE is slow — avoid pulling more data than needed
- Column references change to Col1, Col2 format inside QUERY when source is IMPORTRANGE
- Rate limits: Google limits IMPORTRANGE calls per spreadsheet
Examples
"External spreadsheet with full-year sales data"
Only Q1 2026 rows imported and displayed"Multiple sheets to consolidate"
Use separate IMPORTRANGE+QUERY per source, stack with {range1;range2}Frequently asked questions
Why does IMPORTRANGE show #REF?
You need to grant access. Click the cell with the error, and you'll see an Allow Access button. Click it once per source spreadsheet.
Can I use IMPORTRANGE in Excel?
No. IMPORTRANGE is Google Sheets only. In Excel, use Power Query to connect to external data sources.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.