Spreadsheet Practice

The QUERY function in Google Sheets

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])
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.

Syntax

ArgumentRequired?What it is
dataRequiredThe 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.
queryRequiredThe 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.
headersOptionalOptional. 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 our formula engine in Sheets mode.

ABCDEFGHI
1DateRepRegionProductUnitsPriceRevenueRegion to show
27/2/2026Ana RuizEastWidget40$52$2,080West
37/5/2026Raj PatelWestGadget25$89$2,225Minimum revenue
47/9/2026Lee ChenEastGizmo12$145$1,740$2,000
57/12/2026Ana RuizNorthGadget30$89$2,670
67/15/2026Kim OrtizWestWidget55$52$2,860
77/19/2026Raj PatelEastWidget18$52$936
87/22/2026Lee ChenSouthGadget22$89$1,958
97/26/2026Kim OrtizEastGizmo9$145$1,305
107/28/2026Ana RuizWestWidget35$52$1,820
118/3/2026Mo DiazNorthGizmo14$145$2,030
128/6/2026Raj PatelSouthWidget Pro20$79$1,580
138/11/2026Kim OrtizNorthGadget16$89$1,424
148/14/2026Mo DiazEastGizmo Mini28$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)

RepProductRevenue
Ana RuizWidget$2,080
Raj PatelGadget$2,225
Lee ChenGizmo$1,740
Ana RuizGadget$2,670
Kim OrtizWidget$2,860
Raj PatelWidget$936
+ 7 more rows
Rep, product and revenue for every order, with the header row on top

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

ProductRepUnits
WidgetAna Ruiz40
GadgetRaj Patel25
GizmoLee Chen12
GadgetAna Ruiz30
WidgetKim Ortiz55
WidgetRaj Patel18
+ 7 more rows
Columns come back in the order you list them, not the order in the sheet

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)

RepProductRevenue
Ana RuizWidget$2,080
Raj PatelGadget$2,225
Ana RuizGadget$2,670
Kim OrtizWidget$2,860
Mo DiazGizmo$2,030
Orders over $2,000

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

RepRegionRevenue
Ana RuizEast$2,080
Lee ChenEast$1,740
East orders of at least $1,500: both tests must pass

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

RepRegionProduct
Lee ChenEastGizmo
Ana RuizNorthGadget
Kim OrtizEastGizmo
Mo DiazNorthGizmo
Kim OrtizNorthGadget
Rows from the North region, plus any Gizmo order from elsewhere

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)

RepProductRevenue
Ana RuizWidget$2,080
Kim OrtizWidget$2,860
Raj PatelWidget$936
Ana RuizWidget$1,820
Raj PatelWidget Pro$1,580
Widget and Widget Pro: contains matches any part of the text

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

RepProduct
Raj PatelGadget
Raj PatelWidget
Mo DiazGizmo
Raj PatelWidget Pro
Mo DiazGizmo Mini
Reps whose name starts with R, plus reps whose name ends in Diaz

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

RepProduct
Raj PatelGadget
Lee ChenGizmo
Ana RuizGadget
Lee ChenGadget
Kim OrtizGizmo
Mo DiazGizmo
Kim OrtizGadget
matches needs the whole value to fit, so Gizmo Mini is left out

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)

RepProductRevenue
Mo DiazGizmo$2,030
Raj PatelWidget Pro$1,580
Kim OrtizGadget$1,424
Mo DiazGizmo Mini$1,092
Orders on or after August 1

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

RepRevenue
Ana Ruiz$2,670
Kim Ortiz$2,860
Raj Patel$936
Orders from July 10 up to, but not including, July 20

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)

RepProductRevenue
Kim OrtizWidget$2,860
Ana RuizGadget$2,670
Raj PatelGadget$2,225
Ana RuizWidget$2,080
Mo DiazGizmo$2,030
Lee ChenGadget$1,958
+ 7 more rows
Every order, largest revenue first

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

RegionRepRevenue
EastAna Ruiz$2,080
EastLee Chen$1,740
EastKim Ortiz$1,305
EastMo Diaz$1,092
EastRaj Patel$936
NorthAna Ruiz$2,670
+ 7 more rows
Regions A to Z, and the biggest order first inside each region

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)

