The QUERY function: SQL-style questions on a sheet

QUERY is the one Google Sheets function with no direct Excel equivalent, and screenings like it for that reason. It lets you ask a question of a range in a short language that resembles SQL: pick columns, keep rows that match, sort, and group. One formula replaces a chain of FILTER, SORT and SUMIF.

How it is written

=QUERY(range, "query text", headers). The query text is in double quotes. Inside it, columns are referred to by their letter within the range, in capitals: the first column of the range is A even if the range starts in column D of the sheet. The third argument is the number of header rows, usually 1.

Suppose A1:D100 holds Date, Region, Product and Sales. Example 1: all rows for the North region. =QUERY(A1:D100,"select * where B = 'North'",1). Text values inside the query use single quotes, because the whole query is already inside double quotes.

Filtering, sorting and limiting

Example 2: products with sales above 500, highest first, top five only.

=QUERY(A1:D100,"select C, D where D > 500 order by D desc limit 5",1)

The clauses must appear in this order: select, where, group by, order by, limit. Writing order by before where gives an error.

Dates need a special form: where A >= date '2024-01-01'. Comparing a date column to plain text fails silently and returns nothing.

Grouping and totals

Example 3: total sales per region.

=QUERY(A1:D100,"select B, sum(D) group by B order by sum(D) desc",1)

Every column in select that is not inside sum, count, avg, max or min must appear in group by. Forgetting this is the most common cause of a grouping error. To rename the total column, add label sum(D) 'Total sales' at the end.

Example 4: count orders per product in March. "select C, count(A) where month(A) = 2 group by C". The month() function in QUERY counts from 0, so March is 2. This trips up almost everyone the first time.

Common mistakes and a practice habit

• Lower-case column letters. b = 'North' fails; use B.
• Double quotes inside the query text. Use single quotes for values.
• Mixed data types in one column, such as numbers stored as text. QUERY treats the column as the majority type and blanks out the rest.
• Wrong header count. If you pass 0 but the range has a header row, the header is treated as data and the column becomes text.

Paste any list with at least a date, a category and a number into a sheet. Write five QUERY formulas: a filter, a sort with limit, a group with sum, a group with count, and one with a date condition. Check each against a manual filter so you learn what each clause changes.