QUERY practice exercises
QUERY runs a small SQL-like language over a range: pick columns, filter rows, group and sort, all in one formula. It is unique to Google Sheets and replaces a lot of manual filtering and pivot tables.
Syntax
QUERY(data, "select ... where ... group by ... order by ...", [headers])
data- The range, usually including the header row.
query- Text in quotes. Columns are referred to by letter:
select A, F where B = 'East'. headers- Optional. How many header rows the data has.
The practice data
An orders table: who sold what, where, and for how much. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Price | Sales |
| 2 | Ana | East | Widget | 12 | 5 | 60 |
| 3 | Ben | West | Gadget | 5 | 20 | 100 |
| 4 | Cara | East | Gadget | 8 | 20 | 160 |
| 5 | Dev | North | Widget | 20 | 5 | 100 |
| 6 | Ana | West | Gizmo | 3 | 50 | 150 |
| 7 | Ben | East | Widget | 7 | 5 | 35 |
| 8 | Cara | North | Gizmo | 2 | 50 | 100 |
| 9 | Dev | East | Gadget | 6 | 20 | 120 |
Worked example
=QUERY(A1:F9,"select A, F where B = 'East'")
Returns the Rep and Sales of every East order, with headers. Result:
| Rep | Sales |
| Ana | 60 |
| Cara | 160 |
| Ben | 35 |
| Dev | 120 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return the Rep and Sales of every order in the West.
2 Return the total Sales by Region.
3 Return Rep, Product and Sales for orders with more than 5 Units, highest Sales first.
Master QUERY in the full course
Lesson 16 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon QUERY mistakes
- Using double quotes inside the query text. Text values need single quotes:
where B = 'East'. - Referring to columns by header name. QUERY uses column letters (A, B, C) when the data is a range.
- Selecting a column that isn't in the group by or inside an aggregate like sum().
FAQ
Is QUERY available in Excel?
No. QUERY is Google Sheets only. In Excel the closest tools are FILTER, Power Query or pivot tables.
How do I use a cell value in a QUERY?
Build the text with &: "select A where B = '"&H1&"'".