Level 1Preview: Sheets SuperpowersProject, 6 min

Ask your data questions with QUERY

The task

A preview of Level 3. QUERY lets you ask a whole table a question in plain words, like a database. You'll write one QUERY in H1 and sharpen it four times.
  1. Total sales by regionIn H1, type =QUERY(A1:E31, "select C, sum(E) group by C"). Column letters name the columns, and the result fills in with its own headers.
  2. Biggest region firstEdit H1 so the biggest region comes first: add order by sum(E) desc to the end of the query.
  3. The three biggest dealsReplace the query with one that lists the three biggest deals over $1,000: =QUERY(A1:E31, "select A, B, E where E > 1000 order by E desc limit 3")
  4. A report with real column namesLast one. Deals and revenue per rep, with readable headers: =QUERY(A1:E31, "select B, count(A), sum(E) group by B order by sum(E) desc label count(A) 'Deals', sum(E) 'Revenue'")

The data

The starting sheet. Cells outlined in green are where your formulas go.

ABCDEFGH
1OrderRepRegionProductAmount
24001TheoSouthEnterprise$1,040
34002MayaSouthStarter$105
44003JonahWestStarter$655
54004TheoWestEnterprise$1,295
64005PriyaCentralStarter$455
74006MayaWestPro$405
84007JonahSouthPro$170
94008PriyaSouthEnterprise$2,490
104009TheoSouthStarter$635
114010JonahSouthStarter$310
124011MayaSouthStarter$270
134012TheoCentralPro$305
144013TheoCentralStarter$720
154014PriyaEastPro$315
164015PriyaSouthEnterprise$2,280
174016MayaWestStarter$340
184017PriyaSouthPro$185
194018JonahCentralPro$335
204019JonahEastPro$560
214020JonahSouthEnterprise$1,115
224021TheoWestStarter$415
234022PriyaWestStarter$615
244023JonahEastEnterprise$1,730
254024MayaCentralPro$760
264025JonahEastStarter$550
274026MayaSouthEnterprise$1,505
284027PriyaWestPro$480
294028TheoSouthStarter$780
304029TheoSouthPro$425
314030PriyaEastPro$365

See all lessons