Someone asked a human this. So we asked the AI.
How to Use the QUERY Function in Google Sheets
Someone wanted a spreadsheet to answer questions in plain-ish English, and yes, there is a function for that.
The answer
QUERY runs a short SQL-like command on a range and returns only the rows and columns you ask for. The syntax is =QUERY(data, "query", headers).
data: the range, such asA1:C100.query: the command, in double quotes.headers: how many header rows the data has. Use1for one header row. Set it explicitly, because the automatic guess can get it wrong.
Clauses and their order
Clauses must appear in this order, and you can leave out any you don't need: select, where, group by, pivot, order by, limit, offset, label, format, options.
select A, Creturns only those columns.select *returns all.where B = 'East' and C > 200filters rows. Combine withand,or,not.group by Bwith an aggregate such asselect B, sum(C) group by Bsummarises. Aggregates:sum,count,avg,min,max.pivot Bturns the values of a column into column headings, e.g.select sum(C) pivot B.order by C descsorts. Default is ascending (asc).limit 5returns only the first 5 rows;offset 2skips the first 2 rows.label sum(C) 'Total'renames a column heading.format C '#,##0'formats values.
Writing conditions
- Text goes in single quotes:
where B = 'East'. Numbers need no quotes. - Not equal:
<>or!=. - Contains:
where A contains 'an'. This is case-sensitive; uselower(A) contains 'an'to ignore case. - Does not contain:
where not A contains 'an'. - Dates:
where A > date '2024-01-31'. - Empty cells:
where B is nulloris not null. - Sort by several columns:
order by B asc, C desc.
Column letters vs Col1, Col2
With a normal range, use the sheet's own column letters, not letters counted from the start of the range. For C1:E10, the columns are C, D and E, so "select C" works and "select A" gives a NO_COLUMN error.
When the data is an IMPORTRANGE result or an array like {A1:B10; D1:E10}, the sheet letters no longer apply. Use Col1, Col2, Col3, counted from the first column of that data.
Another sheet or spreadsheet
- Another tab in the same file:
=QUERY(Sales!A1:C100, "select A, C where B = 'East'", 1). Quote names with spaces:'Sales 2024'!A1:C100. - Another spreadsheet:
=QUERY(IMPORTRANGE("spreadsheet-url", "Sheet1!A1:D100"), "select Col1, Col4 where Col2 > 10", 1). Allow access the first time when Sheets asks.
Using a cell value in the query
Join it with &. Numbers: "select A where C > " & E1. Text needs quotes inside the string: "select A where B = '" & E1 & "'".
Example
Data in A1:C6:
Name Region Sales
Ann East 500
Ben West 300
Cara East 700
Dan West 200
Eve East 100East rows with sales over 200, biggest first:
=QUERY(A1:C6, "select A, C where B = 'East' and C > 200 order by C desc", 1)
Result:
Name Sales
Cara 700
Ann 500Total per region, largest first:
=QUERY(A1:C6, "select B, sum(C) group by B order by sum(C) desc", 1)
Result: East 1300, West 500, with headings Region and sum Sales.
Pivot version:
=QUERY(A1:C6, "select sum(C) pivot B", 1)
Result: one row with East 1300 and West 500 as column headings.
Where people go wrong
Putting clauses in the wrong order gives a parse error. Keep select, where, group by, pivot, order by, limit, offset, label, format, options, so
order bynever comes beforewhere.Using double quotes around text inside the query breaks it, because the whole query is already in double quotes. Write
'East'with single quotes.Using
select Aon a range likeC1:E10returns NO_COLUMN. Use the sheet's letters (C,D,E), orCol1,Col2for IMPORTRANGE and array data.Comparing a column that mixes numbers and text gives odd or empty results, because QUERY treats each column as one type. Clean the column so it holds only numbers, or only text.
The prompt you could have used
I have sales data in Google Sheets A1:C100 (Name, Region, Sales). Write a QUERY formula that returns Name and Sales for the East region where Sales is over 200, sorted by Sales descending, and explain each part.
Updated 16 November 2026. Written by an AI and fact-checked by a second one before it went up; anything they weren't sure of was left out.
Someone asked you something like this? Send them a link →
More questions nobody needed to ask
- How to Delete a Git Branch (Local and Remote)
- How to Use SUMIF in Excel (With Examples)
- How to Export Bookmarks from Chrome
- Percentage Change Between Two Numbers in Excel
- Excel IF Formula With Multiple Conditions (With Examples)
More Google Sheets questions · All questions · Just Fucking Prompt It