# The QUERY function in Google Sheets

> QUERY runs a question written in Google's query language against a range and returns a new table.

Source: https://spreadsheetpractice.com/google-sheets/query · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** QUERY runs a question written in Google's query language against a range and returns a new table.
- **Syntax:** `=QUERY(data, query, [headers])`
- **Returns:** A table that spills down and to the right from the formula cell, with the header row on top when data has one.
- **In Excel:** No QUERY function. FILTER, SORT, GROUPBY or a PivotTable do parts of the job.

QUERY runs a question written in Google's query language against a range and returns a new table. One formula can pick columns, filter rows, group and total them, sort the result and rename the headers. It is the closest thing Google Sheets has to SQL.

## Syntax

`=QUERY(data, query, [headers])`

| Argument | Required? | What it is |
|---|---|---|
| `data` | Required | The range or array to query, such as A1:G14 or {B1:B,G1:G}. With a range, refer to columns by their letter. With an array literal, use Col1, Col2 and so on. |
| `query` | Required | The query as text in double quotes. Clauses go in a fixed order: select, where, group by, pivot, order by, limit, offset, label. Text values inside it go in single quotes. |
| `headers` | Optional | Optional. How many header rows data has. Leave it out and QUERY guesses; set it to 1 or 0 so it never guesses wrong. |

## The sample data

Every example below runs on this sheet; results are calculated by the formula engine in Google Sheets mode.

|  | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Rep | Region | Product | Units | Price | Revenue |  | Region to show |
| 2 | 7/2/2026 | Ana Ruiz | East | Widget | 40 | $52 | $2,080 |  | West |
| 3 | 7/5/2026 | Raj Patel | West | Gadget | 25 | $89 | $2,225 |  | Minimum revenue |
| 4 | 7/9/2026 | Lee Chen | East | Gizmo | 12 | $145 | $1,740 |  | $2,000 |
| 5 | 7/12/2026 | Ana Ruiz | North | Gadget | 30 | $89 | $2,670 |  |  |
| 6 | 7/15/2026 | Kim Ortiz | West | Widget | 55 | $52 | $2,860 |  |  |
| 7 | 7/19/2026 | Raj Patel | East | Widget | 18 | $52 | $936 |  |  |
| 8 | 7/22/2026 | Lee Chen | South | Gadget | 22 | $89 | $1,958 |  |  |
| 9 | 7/26/2026 | Kim Ortiz | East | Gizmo | 9 | $145 | $1,305 |  |  |
| 10 | 7/28/2026 | Ana Ruiz | West | Widget | 35 | $52 | $1,820 |  |  |
| 11 | 8/3/2026 | Mo Diaz | North | Gizmo | 14 | $145 | $2,030 |  |  |
| 12 | 8/6/2026 | Raj Patel | South | Widget Pro | 20 | $79 | $1,580 |  |  |
| 13 | 8/11/2026 | Kim Ortiz | North | Gadget | 16 | $89 | $1,424 |  |  |
| 14 | 8/14/2026 | Mo Diaz | East | Gizmo Mini | 28 | $39 | $1,092 |  |  |

## select: choose the columns

`select` lists the columns you want, in the order you want them. Refer to columns by their letter in the sheet: with data in `A1:G14`, `B` is the rep and `G` is revenue.

`select *` returns every column, and so does leaving select out altogether.

`=QUERY(A1:G14,"select B, D, G",1)`

Rep, product and revenue for every order, with the header row on top

Result:

| Rep | Product | Revenue |
|---|---|---|
| Ana Ruiz | Widget | $2,080 |
| Raj Patel | Gadget | $2,225 |
| Lee Chen | Gizmo | $1,740 |
| Ana Ruiz | Gadget | $2,670 |
| Kim Ortiz | Widget | $2,860 |
| Raj Patel | Widget | $936 |
| Lee Chen | Gadget | $1,958 |
| Kim Ortiz | Gizmo | $1,305 |
| Ana Ruiz | Widget | $1,820 |
| Mo Diaz | Gizmo | $2,030 |
| Raj Patel | Widget Pro | $1,580 |
| Kim Ortiz | Gadget | $1,424 |
| Mo Diaz | Gizmo Mini | $1,092 |

`=QUERY(A1:G14,"select D, B, E",1)`

Columns come back in the order you list them, not the order in the sheet

