# How to add a calculated column in QUERY in Google Sheets

Write the calculation in select and name it with label: `=QUERY(A1:F13,"select B, F, F*0.05 where C = 'East' label F*0.05 'Commission' format F*0.05 '$#,##0.00'",1)`.

**Google Sheets:** `=QUERY(A1:F13,"select B, F, F*0.05 where C = 'East' label F*0.05 'Commission' format F*0.05 '$#,##0.00'",1)`

**Excel:** Excel has no QUERY function. Stack the filtered columns with the calculated one: `=LET(f,FILTER(A2:F13,C2:C13="East"),HSTACK(CHOOSECOLS(f,2,6),CHOOSECOLS(f,6)*0.05))`.

## 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, F, F*0.05 where C = 'East' label F*0.05 'Commission' format F*0.05 '$#,##0.00'",1)`

Result (spills 6 × 3): Rep, Revenue, Commission; Ana Ruiz, $2,080, $104.00; Lee Chen, $1,740, $87.00; Raj Patel, $1,422, $71.10; Kim Ortiz, $1,305, $65.25; Mo Diaz, $1,092, $54.60

## How it works

select accepts arithmetic on columns: `+`, `-`, `*` and `/`. `F*0.05` is calculated for every row. Its default header is the expression itself, so `label` names it, and `format` shows it as currency. Both clauses must repeat the expression exactly as written in select.

## Other ways

- `=QUERY(A1:F13,"select B, F/E where C = 'East' label F/E 'Unit price'",1)`: Any arithmetic works, such as revenue divided by units for the unit price. (Rep and unit price.)

## Common mistakes

- Arithmetic on an empty or text cell gives an empty result for that row, not an error.
- label and format must name the same expression as select. If you change `F*0.05` in select, change it in label and format too, or the query fails.
- QUERY has no functions like ROUND. Use format to control how the result looks.

## Questions

**Can the rate come from a cell?**

Yes, but join it into every place the expression appears: select, label and format. A helper column next to the data is often simpler.

**Can I calculate on totals?**

Yes: `select C, sum(F)/sum(E) group by C` gives the average unit price per region.

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/add-a-calculated-column-in-query