How to Create a Calculated Field Inside a Pivot Table in Excel

A Pivot Table in Microsoft Excel is one of the most powerful tools for summarising and analysing large datasets. However, the standard Pivot Table interface only allows you to aggregate data using built-in functions like SUM, COUNT, and AVERAGE. If you need to perform a custom calculation that does not exist as a standard aggregation—such as calculating a profit margin percentage, a commission rate, or a unit cost—you must create a Calculated Field. A Calculated Field allows you to define your own formula that operates directly on the summarised data within the Pivot Table, without altering your original source data.

When to Use a Calculated Field Instead of a Regular Formula

You might be tempted to simply add a new column to your raw data and write a standard Excel formula. While this works for row-level calculations, it fails when you need the calculation to adapt dynamically to the Pivot Table’s grouping. For example, if your Pivot Table groups sales by region and you want to see the profit margin for each region, a Calculated Field will automatically divide the summed revenue by the summed cost for each regional group. A regular formula in the source data would only calculate the margin for individual transactions, which is a fundamentally different number.

Preparing Your Source Data

For this example, assume your source data contains the following columns: Product, Region, Revenue, and Cost. You want to create a Pivot Table that shows total revenue and total cost per region, plus a calculated profit margin percentage.

1. Select your data range.
2. Click the Insert tab on the Ribbon.
3. Click PivotTable.
4. Choose to place it in a new worksheet and click OK.
5. In the PivotTable Fields pane, drag Region to the Rows area.
6. Drag Revenue to the Values area (it will default to Sum of Revenue).
7. Drag Cost to the Values area (it will default to Sum of Cost).

Inserting a Calculated Field

Now that your basic Pivot Table is configured, you can add the custom profit margin calculation.

1. Click anywhere inside your Pivot Table to activate it.
2. Click the PivotTable Analyze tab (or Analyze tab, depending on your Excel version) on the Ribbon.
3. In the Calculations group, click Fields, Items & Sets.
4. Select Calculated Field from the dropdown menu.
5. In the Name box at the top of the dialogue, type a descriptive name such as Profit Margin.
6. In the Formula box, delete the default = 0 and enter: =(Revenue-Cost)/Revenue
7. To insert the field names correctly, you can double-click them from the Fields list below the formula box instead of typing them manually.
8. Click Add, then click OK.

A new column called “Sum of Profit Margin” will instantly appear in your Pivot Table, showing the calculated margin for each region. Because this is a Calculated Field, the formula operates on the aggregated sums, not on individual rows.

Formatting the Calculated Field as a Percentage

By default, the Calculated Field will display as a decimal number. You need to format it as a percentage for readability.

1. Right-click on any value in the new Sum of Profit Margin column.
2. Select Number Format from the context menu.
3. In the Format Cells dialogue, select Percentage from the Category list.
4. Set the decimal places to 1 or 2 as preferred.
5. Click OK.

The column will now display clean percentage values like 32.5% instead of 0.325.

Managing and Editing Existing Calculated Fields

If you need to modify the formula or delete a Calculated Field, navigate back to PivotTable Analyze, click Fields, Items & Sets, and select Calculated Field. In the dialogue, select the field name from the Name dropdown to view and edit its formula, or click Delete to remove it entirely. Remember that deleting a Calculated Field only removes it from the Pivot Table; your original source data remains completely untouched.