Result:

| Product | Rep | Units |
|---|---|---|
| Widget | Ana Ruiz | 40 |
| Gadget | Raj Patel | 25 |
| Gizmo | Lee Chen | 12 |
| Gadget | Ana Ruiz | 30 |
| Widget | Kim Ortiz | 55 |
| Widget | Raj Patel | 18 |
| Gadget | Lee Chen | 22 |
| Gizmo | Kim Ortiz | 9 |
| Widget | Ana Ruiz | 35 |
| Gizmo | Mo Diaz | 14 |
| Widget Pro | Raj Patel | 20 |
| Gadget | Kim Ortiz | 16 |
| Gizmo Mini | Mo Diaz | 28 |

## where: keep the rows that pass a test

`where` keeps the rows that pass a test. Compare numbers with `=`, `!=`, `<`, `>`, `<=` and `>=`. Put text in single quotes: `where C = 'East'`.

Join tests with `and` and `or`, and flip one with `not`. When you mix and with or, add parentheses so the query reads the way you mean it.

`=QUERY(A1:G14,"select B, D, G where G > 2000",1)`

Orders over $2,000

Result:

| Rep | Product | Revenue |
|---|---|---|
| Ana Ruiz | Widget | $2,080 |
| Raj Patel | Gadget | $2,225 |
| Ana Ruiz | Gadget | $2,670 |
| Kim Ortiz | Widget | $2,860 |
| Mo Diaz | Gizmo | $2,030 |

`=QUERY(A1:G14,"select B, C, G where C = 'East' and G >= 1500",1)`

East orders of at least $1,500: both tests must pass

Result:

| Rep | Region | Revenue |
|---|---|---|
| Ana Ruiz | East | $2,080 |
| Lee Chen | East | $1,740 |

`=QUERY(A1:G14,"select B, C, D where C = 'North' or D = 'Gizmo'",1)`

Rows from the North region, plus any Gizmo order from elsewhere

Result:

| Column 1 | Column 2 | Column 3 |
|---|---|---|
| Rep | Region | Product |
| Lee Chen | East | Gizmo |
| Ana Ruiz | North | Gadget |
| Kim Ortiz | East | Gizmo |
| Mo Diaz | North | Gizmo |
| Kim Ortiz | North | Gadget |

## Text tests: contains, starts with, like and matches

Beyond `=`, QUERY has text tests: `contains`, `starts with`, `ends with`, `like` and `matches`. All of them are case-sensitive, so `'widget'` does not match Widget.

`like` takes two wildcards: `%` for any run of characters and `_` for exactly one. `matches` takes a regular expression that has to match the whole value, not just part of it.

`=QUERY(A1:G14,"select B, D, G where D contains 'Widget'",1)`

Widget and Widget Pro: contains matches any part of the text

Result:

| Rep | Product | Revenue |
|---|---|---|
| Ana Ruiz | Widget | $2,080 |
| Kim Ortiz | Widget | $2,860 |
| Raj Patel | Widget | $936 |
| Ana Ruiz | Widget | $1,820 |
| Raj Patel | Widget Pro | $1,580 |

`=QUERY(A1:G14,"select B, D where B like 'R%' or B ends with 'Diaz'",1)`

Reps whose name starts with R, plus reps whose name ends in Diaz

Result:

| Column 1 | Column 2 |
|---|---|
| Rep | Product |
| Raj Patel | Gadget |
| Raj Patel | Widget |
| Mo Diaz | Gizmo |
| Raj Patel | Widget Pro |
| Mo Diaz | Gizmo Mini |

`=QUERY(A1:G14,"select B, D where D matches 'Gizmo|Gadget'",1)`

matches needs the whole value to fit, so Gizmo Mini is left out

Result:

| Column 1 | Column 2 |
|---|---|
| Rep | Product |
| Raj Patel | Gadget |
| Lee Chen | Gizmo |
| Ana Ruiz | Gadget |
| Lee Chen | Gadget |
| Kim Ortiz | Gizmo |
| Mo Diaz | Gizmo |
| Kim Ortiz | Gadget |

## Filter by date

To compare a date column, write the date as a date literal: the word `date`, then the date in single quotes in yyyy-mm-dd form. `where A >= date '2026-08-01'` keeps August onward.

