Pivot Table Calculations: The Complete Guide
Beyond Sum and Count. This guide covers Show Values As percentages, calculated fields (custom formulas), calculated items (custom rows), running totals, YoY comparisons — and when to move to Power Pivot's DAX instead.
Built-in aggregations
Right-click a value cell → Summarize Values By. Every option:
- Sum — total of numeric values (default for numbers)
- Count — number of items, including blanks and text (default for text fields)
- Average — mean of numeric values
- Max / Min — largest / smallest value
- Product — multiplication (rare in practice)
- Count Numbers — count of numeric values only, ignoring text
- StdDev / StdDevp — sample or population standard deviation
- Var / Varp — sample or population variance
Show Values As — the power move
Right-click value → Show Values As. Turns raw values into contextual percentages, ranks, or comparisons — without extra formulas.
% of Grand Total
Each value as % of the whole pivot. Great for "what % of total revenue does each product contribute?"
% of Row Total / % of Column Total
Each value as % of its row or column total. Row: composition within a row. Column: composition within a column.
% of Parent Row / Column Total
For nested layouts. Shows each item as % of its immediate parent. E.g., if rows are Region → State, State values show as % of their Region's total.
Running Total In
Cumulative sum along a chosen field. Set "Base field" to Date to get year-to-date running totals. Perfect for cumulative revenue charts.
% Running Total In
Cumulative sum expressed as a percentage of the total. When you reach 100%, you've covered the whole dataset. Good for pareto charts.
Difference From / % Difference From
Compares each value to a "base" value from the same field. Base field = Date, Base item = Previous or Next → automatic period-over-period difference or percentage change.
Rank Smallest to Largest / Rank Largest to Smallest
Assigns a rank to each row within the chosen scope. Great for "which product is #1 by revenue?"
Drag Revenue to Values twice. Change the first to Sum (raw dollars). Change the second to Show Values As → % of Grand Total. Now you see both the value and its share in the same pivot.
Calculated fields
Custom formulas that operate on source field totals. Add via PivotTable Analyze → Fields, Items & Sets → Calculated Field.
Example: Gross margin
Name: Gross Margin
Formula: = Revenue - Cost
"Gross Margin" appears in the field list. Drag to Values. Excel calculates Revenue minus Cost for each row/column group.
Example: Average deal size
Name: Avg Deal Size
Formula: = Revenue / Deals
Divide two summed fields to get a ratio. Simpler than adding a helper column to source data.
Example: Effective tax rate
Name: Effective Tax Rate
Formula: = Tax / (Revenue - Tax)
Nested arithmetic works. Use standard Excel operators (+, -, *, /) and parentheses for grouping.
Calculated field limitations
A calculated field computes SUM(x)/SUM(y) at every level. If you want a weighted average (like weighted average price per unit), the math works at the grand total but often wrong at subtotals. Use a helper column in source data or move to DAX.
Calculated fields can't reference specific pivot cells — only entire fields from source data. Complex ratios that depend on specific row/column combinations need Power Pivot.
Calculated items
Formulas that add a custom row within an existing field. Rare but powerful for custom groupings.
PivotTable Analyze → Fields, Items & Sets → Calculated Item.
Example: Combined "Americas" row
If your data has "USA", "Canada", "Mexico" as separate regions, and you want an "Americas" total row:
Name: Americas
Formula: = USA + Canada + Mexico
An "Americas" row appears in the pivot alongside the individual countries.
Because "Americas" is a sum of three existing items, the grand total now double-counts. Excel warns about this. Consider whether you actually want a calculated item vs. adjusting the source data.
Common calculation patterns
Year-over-year comparison
- Add Date to Rows, group by Year
- Add Revenue to Values (Sum)
- Add Revenue to Values again → Show Values As → % Difference From → Base field: Date, Base item: (previous)
Now you see raw revenue and YoY% change side by side.
Contribution to total
- Rows: Product
- Values: Revenue (Sum)
- Values: Revenue again → Show Values As → % of Grand Total
Instantly see which products contribute what share of total revenue.
Running year-to-date
- Rows: Date (grouped by Month)
- Values: Revenue → Show Values As → Running Total In → Base field: Date
Each month shows YTD revenue through that month.
Weighted average price
Excel calculated fields do this wrong. Workaround: add a "Revenue" helper column in source (Price × Quantity). In the pivot: Revenue / Quantity as a calculated field. This works because the underlying operation is a properly weighted ratio of sums.
When to switch to Power Pivot
- Weighted averages that respect filter context
- Distinct counts (Sum(distinct customers))
- Calculations that reference multiple related tables
- Time intelligence (Same Period Last Year, YTD, MTD)
- Row-level calculations that reference other rows in the same table
Deep dive: Power Pivot Guide
Calculated fields without the syntax
Building a calculated field means writing formulas in a tiny dialog with no autocomplete. Excel Wizard lets you describe the metric ("gross margin percentage" or "YoY revenue growth") and it creates the calculated field with correct syntax and formatting.
Install Excel Wizard →Frequently asked questions
How do I add a calculated field to a pivot table?
PivotTable Analyze → Fields, Items & Sets → Calculated Field. Enter a name and formula using source field names. Click Add. The calculated field appears in the field list; drag to Values.
What's the difference between a calculated field and a calculated item?
A calculated field creates a new field derived from other fields (like margin = revenue - cost). A calculated item creates a new row within an existing field (like combining USA + Canada + Mexico into an "Americas" row). Fields are common; items are rare.
Why does my calculated field give the wrong average?
Calculated fields compute at grand-total level using summed inputs. This breaks weighted averages. Workaround: create the numerator in source data (e.g., add a "Revenue" = Price × Qty column) and use a ratio of sums in the calculated field.
Can I use IF statements in calculated fields?
Yes, but they're limited. IF works with source field references and constants but can't reference specific pivot cells. For complex conditional logic, use DAX in Power Pivot instead.