Excel Pivot Table Calculations Guide (2026)
Home›Excel›Pivot Tables›Calculations
ExcelSub-Guide⏱ 14 min read

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?"

Show the same field twice

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

Weighted averages break in calculated fields

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.

Only source fields, no cell references

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.

Calculated items break grand totals

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

  1. Add Date to Rows, group by Year
  2. Add Revenue to Values (Sum)
  3. 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

  1. Rows: Product
  2. Values: Revenue (Sum)
  3. Values: Revenue again → Show Values As → % of Grand Total

Instantly see which products contribute what share of total revenue.

Running year-to-date

  1. Rows: Date (grouped by Month)
  2. 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

Excel Wizard

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.