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.

ABCDEF
1RepRegionProductUnitsPriceSales
2AnaEastWidget12560
3BenWestGadget520100
4CaraEastGadget820160
5DevNorthWidget205100
6AnaWestGizmo350150
7BenEastWidget7535
8CaraNorthGizmo250100
9DevEastGadget620120

Worked example

=QUERY(A1:F9,"select A, F where B = 'East'")

Returns the Rep and Sales of every East order, with headers. Result:

RepSales
Ana60
Cara160
Ben35
Dev120

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.

H2

2 Return the total Sales by Region.

H2

3 Return Rep, Product and Sales for orders with more than 5 Units, highest Sales first.

H2

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 pricing

Common QUERY mistakes

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&"'".

Keep practicing