Google SheetsThe QUERY language, clause by clause
The QUERY function takes an instruction written in a small language of its own. This guide goes through that language one clause at a time, using one sample table. If you have used SQL, most of it will look familiar. If you have not, each clause is a plain English word that does what it says.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Salesperson | Region | Product | Units | Amount |
| 2 | 05-Jan-2026 | Asha | North | Notebook | 20 | 1200 |
| 3 | 12-Jan-2026 | Ravi | South | Pen | 50 | 500 |
| 4 | 20-Jan-2026 | Meena | North | Bag | 4 | 3000 |
| 5 | 02-Feb-2026 | Asha | East | Pen | 30 | 300 |
| 6 | 14-Feb-2026 | Kabir | South | Notebook | 15 | 900 |
| 7 | 28-Feb-2026 | Ravi | North | Bag | 2 | 1500 |
| 8 | 03-Mar-2026 | Meena | East | Notebook | 40 | 2400 |
| 9 | 15-Mar-2026 | Asha | North | Bag | 3 | 2250 |
| 10 | 22-Mar-2026 | Kabir | South | Pen | 60 | 600 |
| 11 | 01-Apr-2026 | Ravi | East | Notebook | 25 | 1500 |
| 12 | 18-Apr-2026 | Meena | North | Pen | 80 | 800 |
| 13 | 06-May-2026 | Asha | South | Bag | 5 | 3750 |
The data is A1:F13. In a query, columns are named by their letters: A is the date, B the salesperson, C the region, D the product, E the units and F the amount. The final argument, 1, tells QUERY that the first row is a heading.
The order of the clauses
Clauses must be written in this order. Leave out any you do not need.
select · where · group by · pivot · order by · limit · offset · label · format
select: which columns
=QUERY(A1:F13,"select B, F limit 3",1)Result:Salesperson Amount Asha 1200 Ravi 500 Meena 3000 Two columns, in the order asked for.
select * returns all columns and is the same as leaving select out. A column can also be calculated, as in select B, F*2. Sheets then writes a heading of its own for it, which you will want to replace with label.
where: which rows
=QUERY(A1:F13,"select B, F where F >= 2400",1)Result:Salesperson Amount Meena 3000 Meena 2400 Asha 3750 A number condition. Rows stay in their original order.
=QUERY(A1:F13,"select B, D where C = 'East'",1)Result:Salesperson Product Asha Pen Meena Notebook Ravi Notebook Text in single quotation marks.
=QUERY(A1:F13,"select B, F where D contains 'book' and F > 1000",1)Result:Salesperson Amount Asha 1200 Meena 2400 Ravi 1500 contains, and two conditions joined with and.
=QUERY(A1:F13,"select B, A where A >= date '2026-04-01'",1)Result:Salesperson Date Ravi 01-Apr-2026 Meena 18-Apr-2026 Asha 06-May-2026 A date is written as the word date and year-month-day in single quotation marks.
Comparison words for text: contains, starts with, ends with, matches (a regular expression for the whole value) and like (with % for any run of characters). Use is null and is not null for empty cells, and not to reverse a condition. Text comparisons are case sensitive. Wrap the column in lower() to ignore case: where lower(C) = 'east'.
group by: totals for each value
=QUERY(A1:F13,"select C, sum(F), count(F) group by C",1)Result:Region sum Amount count Amount East 4200 3 North 8750 5 South 5750 4 One row per region, with a total and a count.
=QUERY(A1:F13,"select B, max(F), min(F) group by B",1)Result:Salesperson max Amount min Amount Asha 3750 300 Kabir 900 600 Meena 3000 800 Ravi 1500 500 Largest and smallest sale of each person.
The totalling functions are sum, count, avg, max and min. Every column in the select must be either in the group by or inside one of them.
pivot: values across the top
pivot turns the values of a column into column headings. This makes a cross table.
=QUERY(A1:F13,"select C, sum(F) group by C pivot D",1)Result:Region Bag Notebook Pen East 3900 300 North 6750 1200 800 South 3750 900 1100 Regions down the side, products across the top, totals in the grid. East sold no bags, so that cell is empty.
order by, limit and offset
=QUERY(A1:F13,"select B, F order by F desc limit 3",1)Result:Salesperson Amount Asha 3750 Meena 3000 Meena 2400 Largest first, top three.
=QUERY(A1:F13,"select B, F order by F desc limit 2 offset 3",1)Result:Salesperson Amount Asha 2250 Ravi 1500 Skip the first three, then take two.
=QUERY(A1:F13,"select C, sum(F) group by C order by sum(F) desc",1)Result:Region sum Amount North 8750 South 5750 East 4200 Sorting groups by their total.
asc is the default. List several columns for several sort levels: order by C, F desc.
label and format
=QUERY(A1:F13,"select C, sum(F) group by C label C 'Area', sum(F) 'Total sales'",1)Result:Area Total sales East 4200 North 8750 South 5750 label renames headings.
=QUERY(A1:F13,"select C, sum(F) group by C label sum(F) ''",1)Result:Region East 4200 North 8750 South 5750 An empty label removes a heading.
format sets how a column is shown: format F '#,##0.00', A 'dd-mmm-yyyy'. To drop the heading row from a result altogether, set every label to empty or wrap the query in QUERY(..., 0) over data without headings.
Dates: year, month and friends
Scalar functions work on date columns: year(A), month(A), day(A), dayOfWeek(A), quarter(A). One trap: month() counts from 0, so January is 0 and December is 11. Add 1 to get the usual number: select month(A)+1, sum(F) group by month(A)+1.
Using cell values in a query
The query is text, so join cell values into it with &.
- Text needs single quotation marks around it:
"select B, F where C = '"&H1&"'" - A number does not:
"select B, F where F > "&H2 - A date must be formatted:
"where A >= date '"&TEXT(H3,"yyyy-mm-dd")&"'"
Build long queries in a spare cell first, as ordinary text, and look at the result. A missing space or quotation mark is easy to see there.
Data without column letters
When the data comes from IMPORTRANGE, from curly brackets, or from another function, it has no letters. Name the columns Col1, Col2, Col3, counting from the left: =QUERY(IMPORTRANGE(url,"Sales!A:F"), "select Col2, sum(Col6) group by Col2", 1).
Curly brackets stack ranges for QUERY: {Jan!A2:F; Feb!A2:F} puts two tabs one above the other.
Things that go wrong
| Message or symptom | Likely cause |
|---|---|
#VALUE!, unable to parse query string | a spelling slip, a clause out of order, or double quotation marks inside the query |
#VALUE!, no column | a letter outside the data range, or letters used where Col1 is needed |
| values missing from a column | the column mixes numbers and text; the minority type is treated as empty |
| headings joined into one row of text | the headers argument was left out and guessed wrongly; give it as 1 |
| nothing returned | a text condition with the wrong case, or stray spaces in the data |
The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.
This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.