📎 This article includes 1 downloadable practice file ↓
If Excel is your home, Google Sheets feels familiar — until you discover QUERY, a function with no Excel equivalent that lets you write SQL-style questions inside a cell.
In this article
The shape of a QUERY
=QUERY(A1:F200, "select B, sum(F) where A = 'North' group by B order by sum(F) desc", 1)- First argument: the data range.
- Second: the query text. Columns are referred to by letter (A, B, C…).
- Third: number of header rows (usually 1).
Excel feature → QUERY
| You do in Excel | QUERY text |
|---|---|
| AutoFilter North | select * where A = 'North' |
| Hide columns | select A, B, F |
| Sort largest first | select * order by F desc |
| Pivot: total by product | select B, sum(F) group by B |
| Pivot with columns | select A, sum(F) group by A pivot B |
| Top 5 | select * order by F desc limit 5 |
| Rename a header | … label sum(F) 'Revenue' |
Filter by a cell value
=QUERY(A1:F200, "select * where A = '" & H1 & "'", 1)Build the query text with &. Text values need single quotes inside the double-quoted string.
💡 Dates must be written as
date '2025-03-01'. From a cell: "where C >= date '" & TEXT(H2,"yyyy-mm-dd") & "'".Combine with IMPORTRANGE
=QUERY(IMPORTRANGE("SHEET_URL", "Sales!A1:F"), "select Col2, sum(Col6) group by Col2", 1)With IMPORTRANGE the columns are called Col1, Col2… instead of letters. This pattern builds live summaries from other people’s sheets.
QUERY is SQL-flavoured, so learning real SQL helps both ways — practise in the SQL Playground.
Try it yourself: step by step
- Download sales.csv. In Google Sheets choose File → Import → Upload → Replace current sheet.
- In an empty area type
=QUERY(A1:G61, "select C, sum(F) group by C", 1)— quantity by region. - Sort it: add
order by sum(F) descbefore the closing quote. - Put “North” in cell J1 and try
=QUERY(A1:G61, "select * where C = '" & J1 & "'", 1). - Pivot:
=QUERY(A1:G61, "select C, sum(F) group by C pivot E", 1)gives regions by product in one formula.
📎 Practice files for this article
- 🧾Sample sales data (CSV)60 rows: invoice, date, region, rep, product, qty, price u2014 import into any database or Google Sheets.⬇ CSV · 3 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.