QUERY runs a SQL-style query on a range: pick columns, filter rows, sort them, total them by group and rename the headers, all in one formula. It does in a single cell what would otherwise take FILTER, SORT and a pivot table together, which makes it one of the most useful functions in Google Sheets for reports and dashboards.
Syntax
=QUERY(data, query, [headers])
data
The range to query, including its header row, e.g. A1:E7.
query
The query, written as text in double quotes, e.g. "select A, E where B = 'West'". Clauses must come in a fixed order: select, where, group by, order by, limit, label.
headers
Optional. The number of header rows at the top of data, usually 1. If you leave it out, Sheets guesses, and it occasionally guesses wrong.
Practice QUERY
Task 1 of 6
Use QUERY to return just the Rep and Revenue columns, with their headers. Formula in G1.
G1
fx
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
QUERY examples
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
A
B
C
D
E
1
Rep
Region
Product
Units
Revenue
2
Ava
West
Monitor
12
$2,400.00
3
Liam
East
Keyboard
30
$1,500.00
4
Maya
West
Mouse
45
$900.00
5
Noah
North
Monitor
8
$1,600.00
6
Zara
East
Monitor
15
$3,000.00
7
Omar
West
Keyboard
22
$1,100.00
Sales by rep
Formula
Result
What it shows
=QUERY(A1:E7, "select A, E", 1)
Rep
Revenue
Ava
$2,400.00
Liam
$1,500.00
Maya
$900.00
Noah
$1,600.00
Zara
$3,000.00
Omar
$1,100.00
Just two columns, with their headers.
=QUERY(A1:E7, "select A, C, E where B = 'West'", 1)
Rep
Product
Revenue
Ava
Monitor
$2,400.00
Maya
Mouse
$900.00
Omar
Keyboard
$1,100.00
Filter rows with where. Text goes in single quotes.
=QUERY(A1:E7, "select A, E where E > 1500 order by E desc", 1)
Rep
Revenue
Zara
$3,000.00
Ava
$2,400.00
Noah
$1,600.00
Filter and sort, largest first.
=QUERY(A1:E7, "select A, E order by E desc limit 3", 1)
Rep
Revenue
Zara
$3,000.00
Ava
$2,400.00
Noah
$1,600.00
The top 3 by revenue.
=QUERY(A1:E7, "select B, sum(E), count(A) group by B", 1)
Region
sum Revenue
count Rep
East
4500
2
North
1600
1
West
4400
3
A pivot-style summary per region.
=QUERY(A1:E7, "select C, sum(D) group by C label sum(D) 'Units sold'", 1)
Product
Units sold
Keyboard
52
Monitor
35
Mouse
45
label renames a header.
=QUERY(A1:E7, "select * where C contains 'Key' or D < 10", 1)
Rep
Region
Product
Units
Revenue
Liam
East
Keyboard
30
1500
Noah
North
Monitor
8
1600
Omar
West
Keyboard
22
1100
contains, or, and select * for all columns.
=QUERY(A1:E7, "select A where B = 'west'", 1)
Rep
Comparisons are case-sensitive, so nothing matches and only the header comes back.
=QUERY(A1:E7, "select A, sum(E)", 1)
#VALUE!
Selecting a plain column next to sum() without group by is an error.
QUERY clauses at a glance
A query is built from clauses that must appear in this order. Every clause is optional, but the order is fixed.
Clause
What it does
Example
select
Columns to return, or * for all of them
select A, C, E
where
Keeps only rows that match
where E > 1000 and B != 'East'
group by
One output row per distinct value, with aggregates
select B, sum(E) group by B
order by
Sorts the result, asc (default) or desc
order by sum(E) desc
limit
Keeps the first N rows
limit 10
offset
Skips the first N rows
offset 5
label
Renames output headers
label sum(E) 'Revenue'
Conditions you can use in where
Comparisons: =, != (or <>), <, >, <=, >=.
Text: contains 'mon', starts with 'A', ends with '.com', like 'K%' (% is any text, _ is one character) and matches '...' for regular expressions.
Blanks: is null and is not null.
Combine with and, or and not, and use parentheses to group.
Aggregates for group by
sum(), avg(), count(), min() and max(). Every column in the select clause must either be inside an aggregate or listed in group by, otherwise QUERY returns an error. Without group by, an aggregate summarises the whole table in a single row.
Using a cell value in the query
The query is just text, so you can build it with &. To filter by whatever is typed in H1: =QUERY(A1:E7, "select A, E where B = '"&H1&"'", 1). Note the single quotes on either side of the joined value. For a number, leave them out: "where E > "&H2.
Letters or Col1?
When the data is a range in the sheet, refer to columns by their uppercase letters. When the data comes from another function, such as IMPORTRANGE, FILTER or an array, there are no letters, so use Col1, Col2 and so on.
Tips and common mistakes
Put text values in single quotes inside the query: "where B = 'West'". Double quotes would end the query string early.
Column letters must be uppercase. "select a" is an error.
QUERY gives each column a single data type, the most common one. In a mostly-numeric column, the text values are treated as empty, which is a frequent cause of rows that seem to vanish.
Text comparisons are case-sensitive. To match regardless of case, use lower(B) = 'west' (Sheets supports lower() and upper() inside where).
An aggregate's default header is the function name plus the column header, such as "sum Revenue". Rename it with label.
If nothing matches, QUERY returns only the header row, or #N/A when there is no header to show.