Google Sheets lesson 14 of 17, Sheets power tools
QUERY: pivot, limit and text matching
Quick answer
Match part of a name, keep only the top few rows, and spread a category across the columns.
- Key formula
=QUERY(A1:E11,"select C, E where B contains 'desk' order by E desc limit 3",1)(Top 3 desk orders)
Watch it done
1 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
where B contains 'desk' keeps rows whose text includes a word anywhere in it. It ignores nothing: matching is case-sensitive in QUERY, so write the text the way the data does. For a pattern use like 'S%', where % stands for any run of characters.
limit 3 keeps only the first three rows after sorting, which turns "order by Revenue desc" into a top-3 list.
pivot B turns the values of a column into column headings. select A, sum(E) group by A pivot B returns one row per region and one column per product, with the totals where they cross.
Syntax
=QUERY(A1:E11,"select C, E where B contains 'desk' order by E desc limit 3",1)Top 3 desk orders=QUERY(A1:E11,"select A, sum(E) group by A pivot B",1)Region by product table=QUERY(A1:E11,"select * where B like 'D%'",1)Products starting with DWatch out for
- Text in a query is in single quotes; the whole query is in double quotes.
- contains is case-sensitive. "desk" does not match "Desk".
- With pivot, every selected column must be grouped or aggregated, as with group by.
The exercises
- Top three desk orders. In G1, use QUERY on A1:E11 to return the rep and revenue of orders whose product contains desk (lower case), biggest revenue first, keeping only three rows. Pass 1 for headers.
- Region by product. In G1, return total revenue for each region, with one column for each product. Use QUERY on A1:E11, group by the region and pivot on the product. Pass 1 for headers.
- Average of the bigger orders. In G1, average the revenue for each region, but only for orders of at least 5 units. Label the average column Avg order. Use QUERY on A1:E11 with 1 header row.
The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Top three desk orders
- Region by product
- Average of the bigger orders