# QUERY: pivot, limit and text matching (Google Sheets lesson 14 of 17)

> Match part of a name, keep only the top few rows, and spread a category across the columns.

Source: https://spreadsheetpractice.com/google-sheets/learn/query-pivot-limit-contains · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets power tools"
- **Time:** about 11 minutes, 3 graded exercises
- **Functions:** `QUERY`, `pivot`, `limit`, `contains`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=QUERY(A1:E11,"select C, E where B contains 'desk' order by E desc limit 3",1)`: Top 3 desk orders

## 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

| Formula | What it does |
|---|---|
| `=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 D |

## Watch 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

Solve them in the live grid at [QUERY: pivot, limit and text matching](https://spreadsheetpractice.com/google-sheets/learn/query-pivot-limit-contains). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **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.
2. **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.
3. **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.

Previous: [QUERY: group by, totals and labels](https://spreadsheetpractice.com/google-sheets/learn/query-group-by) · Next: [MAP, BYROW and LAMBDA in Sheets](https://spreadsheetpractice.com/google-sheets/learn/map-lambda-byrow-sheets)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
