Google SheetsQUERY function in Google Sheets
QUERY selects, filters, sorts and summarises a range using a short query written in words, much like SQL.
QUERY is the most powerful function in Google Sheets, and Excel has nothing quite like it. One formula can pick columns, keep only some rows, sort them, and work out totals for each group. It replaces a combination of FILTER, SORT, SUMIF and a pivot table.
Syntax
=QUERY(data, query, [headers])| Argument | What it is |
|---|---|
data | The range to work on, such as A1:F13. Include the heading row. |
query | The instruction, as text in double quotation marks. Columns are named by their letters. |
headers | How many rows at the top are headings. Usually 1. If left out, Sheets guesses. |
Examples
| 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 |
=QUERY(A1:F13,"select B, F where F > 2000 order by F desc",1)Result:Salesperson Amount Asha 3750 Meena 3000 Meena 2400 Asha 2250 Two columns, only the rows with an amount above 2000, largest first. The headings come along.
=QUERY(A1:F13,"select B, sum(F) group by B",1)Result:Salesperson sum Amount Asha 7500 Kabir 1500 Meena 6200 Ravi 3500 A total for each salesperson. Groups come out in alphabetical order.
=QUERY(A1:F13,"select C, sum(F) group by C order by sum(F) desc label sum(F) 'Total'",1)Result:Region Total North 8750 South 5750 East 4200 Totals by region, largest first, with the heading renamed by label.
=QUERY(A1:F13,"select D, count(A) group by D",1)Result:Product count Date Bag 4 Notebook 4 Pen 4 How many sales of each product.
=QUERY(A1:F13,"select B, D, F where C = 'North' and D = 'Bag'",1)Result:Salesperson Product Amount Meena Bag 3000 Ravi Bag 1500 Asha Bag 2250 Two conditions joined with and. Text inside the query goes in single quotation marks.
=QUERY(A1:F13,"select B, F order by F desc limit 3",1)Result:Salesperson Amount Asha 3750 Meena 3000 Meena 2400 The three largest sales.
How it works
You hand QUERY a range and an instruction written as text. The instruction uses a small language of its own, the Google Visualization API Query Language, which reads like SQL. Columns are called by their letters: A, B, C.
The result spills into the cells below and to the right of the formula, with the headings on top.
The parts of a query
They must appear in this order. All are optional except that you need at least one.
| Clause | Does | Example |
|---|---|---|
select | which columns to return | select B, F |
where | which rows to keep | where F > 2000 |
group by | one row per value, with totals | group by B |
order by | sorting | order by F desc |
limit | only the first rows | limit 3 |
label | rename a heading | label sum(F) 'Total' |
select * returns every column.
Conditions in where
- Numbers are written plainly:
where F > 2000. - Text goes in single quotation marks, because the whole query is already inside double ones:
where C = 'North'. - Join conditions with
andandor. - For part of a text, use
contains,starts withorends with:where D contains 'book'. - Text comparisons are case sensitive.
'north'does not match North. - Empty cells:
where B is not null.
Totals: sum, count, avg, max, min
With group by, every column in the select must either be grouped or sit inside one of the totalling functions. select B, sum(F) group by B gives one row per salesperson. The heading of a total column reads like "sum Amount". Use label to give it a better name.
Using a value from a cell
The query is text, so a cell's value has to be joined into it. With a region typed in H1:
=QUERY(A1:F13, "select B, F where C = '"&H1&"'", 1)
Note the single quotation marks either side of the join. For a number, leave them out: "where F > "&H1.
Dates
A date in a query must be written as date '2026-03-01': the word date, then year-month-day in single quotation marks. To use a date from a cell, format it with TEXT: "where A >= date '"&TEXT(H1,"yyyy-mm-dd")&"'".
Each column must hold one kind of data
QUERY decides the type of each column from most of its values, and treats the minority as empty. A column that is mostly numbers with a few text entries loses the text entries. This is the usual reason for values going missing from a QUERY result. Keep each column to one type.
Common mistakes
- Double quotation marks inside the query. Use single ones.
- Column names instead of letters.
select Amountdoes not work. - Clauses in the wrong order, such as
order bybeforewhere. #VALUE!with a message about parsing: a spelling slip in the query text.- A mixed-type column silently dropping values.
In Excel
Excel has no QUERY function. The nearest tools there are FILTER and SORT for rows, PivotTables for totals, and Power Query for bigger jobs. A sheet that uses QUERY shows only its last values after being saved as an Excel file.
Related functions
- FILTEROnly in Sheets
- SORT and SORTNOnly in Sheets
- UNIQUE and COUNTUNIQUEOnly in Sheets
- SUMIF and SUMIFSMath and counting
- ARRAYFORMULAOnly in Sheets
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.