Calculated Fields in a Pivot Table

A calculated field in a pivot table is a new value you build from a formula, such as profit from Sales minus Cost, without adding a column to your data. You create it once and it fills in for every row, column and total of the pivot. This guide shows the steps, the one rule that makes calculated fields go wrong, and how to edit or remove them.

What is a calculated field?#

A calculated field is a formula that uses other fields in the pivot's source data. It appears in the PivotTable Fields list like a normal field, and you drag it into Values. Common examples:

The examples use this list in A1:E9:

How to add a calculated field to a pivot table#

Start with a pivot that has Region in Rows and Sales in Values. Then:

1. Click any cell inside the pivot.
2. Go to PivotTable Analyze > Fields, Items & Sets > Calculated Field. The menu is the same on Windows and Mac.
3. In Name, type Margin.
4. In Formula, delete the 0 after the equals sign. Type =(, double-click Sales in the Fields list, type -, double-click Cost, type )/, and double-click Sales again. The formula reads =(Sales-Cost)/Sales.
5. Click Add, then OK.

Excel adds Sum of Margin to the Values area. It shows decimals like 0.282, so format it: click the field's drop-down in the Values box, choose Value Field Settings > Number Format and pick Percentage.

To rename "Sum of Margin", click the heading cell and type a new name. It can't be exactly the same as a field name, so type Margin % or add a space at the end.

The rule: calculated fields use totals, not rows#

A calculated field doesn't run on each source row. Excel first sums each field for the group, then applies your formula to those sums. For East, the margin is (7,800 − 5,600) / 7,800 = 28.2%. That is the right answer for a ratio, because a group's margin should come from its total sales and total cost.

The same rule breaks formulas that must work row by row. Imagine the data had Units and Price instead of Sales, with prices of 1,200 for laptops, 600 for phones and 300 for tablets. A calculated field =Units*Price for East would multiply total units (3 + 4 + 6 = 13) by the sum of the prices (1,200 + 600 + 300 = 2,100) and show 27,300. The real East sales are 3×1,200 + 4×600 + 6×300 = 7,800.

The fix is to do row-level maths in the source data: add a Sales column with =C2*D2 filled down, refresh the pivot, and sum that column. Keep calculated fields for ratios and differences of totals.

Calculated fields for percent change#

If your data has one column per year, a calculated field can show growth. With Sales2023 and Sales2024 columns, the formula =(Sales2024-Sales2023)/Sales2023 gives each group's percent change, formatted as a percentage. It follows the usual percent change rule, (new − old) / old.

If the year is a field instead, with one Year column and one Sales column, you don't need a calculated field. Put Year in Columns, then right-click a value and choose Show Values As > % Difference From, with Year as the base field and (previous) as the base item. For a share of the total instead of growth, use percent of total in a pivot table.

How to edit, list or delete a calculated field#

All three happen in the same places:

• Edit. Open Calculated Field again, pick the field in the Name drop-down, change the formula and click Modify.
• Delete. Pick the field in Name and click Delete. Unticking it in the field list only hides it, and it still exists in the pivot cache.
• List all formulas. Go to PivotTable Analyze > Fields, Items & Sets > List Formulas. Excel adds a new sheet listing every calculated field and its formula, which is useful when you take over someone else's file.

Limits and common errors#

Calculated fields are handy, but they have limits worth knowing:

• No cell references. You can't use B1 or a named range in the formula. Put the number in the formula itself.
• #DIV/0! in some rows. A group with zero sales divides by zero. Use =IF(Sales=0,0,(Sales-Cost)/Sales).
• Greyed out. The option isn't available in pivots built on the Data Model; those use measures.
• Not the same as a calculated item. A calculated item adds a new item inside a field, such as "East + West" in Region. It's rarely needed and easy to double-count with.
• Errors after renaming source headers. If a field the formula uses disappears from the source, the pivot can't refresh. See the fix for the pivot field name is not valid error.

When a report needs ratios in fixed cells, a formula like =(SUM(D2:D9)-SUM(E2:E9))/SUM(D2:D9) gives the overall 29.1% margin with no pivot at all. The comparison of pivot tables vs SUMIFS helps you choose. Margins by month work too, once you group the dates in the pivot.

Practise it#

Calculated fields and the "totals, not rows" rule are favourite topics in pivot table interview questions. Practise pivot tables in a live spreadsheet, with nested rows, filters and percent of total, checked instantly. Then practise percentages and percent change so the margin and growth formulas come naturally.