How to Use QUERY in Google Sheets
=QUERY(data, query, [headers]). The command goes in quotation marks, for example "select A, C where C > 100".
What QUERY does
QUERY lets you ask a table a question in a small command language, the Google Visualization API Query Language. One formula can pick columns, filter rows, group and total values, and sort the result. It replaces what would otherwise take FILTER, SORT, SUMIF and more.
QUERY syntax
=QUERY(data, query, [headers])
data: the range to query. Each column should hold one type of value: text, numbers or dates. If a column mixes types, Sheets treats the least common type as empty.query: the command, in quotation marks or in a cell that contains it.headers(optional): how many header rows are at the top of the data. If you leave it out or use-1, Sheets guesses. Set it to1when your table has one header row.
In the query you can refer to columns by letter (A, B) or as Col1, Col2. The examples below use letters, with data that starts in column A.
A step-by-step example
Here is a small sales table in cells A1:C7.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | East | Notebook | 120 |
| 3 | West | Pen | 45 |
| 4 | East | Pen | 60 |
| 5 | North | Notebook | 80 |
| 6 | West | Notebook | 150 |
| 7 | East | Backpack | 200 |
select and where
To show only the Region and Sales columns for sales above 100:
=QUERY(A1:C7, "select A, C where C > 100", 1)
This returns the header row plus three rows: East 120, West 150 and East 200.
select A, Cchooses the columns to show.where C > 100keeps only the rows that meet the test.- The last
1says the data has one header row.
When you compare text in a where clause, put the text in single quotes inside the query, as in "select A, C where B = 'Pen'".
group by
To total the sales for each region:
=QUERY(A1:C7, "select A, sum(C) group by A", 1)
This returns one row per region: East 380, North 80 and West 195. Every column in the select clause must either be in group by or be wrapped in an aggregate such as sum().
order by
To sort the result, add an order by clause at the end:
=QUERY(A1:C7, "select A, sum(C) group by A order by sum(C) desc", 1)
The biggest region comes first.
Common QUERY mistakes
- The headers argument is missing. Without it, Sheets guesses and may treat your header row as data. Add
1. - Text without single quotes. Text values in a where clause need single quotes inside the double-quoted query.
- Mixed data types in a column. A column with numbers and text mixed together can make some cells come back empty.
- Selecting a column that is not grouped. With group by, every selected column must be grouped or aggregated.
- Clauses in the wrong order. The order is select, where, group by, order by.
Try it yourself
Use the sales table above (A1:C7). Type your formula into the box, starting with =, then press Check.
Exercise 1: Select and filter
Using the sales table above (A1:C7, one header row), write a QUERY that shows the Region and Sales columns for rows where Sales is greater than 100.
Exercise 2: Total by region
Write a QUERY that shows each Region with the total of Sales for that region.
What to learn next
- FILTER: pull out the rows that match.
- SUMIF: add up the rows that match a condition.
- IMPORTRANGE: bring data in from another spreadsheet.
- SORT: sort a range with a formula.
- ARRAYFORMULA: apply one formula to a whole column. A guide is coming soon.
Syntax and rules in this guide were checked against Google's QUERY help page. See all guides.