Google Sheets QUERY function for reporting

QUERY runs a SQL-like statement against a range and returns a new table. One formula can select columns, filter rows, group records, sum measures, sort the result, and rename headers. The result spills below and to the right, and it recalculates when the source changes. A filter view hides rows for one viewer; QUERY produces a table that charts and other formulas can read.

The syntax has three arguments: the data range, the query text, and the header row count. Keeping the query text in a cell makes the formula readable: =QUERY(Data!A1:F, Query!B2, 1). A reviewer can inspect the logic without nested parentheses.

QUERY parses clauses in a fixed sequence: select, where, group by, pivot, order by, limit, offset, label. Writing them in another order causes a parse error even when the logic is sound. The usual mistakes are placing order by before group by, or label before the sorting clause.

A working clause looks like this: select B, sum(F), count(A) group by B order by sum(F) desc label sum(F) 'Revenue', count(A) 'Orders'. Use Col1, Col2 when the range does not start at column A; that notation is unambiguous.

Grouping replaces a block of SUMIFS formulas. The clause select B, sum(F) group by B returns one row per value in B, with the matching sum of F. Add count(A) for volume and avg(F) for an average. Blank cells are ignored by the aggregates, which can hide missing data.

Column types matter. QUERY decides how to treat a column from the values it finds. A column of numbers with a few text entries can be read as text, and the numeric sum then returns null or drops rows. Store numbers as numbers, dates as dates, and keep header cells out of the data range.

The where clause accepts comparisons, is null, is not null, contains, starts with, and matches. Text literals sit in single quotes inside the query text, which is one reason to keep the query in a cell rather than inside the formula. Dates need the date keyword and an ISO date, not a regional format.

Cell-driven criteria are where QUERY becomes awkward. The query text is a single string, so a changing region or month has to be concatenated into it. That fragility is a design signal: use FILTER when users change criteria, and reserve QUERY for published definitions.

QUERY scans the range it is given. Whole-column references such as A:F force it to consider every row on the sheet, and several such formulas in one file will slow editing. Use a bounded range with headroom, or a named range that you extend when the data grows. Import once into a staging sheet and query that sheet, rather than wrapping every query around IMPORTRANGE.

Keep the spill area empty. The output grows when new categories appear, so notes or formulas placed to the right will eventually cause a #REF error. Protect the block and leave a margin. Wrap the formula in IFERROR only when an empty result is acceptable; otherwise you turn a broken query into a blank report.

Use QUERY for a stable, grouped, labelled view that several people read and that feeds a chart. Use FILTER for row-level views where the user changes a criterion in a cell. Use a pivot table when someone needs to drag fields and explore without editing formulas. The mistake is rebuilding the same summary three ways and letting the totals drift.

Before publishing, reconcile the output against an independent total: a SUMIFS on the source, a pivot, or a control cell. Check the row count and one filtered segment, not just the grand total. A grand total can agree while a category is missing.