Pivot Table Calculated Fields in Excel
Pivot Table Calculated Fields is a Excel function that a calculated field creates a new column in your pivot table using existing fields. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Add custom calculations like profit margins, unit economics, and derived metrics directly inside your Pivot Table.
The Formula
"Add a Revenue Per Unit calculated field to a Pivot Table"
= Revenue / UnitsA calculated field creates a new column in your Pivot Table using existing fields. Revenue / Units gives you the average revenue per unit, calculated at the aggregation level of your Pivot Table layout.
Step-by-Step Breakdown
- Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field
- Name the field (e.g., "Revenue Per Unit")
- Formula references existing field names, not cell references
- The calculation applies at whatever grouping level your Pivot Table uses
- The result appears as a new Value field in the Pivot Table
Edge Cases & Warnings
- Calculated fields operate on sums, not individual rows — this can produce different results than row-level calculations
- Division by zero when a group has 0 units — no built-in error handling in calculated fields
- You can't use worksheet functions like IF or VLOOKUP in calculated fields
- For row-level calculations, add the formula to your source data instead
Examples
"Revenue=10000, Units=500 (Region: East)"
Revenue Per Unit = $20.00"Revenue=25000, Units=1000 (Region: West)"
Revenue Per Unit = $25.00Frequently Asked Questions
Why is my calculated field giving wrong averages?
Calculated fields divide the SUM of revenue by the SUM of units, not the average of individual ratios. This is correct for weighted averages but may differ from simple averages. For row-level calculations, compute in the source data.
Can I use IF in a calculated field?
No, but you can use algebraic equivalents. For example, instead of IF(Units>0, Revenue/Units, 0), add the condition as a column in your source data.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.