Calculations Updated November 2026

Calculated item

Click a row label in the field you want to extend, open PivotTable Analyze, Fields, Items & Sets, Calculated Item, name it and write a formula from that field's items, such as =East + West. The new item appears as an extra row, and the Grand Total includes it, so turn totals off.

Syntax

Select an item in the field > PivotTable Analyze > Fields, Items & Sets > Calculated Item > Name, Formula = East + West

Part	What it is
`Field`	The row or column field whose items you combine; set by what you selected before opening the dialog
`Items list`	Double-click to insert an item name into the formula
`Formula`	Arithmetic on items of one field, for example ='Q1' - 'Q4'

Examples

Select a Region label > Calculated Item: Americas = East + West
Rows: Region. Values: Sum of Revenue

Result: A new Americas row equal to East plus West; the Grand Total includes East, West and Americas, so it is overstated.

Calculated Item on Status: Net = Approved - Rejected

Result: A row showing the difference between approved and rejected amounts.

Common mistakes

Calculated Item is grayed out: you clicked a value cell or a grouped field; select a label in an ungrouped field first.
The Grand Total doubles: the calculated item is added to the total, so hide the original items or turn the Grand Total off.
You get an error about a field with grouping: calculated items cannot be added to fields that use Group; ungroup first.
The pivot is on the Data Model: calculated items are not supported there.

Practice Calculated item

These drills check your answer on live data, free, no account needed.

Revenue by regionBuild a pivot that shows total Revenue for each Region.

Questions

What is the difference between a calculated field and a calculated item?

A calculated field is a new column computed from other value fields. A calculated item is a new row (or column) computed from other items of the same field.

Is there a better way than a calculated item?

Often yes: add a mapping column in the source (Region to Area) and pivot on that. Subtotals then work and nothing double counts.

Calculated fieldInsert a calculated field with Fields, Items & Sets to compute margin, commission or price per unit from other fields. Formula rules, why averages go wrong.
Remove grand totals and subtotalsTurn grand totals off for rows, columns or both and remove subtotals from nested fields on the Design tab. How to bring them back and show subtotals on top.
Difference from previousCompare periods inside a pivot table with Show Values As, Difference From and % Difference From. Pick the base field and (previous); why period one is blank.