# How to use pivot in a QUERY in Google Sheets

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.

**Google Sheets:** `=QUERY(A1:F13,"select B, sum(F) group by B pivot C",1)`

**Excel:** Excel has no QUERY function. Microsoft 365 has PIVOTBY: `=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.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Rep | Region | Product | Units | Revenue |
| 2026-07-03 | Ana Ruiz | East | Widget | 40 | 2080 |
| 2026-07-06 | Raj Patel | West | Gadget | 25 | 2225 |
| 2026-07-10 | Lee Chen | East | Gizmo | 12 | 1740 |
| 2026-07-14 | Ana Ruiz | North | Gadget | 30 | 2670 |
| 2026-07-17 | Kim Ortiz | West | Widget | 55 | 2860 |
| 2026-07-21 | Raj Patel | East | Widget Pro | 18 | 1422 |
| 2026-07-24 | Lee Chen | South | Gadget | 22 | 1958 |
| 2026-07-29 | Kim Ortiz | East | Gizmo | 9 | 1305 |
| 2026-08-04 | Mo Diaz | North | Gizmo | 14 | 2030 |
| 2026-08-07 | Raj Patel | South | Widget | 20 | 1040 |
| 2026-08-12 | Kim Ortiz | North | Gadget | 16 | 1424 |
| 2026-08-18 | Mo Diaz | East | Gizmo Mini | 28 | 1092 |

Formula in H1: `=QUERY(A1:F13,"select B, sum(F) group by B pivot C",1)`

Result (spills 6 × 5): Rep, East, North, South, West; Ana Ruiz, $2,080, $2,670, , ; Kim Ortiz, $1,305, $1,424, , $2,860; Lee Chen, $1,740, , $1,958, ; Mo Diaz, $1,092, $2,030, , ; Raj Patel, $1,422, , $1,040, $2,225

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

## Other ways

- `=QUERY(A1:F13,"select B, count(A) group by B pivot C",1)`: 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)`: 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/pivot-with-query