RepProductRevenue
Kim OrtizWidget$2,860
Ana RuizGadget$2,670
Raj PatelGadget$2,225
The three biggest orders

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

RepProductRevenue
Ana RuizWidget$2,080
Mo DiazGizmo$2,030
Lee ChenGadget$1,958
The next three: offset skips the first three rows

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)

Regionsum Revenue
East$7,153
North$6,124
South$3,538
West$6,905
Total revenue for each region, regions in A to Z order

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

Repcount Productavg Unitsmax Revenue
Ana Ruiz335$2,670
Kim Ortiz326.66666667$2,860
Lee Chen217$1,958
Mo Diaz221$2,030
Raj Patel321$2,225
Per rep: number of orders, average units and largest order

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

Regionsum Revenue
East$7,153
West$6,905
North$6,124
South$3,538
Order by a total to rank the regions

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)

RegionRevenue
East$7,153
North$6,124
South$3,538
West$6,905
The total column is headed Revenue instead of sum Revenue

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

RegionUnits sold
East107
North60
South42
West115
Two labels, separated by a comma

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

RepUnits x price
Ana Ruiz2080
Kim Ortiz2860
Ana Ruiz1820
A calculated column, with a label to name it

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)

RegionRevenueOrders
West$6,9053
East$4,1083
North$4,0942
Top three regions by revenue, leaving out Gizmo orders, with tidy headers

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)

RepProductRevenue
Raj PatelGadget$2,225
Kim OrtizWidget$2,860
Ana RuizWidget$1,820
Orders for the region typed in I2 (West)

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

RepRegionRevenue
Kim OrtizWest$2,860
Ana RuizNorth$2,670
Raj PatelWest$2,225
Ana RuizEast$2,080
Mo DiazNorth$2,030
Orders above the amount in I4, largest first

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)

RepRevenue
Ana Ruiz6570
Kim Ortiz5589
Lee Chen3698
Mo Diaz3122
Raj Patel4741
Revenue per rep from two columns joined side by side

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

Regionsum Units
East107
North60
South42
West115
Open-ended ranges, with the empty rows filtered out

Try QUERY live

The sample sheet with a formula in K1. Click it and change the arguments to see what happens.

Practice QUERY

3 graded exercises. Each answer is checked in Sheets mode, then re-checked on changed data.

1. Long support tickets

In F1, use QUERY to list the Customer and Hours of every ticket that took more than 4 hours. The data has one header row.

2. East region, best first

In G1, use QUERY to list the Product and Revenue of every East order, highest revenue first.

3. Revenue by region

In G1, use QUERY to total Revenue for each region, largest total first, with the total column headed Revenue.

Two full QUERY lessons, with walkthroughs →

QUERY cheat sheet

ClauseExampleWhat it does
selectselect B, D, GReturn these columns, in this order
select *select *Return every column
wherewhere G > 2000Keep rows that pass a test
and, or, notwhere C = 'East' and G >= 1500Combine tests
contains, starts with, ends withwhere D contains 'Widget'Text tests (case-sensitive)
likewhere B like 'R%'% is any run of characters, _ is one character
matcheswhere D matches 'Gizmo|Gadget'A regular expression that must match the whole value
is null, is not nullwhere Col1 is not nullFind or skip empty cells
datewhere A >= date '2026-08-01'Compare with a date, written yyyy-mm-dd
group byselect C, sum(G) group by COne row per value, with an aggregate for the rest
sum, avg, count, min, maxselect B, count(D), max(G) group by BSummarize each group
order byorder by G descSort the result; asc is the default
limit, offsetlimit 3 offset 3Return at most 3 rows, after skipping 3
labellabel sum(G) 'Revenue'Rename a column header

Common QUERY errors

What you seeWhy, and how to fix it
#VALUE! and a message that the query could not be parsedUsually 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 nothingText comparisons are case-sensitive: 'east' does not match East. Match the case exactly, or compare lower(C) = 'east'.
Some values come back blankQUERY 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 existText in the query needs single quotes. Without them, where C = East reads East as a column name. Write where C = 'East'.
Column letters stop workingWith 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 headerWhen 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 nothingInside 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.

Updated Checked by running: every formula on this page was run by our formula engine on .

Google Sheets is a trademark of Google LLC; this guide is independent.