Can QUERY reference multiple sheets at once?

Not directly in a single range argument. The workaround is vertical concatenation using the array syntax: `{'Sheet1'!A:F; 'Sheet2'!A:F}`. When you query an inline array this way, column references change from letter notation (A, B, C) to positional notation (Col1, Col2, Col3). This works reliably for union-style queries but requires both ranges to have the same number of columns and compatible data types per column.

Why does my QUERY return #VALUE! on a date filter?

Almost always a date literal format issue. Google's Visualization Query Language requires the format `date 'YYYY-MM-DD'` - not a cell reference, not a serial number, not a formatted date string. If your dates are dynamic, concatenate them: `"where A >= date '"&TEXT(B3,"YYYY-MM-DD")&"'"`. The `TEXT` function forces the correct format regardless of locale settings in the spreadsheet.

Does QUERY work on named ranges?

Yes. `=QUERY(TransactionLog, "select A, C where E > 50000", 1)` works if `TransactionLog` is a defined named range. Named ranges make the formula more readable and survive column insertions better than raw A:F references. The named range must resolve to a contiguous rectangular block - QUERY won't accept non-contiguous ranges.

How does QUERY handle blank rows in the data range?

QUERY skips fully blank rows in most cases, but a partial row (one cell filled, others empty) can cause unexpected results depending on column data types. For numeric columns, blanks are treated as null and excluded from `sum()` aggregations. For text columns, blanks match a `where Col = ''` condition. If your transaction data has mixed blank patterns, add a `where Col1 is not null` clause to filter them explicitly before aggregating.

What's the performance ceiling for QUERY on large datasets?

QUERY on ranges above 50,000 rows starts adding noticeable recalculation lag in complex models with multiple dependent formulas. At 200K rows, full-sheet recalculation can run several seconds per QUERY formula. Google's hard limit is 10 million cells per spreadsheet - well before that ceiling, QUERY performance typically becomes the constraint. For datasets that large, pre-aggregate with a scheduled Apps Script refresh, or pull through a BigQuery connection rather than querying raw transaction rows in-sheet.

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

Syntax:

=QUERY(data, query, [headers])

data is any range or named range (cross-tab references work fine). query is a string written in Google's Visualization Query Language, which supports 11 clauses: select, where, group by, pivot, order by, skipping, limit, offset, label, format, and options. The headers argument tells the function how many rows to treat as column labels - almost always 1.

Reading Multi-Tab Data with QUERY

The function handles cross-tab ranges cleanly, which is where it starts earning its place in a real model:

=QUERY('P&L'!A:G, "select A, B, C where B = 'Revenue' order by A", 1)

Reference the sheet name in single quotes, then the column range. If the sheet name has spaces - 'Q1 2026 Actuals'!A:G, for example - the quotes are required.

For dynamic tab references where the sheet name lives in a cell, build the range string with concatenation:

=QUERY(INDIRECT("'"&Assumptions!$B$2&"'!A:G"),
  "select A, C, E where E > 50000",
  1)

Assumptions!$B$2 holds the sheet name. INDIRECT resolves it at runtime. Worth noting: INDIRECT is volatile, so lean on it sparingly in large models.

WHERE Clauses: Fungsi QUERY di Spreadsheet as a Filter Engine

The where clause is where QUERY pulls ahead of most alternatives. You can filter on text, numbers, dates, and logical combinations - all in one expression.

Text match:

=QUERY('Revenue'!A:F, "select A, B, D where C = 'Enterprise'", 1)

Numeric threshold - pulling every deal above the $50K threshold:

=QUERY('Pipeline'!A:H,
  "select A, B, E where E > 50000 and F = 'Closed Won'",
  1)

Date filtering using Google's date literal format (required by the Visualization Query Language specification):

=QUERY('Transactions'!A:E,
  "select A, B, D where A >= date '2026-01-01' and A < date '2026-04-01'",
  1)

Date literals must be written as date 'YYYY-MM-DD' - not a cell reference, not a serial number. To make the date dynamic, concatenate:

=QUERY('Transactions'!A:E,
  "select A, B, D where A >= date '"
  &TEXT(Assumptions!$B$3,"YYYY-MM-DD")
  &"'",
  1)

Combining with a dynamic cross-tab reference:

=QUERY(INDIRECT("'"&Assumptions!$B$2&"'!A:H"),
  "select A, C, E where E > "&Assumptions!$C$5
  &" and D = '"&Assumptions!$D$5&"'",
  1)

