How to use pivot in a QUERY in Google Sheets
Google Sheets formula
=QUERY(A1:F13,"select B, sum(F) group by B pivot C",1)Add a pivot clause after group by: =QUERY(A1:F13,"select B, sum(F) group by B pivot C",1) puts each region in its own column.
- Result on the example
- a 6-row list that spills from H1 in
H1 - Excel
- See below
See it work
The formula is already in H1. Change any input and the result updates, or edit the formula to experiment.
How it works
group by B makes the rows, one per rep. pivot C turns each region into a column, and sum(F) fills each cell with that rep's revenue in that region. A rep with no sales in a region gets an empty cell. Both the rows and the pivot columns come back in A to Z order.
=QUERY(A1:F13,"select B, sum(F) group by B pivot C",1)Result{6 rows × 5 cols} "Rep", "East", "North", "South", …
A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …A range: 13 rows × 6 columns.
QUERY(A1:F13,"select B, sum(F) group by B pivot C",1){6 rows × 5 cols} "Rep", "East", "North", "South", …QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.- data
A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", … - query
"select B, sum(F) group by B pivot C" - headers
1
- data
In Excel
=PIVOTBY(B2:B13,C2:C13,F2:F13,SUM). In Excel 2021, use SUMIFS with a column of reps and a row of regions as criteria.Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =QUERY(A1:F13,"select B, count(A) group by B pivot C",1) | spills 6 × 5Different result | Use count() to see how many orders each rep has in each region. Order counts instead of revenue. |
| =QUERY(A1:F13,"select sum(F) pivot C",1) | spills 2 × 4Different result | Pivot without group by gives one row of totals per region. A single row: total revenue per region. |
Common mistakes
- pivot needs an aggregate such as sum() or count() in select; without one QUERY returns #VALUE!.
- Combinations with no data come back as empty cells, not 0, so an AVERAGE across a row skips them. Use SUM, or turn the blanks into zeros in a copy, if that matters.
- The pivot column headers come from the data, so label can't rename them one by one.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Use pivot in a QUERY
H1, use QUERY to build a table with one row per rep, one column per region, and the total revenue in each cell.Questions
Where does pivot go in the query?
After group by and before order by: select, where, group by, pivot, order by, limit, offset, label.
How do I add row totals?
QUERY has no totals. Put =SUM(I2:L2) next to the first row and fill it down, or use a pivot table, which adds totals for you.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.