Pivot Table Calculated Field vs. Calculated Item

A calculated field adds a new column that's computed from other fields' totals (like Profit ÷ Revenue), while a calculated item adds a new row or column label within one existing field (like a "Q1+Q2" item inside a Quarter field). They solve different problems, and mixing them up — or averaging a per-row ratio instead of recomputing it from sums — is the most common source of wrong numbers in a pivot table.

What is a calculated field?

A calculated field is a new field, built from a formula that references other fields by name, that Excel computes using the sum of each referenced field first and then applies the formula — not row by row. It behaves like adding a new column to your Values area that didn't exist in the source data.

PivotTable Analyze > Fields, Items & Sets > Calculated Field

Name: Margin
Formula: =(Revenue-Cost)/Revenue

Excel computes it as: SUM(Revenue) and SUM(Cost) for each row/group,
then applies (Revenue-Cost)/Revenue to those totals — not per source row.

What is a calculated item?

A calculated item is a new item inside an existing field — for example, adding a "H1" item to a Quarter field that sums Q1 and Q2 — and it only works with the classic PivotTable engine, not a pivot built on the Data Model (Power Pivot). Calculated items are also restricted to Sum as the summary function and can behave unpredictably alongside calculated fields in the same pivot.

Right-click an item in the field (e.g. "Q1") > Fields, Items & Sets > Calculated Item

Name: H1
Formula: =Q1+Q2

Adds a new row/column labeled "H1" that sums the Q1 and Q2 rows.

If the pivot's source data was added to the Data Model when the pivot was created, the Calculated Item option is greyed out — use a DAX measure in Power Pivot instead.

Why does summing a ratio column give the wrong total?

If your source data already has a per-row ratio column (like a "Margin %" computed in the source sheet) and you drag it into Values as Average, the pivot averages the individual row ratios — which is not the same as the true combined ratio across all the rows in that group.

Source data:
Rep A: Revenue 100,000  Cost 60,000  Margin% (per row) = 40%
Rep B: Revenue  50,000  Cost 40,000  Margin% (per row) = 20%

Pivot with Margin% dragged in as Average:
(40% + 20%) / 2 = 30%    ← WRONG (average of two ratios)

True combined margin:
Total Revenue = 150,000, Total Cost = 100,000, Total Profit = 50,000
50,000 / 150,000 = 33.33%    ← CORRECT (ratio of the sums)

The fix is to never average a pre-computed ratio column. Instead, build a calculated field from the underlying Revenue and Cost fields — =(Revenue-Cost)/Revenue — so Excel sums Revenue and Cost per group first, then divides, which reproduces the 33.33% figure correctly no matter how the data is grouped or filtered afterward.

How do I edit or delete a calculated field?

Reopen the same dialog, pick the field name from the dropdown, edit the formula, and click Modify — or select it and click Delete to remove it entirely.

PivotTable Analyze > Fields, Items & Sets > Calculated Field
> Name dropdown > select the existing field > edit Formula > Modify (or Delete)

Calculated field or calculated item: which do I need?

Reach for a calculated field when the new metric is derived from other fields (like a margin or ratio), and reach for a calculated item only when you need a new label inside a single existing field.

	Calculated field	Calculated item
Adds	A new field (column of Values)	A new item (row/column label) inside a field
Formula references	Other fields	Other items in the same field
Works with Data Model / Power Pivot	No — use a DAX measure instead	No — greyed out entirely
Typical use	Margin %, average order value, commission	Custom subtotal like "H1" from Q1+Q2

Common mistakes with pivot calculations

Nearly every wrong pivot calculation traces back to computing a ratio at the wrong stage — per row instead of per group, or the other way around.

• Averaging or summing a ratio field from the source data: produces average-of-ratios instead of ratio-of-sums.
• Using a calculated item on a Data Model pivot: the option simply isn't available — you'll need a DAX measure instead.
• Nesting calculated fields inside calculated items (or vice versa): Excel allows it but the result is often not what you'd expect — verify against a manual calculation before trusting it.
• Forgetting a calculated field recomputes per group: if you expected a static per-row value, you'll be surprised the number changes when you add or remove a Row field.