SheetsdrillQUERY
QUERY(data, query, [headers]) runs an SQL-like query over a range in Google Sheets: select columns, filter with where, aggregate with group by, sort with order by and cap rows with limit, in one formula. Columns are referred to by letter (A, B, C), and the result spills into the cells below and to the right.
Syntax
=QUERY(data, "select ... where ... group by ... order by ... limit ...", [headers])| Part | What it is |
|---|---|
data | The range to query, for example A1:D100. Each column must hold one data type, or QUERY treats the minority type as blank. |
query | The query text in Google Visualization API Query Language, in double quotes. Column letters refer to the sheet columns; text values go in single quotes. |
headers | Optional number of header rows at the top of data. 1 keeps the first row as labels; omit and Sheets guesses. |
Examples
=QUERY(A1:C100, "select A, C where B = 'West'", 1)Result: Names and amounts of every row whose region is West, with a header row
Text comparisons are case sensitive and the text goes in single quotes.
=QUERY(A1:C100, "select B, sum(C) group by B order by sum(C) desc", 1)Result: One row per region with its total, largest first
Every selected column that is not aggregated must appear in group by.
=QUERY(A1:C100, "select count(A) where C > 500", 1)Result: A single number: how many rows have an amount above 500
Common mistakes
- #VALUE! with Unable to parse query string usually means a missing single quote around text, a column letter that does not exist in the range, or a clause in the wrong order (select, where, group by, order by, limit).
- A numeric column comes back empty because some cells hold text; QUERY picks the majority type per column and blanks the rest, so convert the column with VALUE or clean the data first.
- Comparing dates fails unless you write them as date '2026-11-03' in the query, or build the string with TEXT(A1, "yyyy-mm-dd").
Practice QUERY
These drills check your answer on live data, free, no account needed.
- QUERY for a totalUse QUERY to add up the East region's sales, and return just the number with no header row. Answer in F1.
- Top region with QUERYUse QUERY to total revenue by region, sort the totals from largest to smallest and keep one row. Return just the region name in F1 (INDEX helps).
- Count with QUERYUse QUERY to count the shipments whose status is "Late" and whose cost is over 100. Return just the number, with no header. Answer in G1.
Questions
Can QUERY reference a cell value in the where clause?
Yes, by concatenating it into the string: "select A where B = '"&E1&"'" for text, or "where C > "&E2 for a number.
How do I query data from another sheet or file?
Use the range with the sheet name, for example QUERY(Sales!A:D, ...), or wrap IMPORTRANGE in QUERY and refer to columns as Col1, Col2 instead of letters.
Related
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.
- SORT and SORTNHow SORT and SORTN in Google Sheets sort data by one or more columns with a formula and return the top or bottom N rows, with examples and common errors.
- UNIQUEHow the UNIQUE function in Google Sheets returns a list of distinct values or rows, how to sort and count them, and the errors people run into.
- IMPORTRANGEHow to use IMPORTRANGE in Google Sheets to import a range from another spreadsheet, allow access, combine it with QUERY or FILTER, and fix the errors.