Calculated fields in Excel - Step-by-Step Guide

Introduction

Calculated fields let you add new data in a pivot table by using formulas based on existing data. This helps you see extra insights without changing your original data.

When you want to find profit by subtracting cost from sales in a sales report pivot table

When you need to calculate a commission as a percentage of sales inside a pivot table

When you want to add a new column showing the difference between two existing columns in a pivot table

When you want to quickly analyze ratios or percentages based on your pivot table data

When you want to avoid changing source data but still add custom calculations

Steps

Step 1: Click

- any cell inside the pivot table

PivotTable Analyze tab appears in the ribbon

Step 2: Click

- PivotTable Analyze tab > Fields, Items & Sets dropdown

A menu opens with options including Calculated Field

Step 3: Select

- Calculated Field option

Insert Calculated Field dialog box opens

Step 4: Type

- Name box in the dialog

You enter a name for your new calculated field, for example 'Profit'

Step 5: Create

- Formula box in the dialog

You write a formula using existing fields, for example =Sales - Cost

Step 6: Click

- Add button in the dialog

The formula is added to the list of fields

Step 7: Click

- OK button in the dialog

The calculated field appears as a new column in the pivot table

Before vs After

Before

Pivot table shows columns for Sales and Cost only

After

Pivot table shows columns for Sales, Cost, and a new Profit column calculated as Sales minus Cost

Settings Reference

Name

📍 Insert Calculated Field dialog box

To name the new calculated field so you can identify it in the pivot table

Default: Empty

Formula

📍 Insert Calculated Field dialog box

To define the calculation for the new field based on existing pivot table fields

Default: Empty

Common Mistakes

Typing field names incorrectly in the formula

Excel will not recognize the field and show an error or ignore the formula

Use the field list in the dialog to insert field names or double-check spelling exactly as shown

Using cell references like A1 or B2 in the calculated field formula

Calculated fields only work with pivot table field names, not cell references

Always use the field names from the pivot table fields list in your formula

Summary

Calculated fields add new columns to pivot tables using formulas based on existing data.

They help analyze data without changing the original source.

Formulas must use pivot table field names, not cell references.