Spreadsheet Practice

How to add a calculated column in QUERY in Google Sheets

Google Sheets formula

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

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

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

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.

=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{6 rows × 3 cols} "Rep", "Revenue", "Commission", "Ana Ruiz", …

  1. A1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …

    A range: 13 rows × 6 columns.

  2. QUERY(A1:F13,"select B, F, F*0.05 where C = 'East' label F*0.05 'Commission' format F*0.05 '$#,##0.00'",1){6 rows × 3 cols} "Rep", "Revenue", "Commission", "Ana Ruiz", …

    QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.

    • dataA1:F1313 rows × 6 cols: "Date", "Rep", "Region", "Product", …
    • query"select B, F, F*0.05 where C = 'East' label F*0.05 'Commission' format F*0.05 '$#,##0.00'"
    • headers1

In 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)).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=QUERY(A1:F13,"select B, F/E where C = 'East' label F/E 'Unit price'",1)spills 6 × 2Different resultAny 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.

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.

Add a calculated column in QUERY

In H1, use QUERY to return the Rep and Revenue of the East orders plus a third column, Commission, that is 5% of the revenue.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.