Google Sheets QUERY Syntax: The FP&A Reference

This article covers every clause you'll actually use in a financial model, with formulas that reach across tabs.

The Basic QUERY Syntax Structure

=QUERY(data, "SELECT col [WHERE condition] [GROUP BY col] [ORDER BY col] [LIMIT n] [LABEL col 'name'] [FORMAT col 'pattern']", headers)

According to Google's official QUERY function documentation (support.google.com/docs, last verified May 2026), the supported clauses in order are: SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, and FORMAT. You can't reorder them. Put WHERE before GROUP BY or you get a parse error.

Columns are referenced by letter - Col1, Col2, etc. - not by header name. That's the first thing that surprises people coming from SQL.

=QUERY('Raw Data'!A:F,
  "SELECT Col1, Col2, SUM(Col4)
   WHERE Col3 = 'North America'
   GROUP BY Col1, Col2
   ORDER BY SUM(Col4) DESC",
  1)

The 1 at the end tells QUERY to treat the first row as a header. Use 0 if your range has no headers, -1 to auto-detect.

WHERE Clause: Core QUERY Syntax Rules

WHERE is where most syntax errors originate. The rules:

Strings require single quotes inside the double-quoted query string:

WHERE Col2 = 'EMEA'

Numbers need no quotes:

WHERE Col4 > 250000

Dates require the date keyword:

WHERE Col1 >= date '2026-01-01'

Cell references can't go inside the string literal - you concatenate them outside:

=QUERY('P&L'!A:E,
  "SELECT Col1, Col3, Col4
   WHERE Col2 = '" & Assumptions!$B$5 & "'
   AND Col4 > " & Assumptions!$C$3,
  1)

This is how you wire a QUERY to your assumptions tab. The region filter in $B$5 and the revenue floor in $C$3 drive every output dynamically. Change one cell, the whole query reruns.

Null handling: QUERY drops rows with null values in grouped columns by default. If you're aggregating headcount data with gaps, wrap with IFERROR or pre-fill blanks with "".

QUERY Syntax for Multi-Tab Financial Models

Single-tab QUERY formulas are fine for demos. Real models pull across sheets.

Contribution margin by product line, pulling from a transaction tab:

=QUERY('Transactions'!A:H,
  "SELECT Col3, SUM(Col5), SUM(Col6), SUM(Col5)-SUM(Col6)
   WHERE Col8 = 'Q1 2026'
   AND Col2 != 'Internal'
   GROUP BY Col3
   ORDER BY SUM(Col5)-SUM(Col6) DESC
   LABEL Col3 'Product Line', SUM(Col5) 'Revenue', SUM(Col6) 'COGS', SUM(Col5)-SUM(Col6) 'Gross Profit'",
  1)

This surfaces contribution margin by SKU from 4,200 rows of transaction data without a pivot table or helper column.

Rolling headcount pull for runway sensitivity:

=QUERY(Headcount!$A:$G,
  "SELECT Col2, Col3, SUM(Col5)
   WHERE Col6 = '" & Dashboard!$B$2 & "'
   AND Col4 >= date '" & TEXT(Dashboard!$B$3,"yyyy-mm-dd") & "'
   GROUP BY Col2, Col3
   ORDER BY Col2",
  1)

Dashboard!$B$2 holds the department filter. Dashboard!$B$3 holds the start date. Change either and the runway projection reruns instantly across a 12-entity model.

GROUP BY, PIVOT, and Aggregation QUERY Syntax

GROUP BY requires that every non-aggregated column in SELECT also appears in GROUP BY. This is standard SQL behavior, but QUERY will throw Invalid query: Column [X] is not in grouping with no further explanation.

PIVOT is the clause most analysts don't know about. It transposes a GROUP BY result into a cross-tab:

=QUERY('P&L'!A:E,
  "SELECT Col1, SUM(Col4)
   WHERE Col3 != 'Intercompany'
   GROUP BY Col1
   PIVOT Col2",
  1)

