Need a metric the source data doesn’t have — margin %, average price, profit? A calculated field adds a formula inside the pivot that works on the summarized totals, no helper column required.
The example
A margin % field computed inside the pivot.
| A | B | |
|---|---|---|
| 1 | Product | Margin % |
| 2 | Widget | 37% |
| 3 | Gadget | 35% |
The formula
A formula that lives in the pivot:
How it works
Calculated fields operate on the summarized data:
- On the pivot, go to PivotTable Analyze → Fields, Items & Sets → Calculated Field.
- Give it a name and a formula using existing field names:
=Profit / Revenue. - It joins the field list; drag it into Values like any field.
- Crucially, it computes on the totals (SUM of Profit ÷ SUM of Revenue), not row-by-row — correct for ratios.
Calculated fields work on sums. “Margin %” = SUM(Profit)/SUM(Revenue), which is the right way to aggregate a ratio. Beware fields that average a per-row percentage — averaging percentages is usually wrong; recompute from the totals instead.
Try it: interactive demo
Revenue & profit → margin % (on totals).
Variations
Average price
Calc field:
Commission
Apply a rate:
Helper column instead
Add to source, then pivot it (row-level).
Pitfalls & errors
Operates on totals, not rows. Great for ratios, but it can’t do row-level logic (like an IF per transaction) — use a source helper column for that.
Use field names, not cells. Calculated-field formulas reference field names (Profit, Revenue), not cell addresses.
Limited functions. Calculated fields support basic math, not lookups or volatile functions. Complex logic belongs in the source.
Practice workbook
Frequently asked questions
How do I add a calculated field to a PivotTable?
Does a calculated field work row by row?
Why is averaging a percentage field wrong?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class