QUERY is Google Sheets' secret weapon — a mini SQL engine that filters, groups, sorts, and pivots data with a single formula. Once you're comfortable with it, you'll stop reaching for SUMIFS and INDEX-MATCH combos entirely. This guide covers the syntax, the tricky bits (dates!), and the patterns that make QUERY genuinely powerful.
Three arguments:
A1:D1000, or a named range, or another formula's output)Returns everything. Not useful on its own, but shows the pattern.
QUERY uses column letters that correspond to positions within your source range, not the sheet's actual columns.
If your data range is C1:F100, column A in the query means column C in the sheet. Off-by-one column bugs are the #1 source of QUERY frustration.
Order matters. Output columns appear in the order you list them, not the source order.
Only Region and Revenue. Skips Date and Product entirely.
Available aggregate functions: SUM, AVG, COUNT, MAX, MIN.
Total revenue across everything.
Adds a computed column: 15% of Revenue. Useful for on-the-fly calculations without extra columns in your source data.
QUERY has no CONCAT function in its dialect. For text combinations, do it before or after QUERY — with helper columns or by wrapping in ARRAYFORMULA.
Text values need single quotes. Numbers don't.
Available operators: =, !=, <>, <, >, <=, >=.
Instead of chaining ORs, use MATCHES with a regex:
QUERY doesn't have SQL's IN keyword, but MATCHES with a regex covers the same ground and is often cleaner.
% is the wildcard for zero-or-more characters. LIKE is case-sensitive.
Filters out rows where Revenue is blank. Useful for cleaning imported data.
Most QUERY operators are case-sensitive. "west" won't match "West". Wrap in LOWER for case-insensitive comparison:
Every unique region and its total revenue. This is the equivalent of a small pivot table in one formula.
Every unique Region + Product combination and its total. All non-aggregated SELECT columns must appear in GROUP BY.
Region, total, average, and count in one output.
Only counts revenue over 500. WHERE runs before GROUP BY.
Any non-aggregated column in SELECT must appear in GROUP BY. Break this rule and QUERY returns "Column X should be added to group by clause, or an aggregation function should be used". Beginners hit this constantly — remember: aggregate it, group by it, or don't select it.
Highest total revenue first. ASC (or omitted) for ascending; DESC for descending.
Region alphabetical, then revenue high-to-low within each region.
Top 5 regions by revenue.
Rows 11-20 in the sorted result. Perfect for building paginated views.
Without LABEL, QUERY names the aggregate column "sum D" — ugly and unhelpful. LABEL cleans it up.
Empty labels remove the header entirely. Useful when embedding QUERY output into a custom-styled report.
QUERY formats the output values without touching the source. Same syntax as Excel's number formats.
Dates confuse people more than any other QUERY topic. The rules:
Format: date 'yyyy-mm-dd'. The lowercase date keyword is required. Single quotes required. Any other format fails.
Filters to Q1 2026. Using < for the end date (not <=) avoids including part of the next quarter.
TEXT with yyyy-mm-dd converts B1 into the exact format QUERY expects, then concatenation drops it into the string. Note the escaped single quotes.
Use datetime instead of date when time-of-day matters.
Total revenue by year-month. Available date extractors: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, QUARTER, DAYOFWEEK.
QUERY's MONTH function is 0-indexed — January is 0, December is 11. Weird but true. Add 1 in a wrapper formula if you want conventional numbering, or use FORMAT to display month names instead.
E1 holds a dropdown value (via data validation). Change the dropdown → QUERY re-runs with the new value.
Numeric values don't need single quotes in the query string.
SUMIFS/COUNTIFS have wildcards; QUERY doesn't in the same way. Pattern for an "All" option:
When dropdown is "All", the WHERE clause is empty. Otherwise it filters. Concatenation lets you construct the query string conditionally.
As many cell-driven conditions as you need. For anything past 3-4, put the query string in a helper cell and reference it — easier to read and debug.
QUERY can produce cross-tabulations natively — no pivot table needed.
Regions become rows. Products become columns. SUM(D) fills the cells. Beautifully compact.
Pull data from another spreadsheet and query it in one formula.
When the data source is an IMPORTRANGE, QUERY doesn't see A, B, C — it sees Col1, Col2, Col3. This trips up almost everyone the first time.
Pull only what you need. Big IMPORTRANGE queries slow the whole spreadsheet — filtering aggressively at the QUERY level helps.
QUERY itself doesn't do running totals, but combine with ARRAYFORMULA and MMULT for a cumulative column. Simpler approach — sort by date, then reference the QUERY output:
In the adjacent column: =SUMIF(A2:A, "<="&A2, B2:B) for the running total.
QUERY doesn't have a native "top N per group" clause. Workaround using LIMIT with a filter:
Repeat for each region, or wrap in a more complex ARRAYFORMULA if you need it in one shot.
Filters out empty rows without affecting your source data.
QUERY's COUNT works on any column and counts non-blank values. For distinct counts, wrap in COUNTUNIQUE outside:
Filters at the row level before aggregating.
GROUP BY without an aggregation returns distinct combinations. Faster than UNIQUE on multi-column ranges.
QUERY can query another QUERY's output. Store the first in a helper range or wrap directly:
Filters the aggregated results. Note the Col1/Col2 syntax for the inner query's output.
QUERY has no built-in total row. Add one with SUM below, or wrap in a range that includes a manual total. Ugly but works.
Curly-brace stacking combines ranges vertically. Header argument is 0 because no headers in the stacked data. Use Col1/Col2 syntax.
QUERY output can be styled by placing it below a manually-formatted header row. Set the header argument to 0 and LABEL your columns to blank strings — the query returns only data, and your styled headers stay untouched.
Query strings need double quotes on the outside. Text values inside the query need single quotes. Get them backwards and you get parse errors or unexpected behavior. Watch every quote character.
When building queries with cell references, the syntax gets dense: "...WHERE B = '"&E1&"'...". That's double-quote, apostrophe, ampersand, cell-ref, ampersand, apostrophe, double-quote. Miss any of them and QUERY parses it wrong. Build in stages — start with a working literal query, then swap in the cell reference one piece at a time.
QUERY's comparisons are case-sensitive by default. "West" doesn't match "west" or "WEST". Wrap in LOWER for case-insensitive matching.
Direct range queries use A, B, C. IMPORTRANGE queries use Col1, Col2. Array literal queries (curly braces) also use Col1, Col2. Choosing the wrong one is a common cause of "no column" errors.
You can't do SELECT A + B for text. Use helper columns with CONCATENATE, or wrap the whole QUERY in a formula that adds the concatenation after.
When a query matches nothing, it returns #N/A or "No data" depending on how it's structured. Wrap in IFERROR for user-friendly fallbacks: =IFERROR(QUERY(...), "No results").
QUERY infers each column's type from the data. If a numeric column has one text value (say, "N/A"), QUERY may treat the whole column as text and comparisons fail. Clean your data or use VALUE() in a helper column.
QUERY is powerful but its syntax rewards frequent use — the dates, the Col1 vs A, the quote escaping. Sheets Wizard generates the exact QUERY formula from plain-English intent ("sum revenue by region for Q1, sorted descending"), with proper syntax and formatting.
Install Sheets Wizard →Runs SQL-style commands against a range — filter, group, sort, aggregate — with one formula. Replaces multiple SUMIFS, COUNTIFS, and lookup formulas.
Use the date keyword with yyyy-mm-dd format: WHERE A >= date '2026-01-01'. For cell references: WHERE A >= date '"&TEXT(B1,"yyyy-mm-dd")&"'.
Concatenate the cell into the query string: =QUERY(data, "SELECT A WHERE B = '"&C1&"'", 1). Text values need single quotes; numbers don't.
Column letters don't match the range positions. If data starts at C1, column A in the query is the sheet's column C. IMPORTRANGE queries use Col1, Col2 instead of A, B.
Yes, with the PIVOT clause: SELECT A, SUM(D) GROUP BY A PIVOT B. A becomes rows, B becomes columns, SUM(D) fills the cells.