Excel like a finance pro.
Pivot tables · Calculations
Calculated fields
Adds a new value field computed from other fields with a formula, like Revenue minus Cost.
When to use it
A calculated field is a new value field computed from other fields with a formula, such as Revenue minus Cost. It calculates on the summed values of each cell.
The shape of it
- How
PivotTable Analyze, Fields, Items and Sets, Calculated Field.
Worked examples
Difference of fields
PivotTable Analyze, Fields Items and Sets, Calculated Field, Name Margin, Formula =Revenue-Cost → A Margin column in the pivot
Sums then subtracts.
Ratio of sums
Calculated Field, =Revenue/Units → Average price per unit as sum over sum
Ratios of sums are correct.
The trap
Calculated Field, =IF(Revenue>1000,Revenue*0.1,0) → Applied to the summed cell, not each row
This is where it goes wrong.
Worth knowing
- It calculates on summed totals, so ratios of sums are fine but averages of ratios are wrong. Use the data model for real measures.
- Ratios of sums work; anything that should apply per row before summing needs a helper column in the source or a DAX measure.
- Calculated fields appear in every pivot on that cache.
- List Formulas documents them all.
Where it goes wrong
- Averages of calculated fields are wrong because the field is computed on totals.
- Cannot reference cells outside the pivot.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.