Two tests make a range. Compare with `<` against the first day of the next period, so you never have to work out the last day of a month. QUERY also has `year()` and `month()`; note that `month()` counts from 0, so July is 6.

`=QUERY(A1:G14,"select B, D, G where A >= date '2026-08-01'",1)`

Orders on or after August 1

Result:

| Rep | Product | Revenue |
|---|---|---|
| Mo Diaz | Gizmo | $2,030 |
| Raj Patel | Widget Pro | $1,580 |
| Kim Ortiz | Gadget | $1,424 |
| Mo Diaz | Gizmo Mini | $1,092 |

`=QUERY(A1:G14,"select B, G where A >= date '2026-07-10' and A < date '2026-07-20'",1)`

Orders from July 10 up to, but not including, July 20

Result:

| Rep | Revenue |
|---|---|
| Ana Ruiz | $2,670 |
| Kim Ortiz | $2,860 |
| Raj Patel | $936 |

## order by: sort the result

`order by` sorts the result. Add `desc` for largest first; `asc` is the default. List more than one column to break ties: `order by C, G desc` sorts by region, then by revenue within each region.

`=QUERY(A1:G14,"select B, D, G order by G desc",1)`

Every order, largest revenue first

Result:

| Rep | Product | Revenue |
|---|---|---|
| Kim Ortiz | Widget | $2,860 |
| Ana Ruiz | Gadget | $2,670 |
| Raj Patel | Gadget | $2,225 |
| Ana Ruiz | Widget | $2,080 |
| Mo Diaz | Gizmo | $2,030 |
| Lee Chen | Gadget | $1,958 |
| Ana Ruiz | Widget | $1,820 |
| Lee Chen | Gizmo | $1,740 |
| Raj Patel | Widget Pro | $1,580 |
| Kim Ortiz | Gadget | $1,424 |
| Kim Ortiz | Gizmo | $1,305 |
| Mo Diaz | Gizmo Mini | $1,092 |
| Raj Patel | Widget | $936 |

`=QUERY(A1:G14,"select C, B, G order by C, G desc",1)`

Regions A to Z, and the biggest order first inside each region

Result:

| Region | Rep | Revenue |
|---|---|---|
| East | Ana Ruiz | $2,080 |
| East | Lee Chen | $1,740 |
| East | Kim Ortiz | $1,305 |
| East | Mo Diaz | $1,092 |
| East | Raj Patel | $936 |
| North | Ana Ruiz | $2,670 |
| North | Mo Diaz | $2,030 |
| North | Kim Ortiz | $1,424 |
| South | Lee Chen | $1,958 |
| South | Raj Patel | $1,580 |
| West | Kim Ortiz | $2,860 |
| West | Raj Patel | $2,225 |
| West | Ana Ruiz | $1,820 |

## limit and offset: top N lists

`limit` caps how many rows come back, which together with order by gives you a top-N list. `offset` skips rows first, so `limit 3 offset 3` returns rows 4 to 6 of the sorted result.

`=QUERY(A1:G14,"select B, D, G order by G desc limit 3",1)`

The three biggest orders

Result:

| Rep | Product | Revenue |
|---|---|---|
| Kim Ortiz | Widget | $2,860 |
| Ana Ruiz | Gadget | $2,670 |
| Raj Patel | Gadget | $2,225 |

`=QUERY(A1:G14,"select B, D, G order by G desc limit 3 offset 3",1)`

The next three: offset skips the first three rows

Result:

| Rep | Product | Revenue |
|---|---|---|
| Ana Ruiz | Widget | $2,080 |
| Mo Diaz | Gizmo | $2,030 |
| Lee Chen | Gadget | $1,958 |

## group by: totals per category

`group by` collapses rows that share a value into one row. Every other column in select then needs an aggregate: `sum`, `avg`, `count`, `min` or `max`. Select a column that is neither grouped nor aggregated and QUERY returns an error.

Groups come back sorted by the grouped column. Add order by to sort them by a total instead.

`=QUERY(A1:G14,"select C, sum(G) group by C",1)`

Total revenue for each region, regions in A to Z order

Result:

| Region | sum Revenue |
|---|---|
| East | $7,153 |
| North | $6,124 |
| South | $3,538 |
| West | $6,905 |

`=QUERY(A1:G14,"select B, count(D), avg(E), max(G) group by B",1)`

