Google Sheets QUERY Col1 Col2 Syntax Explained

The QUERY function uses Google's Visualization API Query Language, which supports 11 distinct clauses (select, where, group by, pivot, order by, limit, offset, label, format, options, skipping). The ColN notation is how that query language identifies columns when it can't use named headers — and understanding why matters more than memorizing the syntax.

Mapping Col1 Col2 Syntax to Actual Sheet Columns

The ColN number is its position counting left-to-right from the first column in your range. Here's how that maps across different sheet ranges:

So =QUERY(C1:F100, "SELECT Col1, Col3 WHERE Col2 > 1000") selects columns C and E, filtered on column D. Write SELECT C, E WHERE D > 1000 and QUERY throws a parse error — it doesn't accept column letters in that context.

The confusion is compounded because the =QUERY documentation in Google's Sheets help pages uses both column letter examples and ColN examples in different places, without always making clear which syntax applies where.

Why QUERY Col1 Col2 Syntax Breaks When You Move Columns

Insert a column before your range, and every ColN reference inside your query shifts silently. Your formula doesn't error out — it just returns wrong data.

Say your formula is =QUERY(B:E, "SELECT Col1, Col2, Col4 WHERE Col3 = 'Active'"). That's reading columns B, C, E where D is "Active." Someone inserts a column at C to add a notes field. Now your range B:E picks up the new column C, your old column C is now Col3, and your old column D (the Active filter) is Col4. The formula still runs. It's just answering a different question than you wrote it to answer.

This is why locking ranges with explicit bounds (B2:E5000 rather than B:E) only partially helps. The real fix is either named ranges or building the column map into a comment somewhere visible. According to Google's Visualization API Query Language reference, ColN indices are always evaluated at query execution time relative to the data table passed in — there's no static binding.

Using Dynamic Criteria in QUERY with Col Syntax

Hardcoded WHERE clauses rot. Quarters end, filters change, someone forgets to update the formula. The pattern that holds up is wiring your filter criteria to a cell:

=QUERY(B2:F5000,
  "SELECT Col1, Col3, Col5
   WHERE Col4 = date '"&TEXT(A2,"yyyy-mm-dd")&"'",
  1)

The TEXT(A2,"yyyy-mm-dd") forces the date into the exact format QUERY's parser expects. Use any other format and you get a type mismatch. This isn't a QUERY quirk — it's baked into the Visualization API specification: date literals must follow ISO 8601 (yyyy-MM-dd), as documented in Google's Query Language Reference.

For text filters, the same principle applies:

=QUERY(B2:F5000,
  "SELECT Col1, Col3, Col5
   WHERE Col2 = '"&A1&"'",
  1)

Drop the inner quotes and QUERY tries to evaluate the cell value as a column name. Include them and it treats it as a string literal. The 1 at the end tells QUERY that the first row is a header — skip it and QUERY treats your header row as data.

Combined Ranges and the ColN Restart

When you stitch two ranges together with curly braces — {Sheet1!A:D, Sheet2!E:G} — QUERY treats the result as a single flat table. ColN starts over from Col1 at the leftmost column of the combined range. Sheet2's column E becomes Col5 (or wherever it falls in the merged table), not Col1.

This matters in cross-tab lookups. A common board-reporting pattern:

=QUERY(
  {Accounts!A2:C, IFERROR(
    VLOOKUP(Accounts!A2:A, Deals!A:D, {2,3,4}, 0),
    {"","",""})},
  "SELECT Col1, Col2, Col4, Col5, Col6
   WHERE Col3 = 'Enterprise'
   ORDER BY Col6 DESC",
  0
)

The combined range has 6 columns total: 3 from Accounts (A, B, C → Col1, Col2, Col3) and 3 from the VLOOKUP result (→ Col4, Col5, Col6). The IFERROR wrapping the VLOOKUP fills in empty strings for any Accounts row without a matching Deal — which will cover roughly 12-15% of rows in any real dataset within a few weeks of going live.

The 0 at the end (header row count) is intentional here since we're building the range programmatically rather than pulling from a sheet that has headers in row 1.

Handling QUERY Col Syntax Across Large Datasets

QUERY runs entirely in-browser on Google's infrastructure. On ranges with 4,800 numbers and 200 text strings, it's near-instant. On 50,000 rows with multiple nested cross-tab lookups, recalculation lag becomes real — especially if the sheet recalculates on every edit. Google Sheets caps workbooks at 10 million cells total, and the 30-second execution timeout for QUERY applies before that limit.

A few things that actually help at scale:

The first is range scoping. B2:F (open-ended) forces QUERY to scan every row down to row limit. B2:F5000 caps it. If your dataset grows past that bound you'll get truncated results, but for most ops dashboards a bounded range trades correctness-risk for speed.

The second is filter ordering. Put your most selective WHERE clause first. WHERE Col3 = 'Closed Won' AND Col5 > DATE '2026-01-01' runs faster than the same conditions reversed if Col3 eliminates 90% of rows.

The third is offloading to a tool that isn't recalculating in a browser cell. If you're regularly querying against a 40,000-row CSV export or a live PostgreSQL connection, ModelMonkey's DuckDB integration handles that without the recalculation overhead — you write SQL, results land in the sheet, no ColN mapping required.

QUERY Col Syntax vs. FILTER: When to Use Which

Both functions filter rows, but they answer different questions.

FILTER is simpler for basic row selection. QUERY is the right call once you need to reshape or aggregate — selecting 3 of 8 columns, pivoting categories, or grouping by month.

As Google's Sheets function documentation notes, QUERY "runs a Google Visualization API Query Language query across the data" — which means it's a full query engine, not just a filter layer.