If Col2 is quarter (Q1, Q2, Q3, Q4), this produces revenue by entity × quarter in one formula. No helper columns, no pivot table refresh. The column headers are dynamic - add Q5 data to the source and the pivot grows automatically.

Supported aggregations per the GViz Query Language Reference (developers.google.com/chart/interactive/docs/querylanguage, version as of May 2026): SUM, AVG, COUNT, MAX, MIN. There's no MEDIAN or STDEV - for those you need array formulas wrapped around the QUERY output.

LABEL and FORMAT: Cleaning QUERY Output for Board Packs

Raw QUERY output uses column letters as headers (SUM(Col4) literally). For a quarterly board pack you need readable labels and formatted numbers.

=QUERY('Revenue'!A:F,
  "SELECT Col1, SUM(Col4), AVG(Col4), MAX(Col4)
   WHERE Col2 = 'Recurring'
   AND Col3 >= date '2026-01-01'
   GROUP BY Col1
   ORDER BY SUM(Col4) DESC
   LABEL Col1 'Segment', SUM(Col4) 'Total ARR', AVG(Col4) 'Avg Deal', MAX(Col4) 'Largest Deal'
   FORMAT SUM(Col4) '$#,##0', AVG(Col4) '$#,##0', MAX(Col4) '$#,##0'",
  1)

FORMAT accepts standard Google Sheets number format strings. $#,##0 for whole-dollar amounts, $#,##0.00 for cents, #,##0.0% for percentages. The formatting is display-only - the underlying cell value stays numeric, so downstream SUMIFS against this output still work.

One caveat: FORMAT applies to the QUERY output cell rendering, not to the cell format itself. If someone copies the output to another sheet, the formatting strips. For a board pack, paste-as-values before sending.

QUERY Syntax vs. SUMIFS: When to Use Each

Both can aggregate across tabs. The choice depends on what you're building.

SUMIFS wins when you need one number in one cell that other formulas depend on. QUERY wins when you need a formatted table output for reporting. Mixing them - SUMIFS for model logic, QUERY for output display - is usually the right call.

Common QUERY Syntax Errors and What They Mean

PARSE_ERROR - Usually a clause out of order, or a string that's missing its closing single quote. Check that your concatenation closes every '.

Invalid query: Column [ColN] is in SELECT but not in GROUP BY - You aggregated some columns but forgot to add a non-aggregated column to GROUP BY.

Invalid query: Unable to parse date string - Your date concatenation isn't producing yyyy-mm-dd format. Wrap with TEXT(date_cell,"yyyy-mm-dd").

#N/A with no error message - The query returned 0 rows. Usually a filter that matches nothing. Check your string values - QUERY is case-sensitive. 'North America' and 'north america' are different.

#VALUE! - Mixed data types in the source column. A column QUERY thinks is numeric that contains even one text cell will break numeric comparisons. Clean the source or use ISNUMBER filters.

Named Ranges and QUERY Performance

As of May 2026, named ranges work as the data argument in QUERY:

=QUERY(TransactionLedger,
  "SELECT Col1, Col2, SUM(Col5)
   WHERE Col3 = 'Actuals'
   GROUP BY Col1, Col2",
  1)

This makes formulas readable and decouples the query from the physical tab structure. If you reorganize tabs, update the named range definition and every QUERY that references it updates automatically.

Performance degrades noticeably on ranges above 50,000 rows. QUERY recalculates on every edit to any cell in the source range. For large transaction logs, filter to a smaller working range first using a helper tab, then run QUERY against that. The recalculation time difference can be 10x.

If you need to join data across two ranges - say, matching transaction IDs against a reference table - QUERY can't do it natively. You need to either merge the data first or use the ModelMonkey sidebar, which runs actual SQL against your sheet ranges via an embedded DuckDB engine and supports joins, window functions, and aggregations that QUERY doesn't.