Per rep: number of orders, average units and largest order

Result:

| Rep | count Product | avg Units | max Revenue |
|---|---|---|---|
| Ana Ruiz | 3 | 35 | $2,670 |
| Kim Ortiz | 3 | 26.66666667 | $2,860 |
| Lee Chen | 2 | 17 | $1,958 |
| Mo Diaz | 2 | 21 | $2,030 |
| Raj Patel | 3 | 21 | $2,225 |

`=QUERY(A1:G14,"select C, sum(G) group by C order by sum(G) desc",1)`

Order by a total to rank the regions

Result:

| Region | sum Revenue |
|---|---|
| East | $7,153 |
| West | $6,905 |
| North | $6,124 |
| South | $3,538 |

## label: rename the headers

`label` renames column headers in the result. A total gets a header like "sum Revenue" by default; `label sum(G) 'Revenue'` replaces it. Separate several labels with commas, and use an empty label, `''`, for no header text.

label comes last, after order by and limit.

`=QUERY(A1:G14,"select C, sum(G) group by C label sum(G) 'Revenue'",1)`

The total column is headed Revenue instead of sum Revenue

Result:

| Region | Revenue |
|---|---|
| East | $7,153 |
| North | $6,124 |
| South | $3,538 |
| West | $6,905 |

`=QUERY(A1:G14,"select C, sum(E) group by C label C 'Region', sum(E) 'Units sold'",1)`

Two labels, separated by a comma

Result:

| Region | Units sold |
|---|---|
| East | 107 |
| North | 60 |
| South | 42 |
| West | 115 |

`=QUERY(A1:G14,"select B, E*F where E > 30 label E*F 'Units x price'",1)`

A calculated column, with a label to name it

Result:

| Rep | Units x price |
|---|---|
| Ana Ruiz | 2080 |
| Kim Ortiz | 2860 |
| Ana Ruiz | 1820 |

## Putting the clauses together

The clauses work together as long as they appear in this order: select, where, group by, pivot, order by, limit, offset, label. where runs before grouping, so it decides which rows go into each total.

`=QUERY(A1:G14,"select C, sum(G), count(G) where D != 'Gizmo' group by C order by sum(G) desc limit 3 label sum(G) 'Revenue', count(G) 'Orders'",1)`

Top three regions by revenue, leaving out Gizmo orders, with tidy headers

Result:

| Region | Revenue | Orders |
|---|---|---|
| West | $6,905 | 3 |
| East | $4,108 | 3 |
| North | $4,094 | 2 |

## Use a cell inside the query

The query is just text, so you can build it from cells. Close the double quotes, join the cell with `&`, and open them again: `"where C = '"&I2&"'"`. The single quotes stay in the text because the value is text.

Numbers need no single quotes: `"where G > "&I4`. Change I2 or I4 and the result updates.

`=QUERY(A1:G14,"select B, D, G where C = '"&I2&"'",1)`

Orders for the region typed in I2 (West)

Result:

| Rep | Product | Revenue |
|---|---|---|
| Raj Patel | Gadget | $2,225 |
| Kim Ortiz | Widget | $2,860 |
| Ana Ruiz | Widget | $1,820 |

`=QUERY(A1:G14,"select B, C, G where G > "&I4&" order by G desc",1)`

Orders above the amount in I4, largest first

Result:

| Rep | Region | Revenue |
|---|---|---|
| Kim Ortiz | West | $2,860 |
| Ana Ruiz | North | $2,670 |
| Raj Patel | West | $2,225 |
| Ana Ruiz | East | $2,080 |
| Mo Diaz | North | $2,030 |

## Col1, Col2: querying an array

When data is an array literal such as `{B1:B14,G1:G14}`, or the result of another function, column letters no longer apply. Refer to columns by position instead: `Col1`, `Col2` and so on.

Open-ended ranges like `C1:C` include the empty rows below your data, so add `where Col1 is not null` to drop them.

`=QUERY({B1:B14,G1:G14},"select Col1, sum(Col2) group by Col1 label sum(Col2) 'Revenue'",1)`

Revenue per rep from two columns joined side by side

Result:

| Rep | Revenue |
|---|---|
| Ana Ruiz | 6570 |
| Kim Ortiz | 5589 |
| Lee Chen | 3698 |
| Mo Diaz | 3122 |
| Raj Patel | 4741 |

