Analistable

Why QUERY across multiple Google Sheets returns errors or misses rows

By the Analistable team · Updated · 5 min read

When QUERY runs over stacked ranges like {Jan!A2:C; Feb!A2:C}, refer to columns as Col1, Col2, Col3 — letters cause a NO_COLUMN error. Every stacked range must have the same number of columns, set the header argument to 0 explicitly, and filter out blank rows with where Col1 is not null. If rows vanish, a column holds mixed numbers and text: QUERY keeps the majority type and treats the rest as empty.

Part of our guide: How to combine data in Google Sheets

The formula these errors come from

Combining tabs with QUERY usually looks like this:

=QUERY({Jan!A2:C; Feb!A2:C; Mar!A2:C}, "select Col1, sum(Col3) where Col1 is not null group by Col1", 0)

The braces stack the three ranges into one array; the semicolon means "next rows". QUERY then treats that array as a table. Most problems come from the stacking, not the query. The basics of this pattern are in QUERY with multiple ranges; this page is about what goes wrong.

Error: NO_COLUMN / unable to parse query string

Inside QUERY, a column letter like A only works when the data argument is a direct range reference. A stacked array {…} has no letters, so select A, C fails with an error mentioning NO_COLUMN. Use Col1, Col2, Col3 — with a capital C — numbered by position in the stacked array.

Other common parse errors:

  • Text values need single quotes inside the query: where Col2 = 'North'.
  • Dates need the date keyword and ISO format: where Col1 >= date '2026-01-01'.
  • A cell reference must be concatenated in: "where Col2 = '"&E1&"'". Putting E1 inside the string looks for a column called E1.
  • Every non-aggregated column in select must appear in group by.

Error: array literal missing values

"In ARRAY_LITERAL, an Array Literal was missing values for one or more rows" means the stacked ranges have different widths — Jan!A2:C is three columns, Feb!A2:D is four. Make every range the same width: add an empty column to the short tab, or narrow the wider range so every tab contributes the same columns in the same order.

Separators depend on the spreadsheet locale. In locales that use a comma as the decimal separator, the column separator inside braces is a backslash ({A2:A \ B2:B}) and function arguments use semicolons. If a copied formula errors immediately, check File → Settings → Locale.

Problem: header rows mixed into the data

QUERY's third argument says how many header rows the data has. If you omit it, QUERY guesses, and with stacked ranges it can guess wrong — merging the first data rows into a combined header or treating a header as data. Start each range at row 2 to skip the header and set headers to 0. If you want headers, include them only from the first tab and set 1:

=QUERY({Jan!A1:C; Feb!A2:C; Mar!A2:C}, "select * where Col1 is not null", 1)

Problem: rows silently disappear

This is the hardest one to spot. QUERY gives each column a single data type, chosen by the majority of values in it. Values of the minority type are treated as null — they don't error, they just vanish from sums and filters.

Amount column after stacking three tabs
TabRowsAmount valuesTypeIncluded in sum(Col3)
Jan40120, 85, 230…NumberYes
Feb38"95", "140"… (imported as text)TextNo — treated as empty
Mar4260, 310, 75…NumberYes

Numbers are the majority (82 of 120 rows), so February's 38 text amounts drop out and the total is short by February's whole month. Fixes: convert the source column to numbers (Format → Number, or =VALUE() in a helper column), or add a helper column on the problem tab with =VALUE(C2) and stack that column instead. For ID columns, go the other way and turn everything to text with ARRAYFORMULA(TO_TEXT(…)).

Problem: blank rows and slow sheets

Open-ended ranges like A2:C include every empty row to the bottom of each tab, so the stack is mostly blank. Always add where Col1 is not null. On very large tabs, bound the ranges (A2:C5000) to keep the sheet responsive.

Newer Google Sheets also has VSTACK, which stacks ranges without brace syntax: =QUERY(VSTACK(Jan!A2:C, Feb!A2:C), …). The Col1 rule and the mixed-type rule still apply.

A debugging routine

  1. Remove the QUERY and look at the bare stacked array in an empty area: ={Jan!A2:C; Feb!A2:C}. If that errors, the problem is the stacking (widths, separators), not the query.
  2. Check the column count and order on every tab. A tab with Region and Date swapped stacks without error but gives nonsense.
  3. Run the simplest query, select *, with headers set to 0. Then add the where clause, then grouping, one piece at a time.
  4. Compare a total: =SUM(Jan!C2:C) + SUM(Feb!C2:C) + SUM(Mar!C2:C) against the QUERY's sum. SUM in a cell ignores text too, so also count text cells with =SUMPRODUCT(--ISTEXT(Feb!C2:C)).

Across separate spreadsheets

For tabs in other files, each range becomes an IMPORTRANGE inside the braces. Every source needs its access approved once, and every import must load before QUERY can run — see IMPORTRANGE not working and combining multiple IMPORTRANGE formulas.

If the formula keeps fighting you, Analistable can connect the Google Sheets directly, load each tab as its own table in DuckDB in your browser and answer the question with SQL, where types are set per column and a text amount produces a visible cast rather than a silent null. More options are in the combine data in Google Sheets guide.

Frequently asked questions

Why does QUERY say NO_COLUMN when I combine ranges?
Stacked arrays don't have column letters. Use Col1, Col2 and so on, numbered by position, with a capital C.
Why is QUERY ignoring some rows?
The column holds mixed types. QUERY keeps the majority type and treats the minority as empty, so convert the column to one type first.
What does the header argument do?
It tells QUERY how many rows at the top are headers. Set it explicitly — 0 if you skipped headers, 1 if only the first range includes them.
Can QUERY combine ranges from different spreadsheets?
Yes, by stacking IMPORTRANGE results inside braces, once access to each source has been allowed.

Related guides