Filtering, sorting, and summarizing a dataset usually means three separate tools and a pivot table. The QUERY function does all of it in one cell, using a stripped-down version of SQL — the language databases speak. Write SELECT B, SUM(C) WHERE A > 100 GROUP BY B and you’ve filtered, grouped, and totaled in a single formula that recalculates the instant the data changes. This walks through the clauses one at a time, in the order they go, so by the end you can read and write the kind of QUERY that replaces a whole worksheet of helper formulas.
The three arguments
Every QUERY has the same skeleton: the data to read, the query string that does the work, and a number telling it how many header rows to expect.
=QUERY(A1:D, "SELECT A, C WHERE C > 100", 1)The first argument is the range. The second — always in quotes — is the query itself, written in QUERY’s SQL-like language. The third, 1 here, tells QUERY your data has one header row so it doesn’t treat your column titles as data. That trailing 1 is easy to forget and worth making a habit: leave it off and your headers can end up mixed into the results or your column types misread. Everything interesting happens inside the quoted string, and it’s built from clauses that always appear in a fixed order.
SELECT and WHERE: choose and filter
SELECT picks which columns come back; WHERE decides which rows. Together they cover the everyday “show me just these, where that’s true” question.
=QUERY(A1:D, "SELECT A, B, D WHERE D = 'Electronics'", 1)This returns columns A, B, and D for only the rows where column D equals “Electronics.” Two rules trip up nearly everyone at the start. First, you reference columns by their letter (A, B, C), never by their header name — QUERY doesn’t see “Category,” it sees column D. Second, text you’re matching goes in single quotes inside the double-quoted string: 'Electronics', not Electronics. Forgetting those single quotes is the single most common QUERY error. WHERE also understands >, <, !=, and words like contains and starts with for text.
Filtering text and dates
WHERE goes well beyond equals. For text, contains, starts with, and matches (which takes a regular expression) handle partial matches a plain = can’t.
=QUERY(A1:D, "SELECT A, C WHERE B contains 'North' AND D >= date '2026-01-01'", 1)Two things to notice. Conditions combine with AND and OR, so you can stack a text match and a date bound in one filter. And dates need the special literal form — the keyword date followed by a 'YYYY-MM-DD' string in single quotes — not a bare date, which QUERY won’t recognize. That date syntax catches people out constantly; once it clicks, “everything since the start of the year” becomes a clause you write without thinking. Google’s QUERY function reference documents every operator and keyword the language accepts.
GROUP BY: summarize like a pivot
This is where QUERY earns its reputation. GROUP BY collapses rows that share a value and lets you aggregate the rest — the same job a pivot table does, written as a line of text.
=QUERY(A1:C, "SELECT B, SUM(C) GROUP BY B", 1)This returns each unique value in column B alongside the total of column C for that group — sales per region, hours per project, count per category. Swap SUM for AVG, COUNT, MAX, or MIN depending on the question. The rule GROUP BY enforces: every column in your SELECT must either be grouped or wrapped in an aggregate, because QUERY has to know what to do with it when it collapses the rows. Get a “column not in GROUP BY” error and that’s what it’s telling you. You can also group by two columns at once — GROUP BY B, D — to get a total for every combination, like sales per region per category. This single clause replaces a surprising number of pivot tables when you want the summary inline rather than on its own sheet.
ORDER BY and LIMIT: rank and trim
ORDER BY sorts the result; LIMIT caps how many rows come back. Stack them and “the top five by sales” is one formula.
=QUERY(A1:C, "SELECT A, C ORDER BY C DESC LIMIT 5", 1)This sorts by column C highest-first (DESC; use ASC for lowest-first) and returns only the top five rows. Because the whole thing re-evaluates live, that leaderboard updates itself as the underlying numbers change — no re-sorting, no manual trimming. Order matters in more than the sort: the clauses themselves must appear in sequence — SELECT, then WHERE, then GROUP BY, then ORDER BY, then LIMIT. Put them out of order and QUERY throws a parse error, so when a formula won’t run, check the clause sequence before the syntax.
LABEL and the column-letter rule
By default an aggregated column gets an ugly header like “sum Sales.” LABEL renames it to something readable, which matters when the result is going in front of anyone.
=QUERY(A1:C, "SELECT B, SUM(C) GROUP BY B LABEL SUM(C) 'Total Sales'", 1)Col1, Col2, Col3 instead of A, B, C. Same idea, different labels — the first column is Col1 rather than A. Mixing the two is a frequent source of “unable to parse query” messages.
QUERY across files with IMPORTRANGE
QUERY isn’t limited to the current sheet. Wrap an IMPORTRANGE inside it and you can filter and summarize data that lives in an entirely different spreadsheet, live.
=QUERY(IMPORTRANGE("url","Sheet1!A:C"), "SELECT Col1, SUM(Col3) GROUP BY Col1", 1)Here QUERY reads the imported range and groups it just as it would local data — note the Col1/Col3 syntax the import forces. This pairing is genuinely powerful: a central data file owned by one person, with summary views pulled and shaped in everyone else’s sheets, each updating on its own. The guide to IMPORTRANGE covers the one-time permission step it needs before the formula will return anything.
One formula instead of a worksheet
SELECT * to confirm the range is right, add WHERE and check the rows, then layer in GROUP BY and ORDER BY. Debugging a long QUERY all at once is miserable; building it one clause at a time, watching the result after each, is painless.
SELECT to choose, WHERE to filter, GROUP BY to summarize, ORDER BY and LIMIT to rank — those five clauses, in that order, cover the overwhelming majority of real analysis. QUERY shines when you want the answer to live and update inline; for a single targeted figure a plain formula is simpler, and for heavy interactive slicing a pivot still wins. But for “filter to this, group by that, sorted, top ten,” nothing in Sheets is more concise. It’s the engine behind most live reports — feed its output into a real-time dashboard and the numbers shape and refresh themselves. Learn the clause order, mind the column letters and the single quotes, and a formula you can read aloud does the work of an entire worksheet.