Google Sheets QUERY Syntax: FP&A Reference (2026)

Function signature: =QUERY(data, query_string, [headers])

The headers parameter defaults to -1 (auto-detect), but set it explicitly. Auto-detect guesses wrong on numeric data. Use 1 for a single header row, 0 for none.

Clause Order Is Fixed

All 10 clauses must appear in this exact sequence when used together:

SELECT ... WHERE ... GROUP BY ... PIVOT ... ORDER BY ... LIMIT ... OFFSET ... LABEL ... FORMAT ... OPTIONS

Put ORDER BY before LABEL or you get a parse error. According to Google's Visualization API Query Language documentation, "The order of the clauses must follow this order" - there's no flexibility there, unlike standard SQL.

Column References: The Rule That Breaks Everything

How you reference columns depends entirely on what you pass as data.

When data is a sheet range ('P&L'!A:H), reference columns by their spreadsheet letter: A, B, C. When data is an array - including IMPORTRANGE results, literal arrays, or any function returning an array - reference columns as Col1, Col2, Col3.

This is why =QUERY('P&L'!B:F, "SELECT B, SUM(F) GROUP BY B", 1) works, but wrapping the same data in IMPORTRANGE() requires switching to Col1, Col2. Mixing the two notations produces Column [X] is not a valid column - one of the harder errors to debug because the formula looks syntactically correct.

WHERE Clause Syntax

Three data types, three different rules.

Strings: single quotes only. WHERE C = 'Revenue' works. Double quotes cause a parse error. If your string contains an apostrophe (e.g., a company name), escape it with a backslash: WHERE C = 'Owner\'s Equity'.

Numbers: no quotes. WHERE D > 4200000 not WHERE D > '4200000'.

Dates: require the date keyword. WHERE E >= date '2026-01-01'. Without it, Sheets treats the string as text and the comparison silently fails or returns zero rows. This is the single most common reason QUERY date filters appear to work but return nothing.

NULLs: use is not null and is null, not != null. A cell containing a formula that returns an empty string is null from QUERY's perspective. WHERE A != '' and WHERE A is not null are not interchangeable.

Here's a real example - pulling Q1 2026 revenue transactions by department from a 5,000-row transaction log:

=QUERY('Transactions'!A:G,
  "SELECT A, B, SUM(F)
   WHERE C = 'Revenue'
   AND E >= date '2026-01-01'
   AND E < date '2026-04-01'
   GROUP BY A, B
   ORDER BY SUM(F) DESC
   LABEL SUM(F) 'Q1 Revenue ($)'",
  1)

Without the LABEL clause, the header column reads SUM(F). Accurate, but not something you want in a board pack.

GROUP BY and Aggregates

QUERY supports SUM, AVG, COUNT, MAX, and MIN. Standard SQL behavior with one strict rule: every column in SELECT that isn't wrapped in an aggregate must appear in GROUP BY. Miss one and you get Column [X] is not specified in a GROUP BY clause.

A contribution margin summary from a multi-segment P&L, grouping actuals by business unit with 38.5% gross margin running through the source data:

=QUERY('P&L'!A:E,
  "SELECT B, SUM(D), SUM(E)
   WHERE A = 'Actuals'
   AND C = 'FY2026'
   GROUP BY B
   LABEL B 'Business Unit', SUM(D) 'Revenue', SUM(E) 'Gross Profit'",
  1)

One gap: QUERY has no COUNT DISTINCT. For distinct counts, you need a helper column or COUNTUNIQUE() outside the QUERY call.

PIVOT: Monthly Views Without a Pivot Table

PIVOT turns unique values in one column into separate output columns. The financial modeling use case: converting a transaction table with a Month column into a side-by-side monthly comparison without building a manual pivot table.

=QUERY('Monthly P&L'!A:D,
  "SELECT A, SUM(D)
   WHERE B = 'EBITDA'
   GROUP BY A
   PIVOT C",
  1)

Where column C contains month labels and column D contains values. Output: one row per segment, one column per month. The catch: column headers are the distinct values from C in the order they appear. If your month labels aren't consistent (Jan vs January vs 2026-01), output columns fragment. Sort your source data by date before it hits QUERY, and standardize month labels in your input range.

PIVOT also can't be combined with ORDER BY on the pivoted column.

LABEL and FORMAT

LABEL renames output columns. Format: LABEL Col1 'New Name', SUM(Col2) 'Total Revenue'. Every column you want renamed gets its own entry, comma-separated.

FORMAT applies display formatting to output values. FORMAT D '#,##0.00' formats numbers; FORMAT E 'MMM YYYY' formats dates. This is display-only - it doesn't change the underlying cell type or affect downstream formulas that reference the QUERY output range.

QUERY with IMPORTRANGE

Wrapping IMPORTRANGE inside QUERY lets you pull a filtered subset from an external file rather than importing the whole sheet. Since IMPORTRANGE returns an array, you must use Col1, Col2 notation:

=QUERY(
  IMPORTRANGE("spreadsheet_id", "Portfolio!A:H"),
  "SELECT Col1, Col3, SUM(Col6)
   WHERE Col4 = 'Active'
   AND Col7 >= date '2026-01-01'
   GROUP BY Col1, Col3
   ORDER BY SUM(Col6) DESC",
  1)

One performance note: IMPORTRANGE recalculates on every sheet recalculation. On large ranges, that compounds. For multi-tab models pulling from a frequently-recalculating source, a dedicated pull tab (IMPORTRANGE alone) followed by a local QUERY on the cached range is usually faster than nesting them.

QUERY vs SUMIFS vs FILTER

These three overlap enough that the choice isn't always obvious.

SUMIFS wins for point-in-time lookups into summary cells - =SUMIFS('P&L'!C:C, 'P&L'!B:B, ">=" & Assumptions!$B$3, 'P&L'!A:A, "Revenue") - especially when you need that number in a linked cell downstream. QUERY wins when you need a variable-length output table, like a dynamic SKU contribution margin summary where you don't know ahead of time how many rows you'll get. FILTER is the right call when you want filtered rows but no aggregation.

Most 8-tab financial models use all three: SUMIFS for scalar lookups into the summary tab, QUERY for generating display tables, FILTER for pulling scenario-specific rows into sensitivity analysis.

Common Errors and What They Mean

Unable to parse query string: syntax error in the query string. Most common causes are double quotes around string values instead of single quotes, wrong clause order, or a missing comma in a LABEL clause.

Column [X] is not a valid column: you're using letter notation when QUERY expects Col notation, or vice versa. Check whether your data argument is a direct sheet range or an array.

Column [X] is not specified in a GROUP BY clause: non-aggregated column in SELECT missing from GROUP BY.

#VALUE!: type mismatch. Often a date comparison missing the date keyword, or a number column being compared to a quoted string.

Two behaviors worth noting as of June 2026: text comparisons in QUERY are case-insensitive by default - WHERE B = 'revenue' and WHERE B = 'Revenue' return identical rows. This differs from most SQL dialects and from EXACT() in Sheets. Also, substring matching uses CONTAINS, STARTS WITH, and ENDS WITH - not LIKE. WHERE A CONTAINS 'EBITDA' is the equivalent of WHERE A LIKE '%EBITDA%'.

Where ModelMonkey Fits

When QUERY formulas across linked IMPORTRANGE tabs start getting unwieldy - or when you want GROUP BY and multi-source joins without debugging Col notation - ModelMonkey's built-in SQL engine (running on DuckDB) can query your sheet ranges directly using standard SQL syntax and write results back to the sheet. The syntax is closer to what you'd write in BigQuery or Postgres, with no Visualization API quirks to work around.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.