Calculated Item in a Pivot Table: When and How to Use One

A calculated item in a pivot table is a new item inside one field, made from other items in the same field. The classic example is a Variance item equal to Actual minus Budget, sitting next to Actual and Budget as if it were real data. This guide shows when a calculated item is the right tool, how to add one step by step, and the grand total trap that catches almost everyone.

What is a calculated item in a pivot table?#

A calculated item is a formula that combines items of one row or column field. An item is one of the values in a field: in a Scenario field, the items are Actual and Budget. In a Region field, they are North, South and East.

It is easy to confuse with a calculated field in a pivot table, so here is the difference:

Use a calculated item when the numbers you want to combine sit in one column of your source data and are told apart by a label in another column.

The example data#

All the steps use this small sales table. Each row is one region in one scenario:

Build a pivot table with Region in Rows, Scenario in Columns and Sum of Sales in Values. If you haven't built one before, follow the beginner's guide to pivot tables first. You now have Actual and Budget side by side for each region, but no variance.

How to add a calculated item, step by step#

To add a calculated item, select an item label in the field you want to extend, then open the Calculated Item dialog.

1. Click the Actual or Budget column heading in the pivot table. The cell must be an item label, not a number.
2. Go to PivotTable Analyze (called Analyze or Options in older versions) > Fields, Items & Sets > Calculated Item.
3. In Name, type Variance.
4. In Formula, delete the 0. Double-click Actual in the Items list, type a minus sign, then double-click Budget. The formula reads =Actual-Budget.
5. Click Add, then OK.

A new Variance column appears after Budget:

The Variance column total is right: 2,000 + 2,000 − 3,000 = 1,000, the same as 106,000 − 105,000. If an item name has spaces, put it in single quotes in the formula, such as ='Actual 2026'-'Budget 2026'.

The grand total trap#

Calculated items are added into grand totals like any other item, and that is where results go wrong. Look at the grand total column on the right of the pivot table. For North it now shows 42,000 + 40,000 + 2,000 = 84,000. That number means nothing: it adds actual sales, budget sales and the gap between them.

Fix it in one of these ways:

• Turn off the grand total column. Go to PivotTable Design > Grand Totals and choose On for Columns Only. The Grand Total row at the bottom stays, and the meaningless column on the right goes.
• Combine items carefully. A calculated item such as =North+South in the Region field adds a "North + South" row, and the grand total at the bottom now counts North and South twice. Hide the original items with the field's filter, or don't show a grand total.

Subtotals behave the same way. Always check one total by hand after you add a calculated item.

Limits of calculated items#

Calculated items are an older feature with several rules. Excel will stop you, or the command will be greyed out, when:

• The field is grouped. You can't add a calculated item to a field with grouped items, such as dates grouped by month. Ungroup it first; see how pivot tables group dates to understand what grouping changes.
• The pivot table uses the Data Model. Pivot tables built with "Add this data to the Data Model" don't offer calculated items. Use a measure in Power Pivot instead.
• You need a cell reference. The formula can use only items, numbers and operators. It can't point to a worksheet cell or a defined name.
• The value field uses certain summary functions. Excel shows a message if you mix calculated items with summaries such as Average or StdDev. Keep the value field as Sum or Count.

Calculated items can also make a large pivot table slow, because Excel creates the new item for every combination of the other fields. To see every formula in the pivot table, use Fields, Items & Sets > List Formulas. Excel adds a new sheet that lists each calculated field and item.

Better options for common cases#

Before you add a calculated item, check whether a simpler tool does the job. For a variance, Show Values As is often cleaner:

1. Add Sum of Sales to Values a second time.
2. Right-click it > Show Values As > Difference From.
3. Set Base field to Scenario and Base item to Budget.

The Actual column of the second copy now shows Actual minus Budget, and no grand total is distorted. The same menu gives you each item's percent of the total. To put the variance first or last, sort the pivot table by that column.

You can also calculate outside the pivot table with a formula on the source data. For South's variance:

=SUMIFS(C2:C7,A2:A7,"South",B2:B7,"Actual")-SUMIFS(C2:C7,A2:A7,"South",B2:B7,"Budget")

The result is -3,000 (35,000 − 38,000), the same number the calculated item shows. This is a good way to check a calculated item before you trust it.

Practise it#

Calculated items make sense once you have built a few pivot tables yourself. Start with the first pivot tables lesson to place fields in Rows, Columns and Values, then move on to the second pivot tables lesson for summaries and value settings. You build each pivot table in a live spreadsheet and it checks your work instantly. Then try the exercise on this page to confirm a variance with SUMIFS.