`=QUERY({C1:C,E1:E},"select Col1, sum(Col2) where Col1 is not null group by Col1",1)`

Open-ended ranges, with the empty rows filtered out

Result:

| Region | sum Units |
|---|---|
| East | 107 |
| North | 60 |
| South | 42 |
| West | 115 |

## QUERY cheat sheet

| Clause | Example | What it does |
|---|---|---|
| select | `select B, D, G` | Return these columns, in this order |
| select * | `select *` | Return every column |
| where | `where G > 2000` | Keep rows that pass a test |
| and, or, not | `where C = 'East' and G >= 1500` | Combine tests |
| contains, starts with, ends with | `where D contains 'Widget'` | Text tests (case-sensitive) |
| like | `where B like 'R%'` | % is any run of characters, _ is one character |
| matches | `where D matches 'Gizmo\|Gadget'` | A regular expression that must match the whole value |
| is null, is not null | `where Col1 is not null` | Find or skip empty cells |
| date | `where A >= date '2026-08-01'` | Compare with a date, written yyyy-mm-dd |
| group by | `select C, sum(G) group by C` | One row per value, with an aggregate for the rest |
| sum, avg, count, min, max | `select B, count(D), max(G) group by B` | Summarize each group |
| order by | `order by G desc` | Sort the result; asc is the default |
| limit, offset | `limit 3 offset 3` | Return at most 3 rows, after skipping 3 |
| label | `label sum(G) 'Revenue'` | Rename a column header |

## Common QUERY errors

| What you see | Why, and how to fix it |
|---|---|
| #VALUE! and a message that the query could not be parsed | Usually the clauses are out of order or there is a typo. Google expects select, where, group by, pivot, order by, limit, offset, label. Put order by after group by, and label last. |
| A text test finds nothing | Text comparisons are case-sensitive: 'east' does not match East. Match the case exactly, or compare lower(C) = 'east'. |
| Some values come back blank | QUERY gives each column one type: the type most of its cells hold. Numbers stored as text in a mostly numeric column, or the other way round, are treated as empty. Make every value in the column the same type. |
| An error saying a column does not exist | Text in the query needs single quotes. Without them, where C = East reads East as a column name. Write where C = 'East'. |
| Column letters stop working | With an array literal such as {B1:B14,G1:G14}, or data from another function, use Col1, Col2 and so on instead of letters. |
| The header row shows up as data, or the first order becomes the header | When headers is left out, QUERY guesses how many header rows there are. Set it yourself: 1 for one header row, 0 for none. |
| A cell reference inside the query does nothing | Inside the quotes, I2 is just two characters. Close the quotes and join the cell: "where C = '"&I2&"'". Numbers need no single quotes: "where G > "&I4. |

## Questions

### Does Excel have a QUERY function?

No. In Excel you get a similar result by combining FILTER, SORT and UNIQUE with SUMIFS, or with the GROUPBY function in Microsoft 365. Power Query, a separate data tool in Excel, also covers this ground, but it is not a formula.

### Can QUERY pull data from another spreadsheet?

Yes, by wrapping IMPORTRANGE: =QUERY(IMPORTRANGE("spreadsheet URL","Sheet1!A1:G"),"select Col1, Col7 where Col7 > 2000",1). Because IMPORTRANGE returns an array, refer to columns as Col1, Col2 and so on.

### How do I filter by a date typed in a cell?

Turn the date into yyyy-mm-dd text and join it in: "where A >= date '"&TEXT(I5,"yyyy-mm-dd")&"'". The date keyword and the single quotes stay inside the query text.

### Does QUERY update when the data changes?

Yes. It recalculates like any other formula. Use an open-ended range such as A1:G, plus where A is not null, and new rows are picked up as they are added.

### What does pivot do?

pivot turns the values of one column into column headers, like a pivot table. For example, group by region and pivot by product to get one revenue column per product. It comes after group by and before order by.

### Why does QUERY return #N/A or only a header row?

No row passed the where test. Check the case of any text you compare, and that dates are written as date 'yyyy-mm-dd'. Wrap the formula in IFERROR to show a message instead.

## Practice

3 graded QUERY exercises are on [the QUERY guide](https://spreadsheetpractice.com/google-sheets/query), checked in Google Sheets mode and re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