Dynamic sheet, dynamic threshold, dynamic category filter - one formula. That's the pattern that handles real model complexity.

GROUP BY Aggregation: Fungsi QUERY di Spreadsheet for Multi-Level Summaries

If you're still building P&L summaries with nested SUMIFS, GROUP BY will change how you work. The clause aggregates rows by one or more dimensions without a helper column or pivot table rebuild.

Revenue by business unit, Q1 2026 actuals:

=QUERY('Transactions'!A:F,
  "select C, sum(E)
   where A >= date '2026-01-01' and A < date '2026-04-01'
   group by C
   order by sum(E) desc
   label C 'Business Unit', sum(E) 'Q1 Revenue'",
  1)

Contribution margin by SKU - the kind of slice a product controller needs weekly:

=QUERY({'Revenue'!A:F; 'COGS'!A:F},
  "select Col1, Col3, sum(Col4) - sum(Col6)
   where Col2 = 'Product'
   group by Col1, Col3
   label sum(Col4) - sum(Col6) 'Contribution Margin'",
  1)

Note the {range1; range2} vertical concatenation syntax - that's how you union two ranges before querying. Column references switch from A/B/C to Col1/Col2/Col3 when you're querying an inline array rather than a named range.

Multi-level GROUP BY for a summary by region and channel:

=QUERY('Sales'!A:G,
  "select B, C, sum(F)
   group by B, C
   order by B, sum(F) desc
   label B 'Region', C 'Channel', sum(F) 'Net Revenue'",
  1)

The label clause renames output columns in the result. Without it, you get headers like sum(F), which looks broken in a board pack.

QUERY vs. SUMIFS - When to Use Each

Neither wins unconditionally. The choice depends on what you need the output to look like.

Criteria	QUERY	SUMIFS
Returns tabular output (multiple columns)	Yes	No
Works on cross-tab arrays	Yes	Limited
Handles date range filters natively	Yes (with literals)	Yes (with operators)
Supports GROUP BY aggregation	Yes	No
Compatible with structured table references	Partial	Full
Readable by non-formula users	Low	Medium
Performance on 200K+ rows	Slower	Faster

For a single-cell lookup or SUMIF equivalent, SUMIFS is faster and more readable. For anything that returns a result set - a filtered transaction list, a revenue summary by dimension, a cross-tab aggregation - QUERY wins. On datasets above 200K rows, expect recalculation lag. Google's hard cell limit is 10 million cells per spreadsheet (per Google's own specification), and QUERY on a range approaching that ceiling can grind a model to a crawl.

Cross-Tab and Multi-Sheet QUERY Syntax

The most common pattern in a real financial model is pulling from a structured data tab into a summary tab:

=QUERY('P&L'!A:H,
  "select A, B, sum(E)
   where C = 'Gross Profit'
   group by A, B
   order by A",
  1)

For a model where the actuals tab name changes each period (stored in Assumptions!$B$1):

=QUERY(INDIRECT("'"&Assumptions!$B$1&"'!A:H"),
  "select A, C, D, E where B = 'Operating' and E > 0 order by A",
  1)

For combining Q1 2026 and Q2 2026 actuals side-by-side using pivot:

=QUERY('Actuals'!A:D,
  "select A, B, sum(D) where C != '' group by A, B pivot C",
  1)

The pivot clause reshapes unique values in column C into separate output columns - the equivalent of a pivot table, but formula-driven and auto-refreshing when the source data updates.

Generating Complex QUERY Formulas Without Debugging for 20 Minutes

The practical limit on QUERY isn't the function itself - it's building the query string when you need dynamic dates, dynamic column references, and conditional logic all in the same expression. The string concatenation gets messy fast.

If you're working in ModelMonkey, you can describe what you need in plain language ("filter transactions where close date falls in Q2 2026 and deal size exceeds $50K, then group by rep and return total and count") and get the formula back ready to paste. It won't write your model, but it removes the 15-minute debugging loop that usually comes from a malformed date literal or a misplaced quote.

For anything built from scratch, the safest approach is to construct the query string in a helper cell first:

= "select A, B, sum(E) where A >= date '"
  &TEXT(Assumptions!$B$3,"YYYY-MM-DD")
  &"' and A < date '"
  &TEXT(Assumptions!$B$4,"YYYY-MM-DD")
  &"' group by A, B order by A"

Then reference that cell as the query argument. Easier to debug, easier to hand off.