Formula example
Google Sheets QUERY Another Sheet
Direct answer: use 'Raw Data'!A1:D100 when QUERY reads another tab in the same Google Sheets file, and use QUERY with IMPORTRANGE only when the source data is in another spreadsheet file. The examples return selected columns where Status is Complete.
Copy-paste formula
=QUERY('Raw Data'!A1:D100, "SELECT A, B, D WHERE C = 'Complete'", 1)With the sample data, Status = Complete returns Import leads and Review budget.
Useful variations
=QUERY(A1:D100, "SELECT A, B, D WHERE C = 'Complete'", 1)Use a normal range when the source data is on the same tab as the formula.
=QUERY(Sheet2!A1:D100, "SELECT A, B, D WHERE C = 'Complete'", 1)Tab names without spaces can be referenced without single quotes.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit", "Raw Data!A1:D100"), "SELECT Col1, Col2, Col4 WHERE Col3 = 'Complete'", 1)The first use of IMPORTRANGE needs Allow access before QUERY can return rows.
=QUERY('Raw Data'!A1:D100, "SELECT A, B, D WHERE C = '"&F1&"'", 1)Use this when F1 contains the status, region, owner, or category to filter.
=QUERY({'Jan Data'!A2:D;'Feb Data'!A2:D}, "SELECT Col1, Col2, Col4 WHERE Col3 = 'Complete'", 0)Array-combined tabs use Col1, Col2, and Col4 because QUERY sees the stacked data as an array.
Sample data
| Task | Owner | Status | Due Date |
|---|---|---|---|
| Import leads | Maya | Complete | 2026-01-06 |
| Clean headers | Nico | In Progress | 2026-01-08 |
| Review budget | Maya | Complete | 2026-01-12 |
| Publish report | Iris | Blocked | 2026-01-15 |
When to use this formula
- Use A1:D100 when the source data is on the same tab as the formula.
- Use 'Raw Data'!A1:D100 when the source tab is in the same spreadsheet and the tab name contains spaces.
- Use Sheet2!A1:D100 when the source tab is in the same spreadsheet and the tab name has no spaces.
- Use IMPORTRANGE plus QUERY when the source data lives in another spreadsheet file.
Same tab another tab or another spreadsheet
Same tab: use a normal range such as A1:D100.
Same spreadsheet, another tab with spaces: wrap the tab name in single quotes, such as 'Raw Data'!A1:D100.
Another spreadsheet file: wrap IMPORTRANGE inside QUERY. This is the only pattern here that needs an external spreadsheet URL and access permission.
=QUERY(A1:D100, "SELECT A, B, D WHERE C = 'Complete'", 1)=QUERY('Raw Data'!A1:D100, "SELECT A, B, D WHERE C = 'Complete'", 1)=QUERY(Sheet2!A1:D100, "SELECT A, B, D WHERE C = 'Complete'", 1)=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit", "Raw Data!A1:D100"), "SELECT Col1, Col2, Col4 WHERE Col3 = 'Complete'", 1)Click Allow access for the IMPORTRANGE connection the first time.
Column letters versus Col notation
Use A, B, C, and D in the query string when QUERY reads a normal Google Sheets range from the same spreadsheet.
Use Col1, Col2, Col3, and Col4 when QUERY reads an IMPORTRANGE result, because QUERY sees the imported data as an array.
If you change the source range from A1:D100 to B1:E100, update the selected letters or Col numbers so they still point to the intended fields.
=QUERY('Raw Data'!A1:D100, "SELECT A, B, D WHERE C = 'Complete'", 1)=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit", "Raw Data!A1:D100"), "SELECT Col1, Col2, Col4 WHERE Col3 = 'Complete'", 1)Criteria from a local cell
Keep the source range on another tab, but build the WHERE value from a cell on the current tab when users need to choose the filter.
For text criteria, leave the query string, concatenate the local cell, then re-enter the query string so the final QUERY receives quotes around the text value.
=QUERY('Raw Data'!A1:D100, "SELECT A, B, D WHERE C = '"&F1&"'", 1)If F1 contains Complete, the returned rows match the fixed Complete example.
=QUERY('Raw Data'!A1:D100, "SELECT A, B, D WHERE C = '"&F1&"' AND B = '"&F2&"'", 1)Use AND when the local controls should both apply.
QUERY multiple tabs in one spreadsheet
Stack same-shaped tabs with an array literal when the source is split by month, region, or team inside the same spreadsheet.
Because the stacked source is an array, use Col1, Col2, Col3, and Col4 in the QUERY string instead of A, B, C, and D.
Use 0 as the header argument when each tab reference starts at row 2 and the header row has already been excluded.
=QUERY({'Jan Data'!A2:D;'Feb Data'!A2:D}, "SELECT Col1, Col2, Col4 WHERE Col3 = 'Complete'", 0)Each tab must have the same columns in the same order.
Set up IMPORTRANGE before QUERY
When the data is in another spreadsheet file, authorize the connection before troubleshooting the QUERY string.
Place a simple IMPORTRANGE in an empty cell, click Allow access, then move the same IMPORTRANGE inside QUERY.
If the source sheet is private, the account opening the destination spreadsheet needs permission to view the source file.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit", "Raw Data!A1:D100")Use this once to approve access, then use the QUERY formula.
Header row argument
The last QUERY argument tells Google Sheets how many header rows exist in the source range.
Use 1 when the first row contains headers such as Task, Owner, Status, and Due Date.
Use 0 when the source range starts directly with data rows. A wrong header value can make the first row disappear or appear inside the result.
Which formula should I use?
| Source location | Use this formula pattern | Notes |
|---|---|---|
| Same tab as the formula | =QUERY(A1:D100, "SELECT A, B, D WHERE C = 'Complete'", 1) | Use a normal range. |
| Another tab in the same spreadsheet | =QUERY('Raw Data'!A1:D100, "SELECT A, B, D WHERE C = 'Complete'", 1) | Quote the tab name when it has spaces. |
| Another spreadsheet file | =QUERY(IMPORTRANGE(url, "Raw Data!A1:D100"), "SELECT Col1, Col2, Col4 WHERE Col3 = 'Complete'", 1) | Authorize IMPORTRANGE first and use Col notation. |
| Multiple tabs in the same spreadsheet | =QUERY({'Jan Data'!A2:D;'Feb Data'!A2:D}, "SELECT Col1, Col2, Col4 WHERE Col3 = 'Complete'", 0) | Stack same-shaped ranges with an array literal. |
QUERY plus IMPORTRANGE precheck
| Step | Formula or check | Expected result |
|---|---|---|
| 1. Test access | =IMPORTRANGE("https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit", "Raw Data!A1:D100") | Google Sheets asks for Allow access, then shows imported rows. |
| 2. Confirm column order | Check that Task, Owner, Status, and Due Date are still columns 1-4. | The later QUERY can safely use Col1, Col2, Col3, and Col4. |
| 3. Add QUERY | =QUERY(IMPORTRANGE(url, "Raw Data!A1:D100"), "SELECT Col1, Col2, Col4 WHERE Col3 = 'Complete'", 1) | Returns only completed rows from the other spreadsheet. |
Returned result for Status = Complete
| Task | Owner | Due Date | Why it returns |
|---|---|---|---|
| Import leads | Maya | 2026-01-06 | Status is Complete in column C. |
| Review budget | Maya | 2026-01-12 | Status is Complete in column C. |
QUERY another sheet troubleshooting
| Problem | Likely cause | Fix |
|---|---|---|
| Formula parse error with Raw Data | The sheet name contains a space but is not wrapped in single quotes. | Use 'Raw Data'!A1:D100. |
| #REF! with another spreadsheet | IMPORTRANGE has not been authorized or the source file is not shared. | Run a simple IMPORTRANGE first and confirm the destination account can view the source file. |
| Wrong columns returned after changing the range | The SELECT and WHERE column letters still match the old range. | Update A, B, C, and D to match the new source range, or use Col notation with IMPORTRANGE. |
| IMPORTRANGE returns #REF! | The connection has not been allowed yet. | Open a simple IMPORTRANGE once and click Allow access. |
| Header row appears as data or a row is missing | The third QUERY argument has the wrong header row count. | Use 1 when the first row is headers, or 0 when there is no header row. |
| Rows disappear from a mixed-type column | QUERY chooses one dominant data type in each column. | Keep each QUERY column consistently text, number, or date before filtering. |
Formula explanation
- A normal same-tab QUERY can read A1:D100 directly.
- Single quotes around the sheet name are needed when the tab name contains a space.
- The query string filters column C to completed rows.
- The formula returns only columns A, B, and D from the source range.
- IMPORTRANGE queries use Col1, Col2, and Col3 notation because the imported array is not addressed by sheet column letters.
Common errors
- Using IMPORTRANGE for another tab in the same spreadsheet when a normal tab reference is enough.
- Forgetting single quotes around sheet names with spaces.
- Using the wrong column letters after changing the source range.
- Forgetting to allow IMPORTRANGE access before using QUERY on another spreadsheet file.
- Using the wrong header row count as the third QUERY argument.
- Filtering mixed data types in QUERY columns without cleaning the source column first.
Build your own version
Use the formula builder for this pattern: Google Sheets QUERY Formula Builder.
Related formulas
FAQ
Do I need IMPORTRANGE for another tab in the same spreadsheet?
No. Use IMPORTRANGE only when the source data is in another spreadsheet file. For another tab in the same file, use a tab reference such as 'Raw Data'!A1:D100.
Does this import from another spreadsheet file?
Use IMPORTRANGE inside QUERY when the source data is in another spreadsheet file. The first connection needs Allow access.
What if the sheet name has no spaces?
Use Sheet2!A1:D100. Single quotes are still safe, but not always required.
Why does IMPORTRANGE use Col1 instead of A?
QUERY sees the imported range as an array, so it uses Col1, Col2, and Col3 notation instead of sheet column letters.
Why does QUERY skip my first row?
Check the last QUERY argument. Use 1 when the source range includes one header row, and use 0 when the range starts with data.