How to Use Calculated Field in Pivot Table in Excel

To use a calculated field in an Excel pivot table, open the pivot table, go to the PivotTable Analyze tab, select Fields, Items & Sets, then choose Calculated Field. Enter a name and formula using existing fields, then click OK to add the calculated field to your pivot table.

Syntax

The syntax for creating a calculated field in a pivot table involves naming the field and writing a formula using existing pivot table fields.

Steps:

• Name: The name you assign to the new calculated field.
• Formula: A formula using existing field names combined with operators like +, -, *, and /.

Example formula: =Sales - Cost

text

Name: Profit
Formula: =Sales - Cost

Example

This example shows how to add a calculated field named Profit that subtracts Cost from Sales in a pivot table.

text

1. Click anywhere inside your pivot table.
2. Go to the <strong>PivotTable Analyze</strong> tab on the ribbon.
3. Click <strong>Fields, Items & Sets</strong> > <strong>Calculated Field</strong>.
4. In the dialog box, enter <code>Profit</code> as the name.
5. In the formula box, type <code>=Sales - Cost</code>.
6. Click <strong>Add</strong> then <strong>OK</strong>.
7. The new <code>Profit</code> field appears in the pivot table values area.

Output

Pivot table now shows a new column 'Profit' with values calculated as Sales minus Cost.

Common Pitfalls

Common mistakes when using calculated fields include:

• Using incorrect field names that don't match exactly the pivot table fields.
• Including cell references or ranges instead of field names in the formula.
• Trying to use calculated fields on data types that don't support arithmetic (like text fields).
• Not refreshing the pivot table after adding the calculated field.

Always use the exact field names shown in the pivot table field list and simple arithmetic operators.

text

Wrong formula example:
=Sales - B2

Right formula example:
=Sales - Cost

Quick Reference

Step	Action
1	Select pivot table
2	Go to PivotTable Analyze tab
3	Click Fields, Items & Sets > Calculated Field
4	Enter name for calculated field
5	Write formula using existing fields
6	Click Add then OK
7	View calculated field in pivot table

Key Takeaways

Calculated fields let you create new data columns in pivot tables using formulas with existing fields.

Always use exact pivot table field names in your calculated field formulas.

Access calculated fields from the PivotTable Analyze tab under Fields, Items & Sets.

Avoid using cell references; formulas must use pivot table field names only.

Refresh your pivot table to see updates after adding calculated fields.