Pivot Table Calculated Fields

Modified on Sun, 26 Jul at 4:14 AM

Overview

Excel provides a Calculated Field feature for adding a calculated value to a PivotTable.

The calculation remains connected to the PivotTable. If the number of displayed rows changes after a refresh, Excel continues to apply the calculation to the appropriate PivotTable results.

Calculated Field Limitations

Calculated Fields are useful for simple numeric calculations, but they have important limitations.

Text Fields Are Treated as Zero

Excel treats a text field as 0 inside a PivotTable Calculated Field, even when the text appears to contain a number.

Calculations Use Summarized Values

A PivotTable Calculated Field works with the summarized value of each field, not with an individual source-row value.

Even when a formula appears to reference one item in a column, Excel evaluates the summarized PivotTable field.

Example of a Formula That Will Not Work

Consider an IF formula intended to perform the following test:

If the account number begins with 05, return the value from Column C; otherwise, return 0.00.

A standard PivotTable Calculated Field does not evaluate the individual account number. Excel instead evaluates the summarized Account Number field, which prevents this type of row-level IF statement from working correctly.

Use PowerPivot for Row-Level Logic

Use a PowerPivot model when the calculation requires row-level conditions, filtered subsets, or IF logic based on individual records. PowerPivot uses DAX for these calculations.

When a Calculated Field Is Appropriate

A PivotTable Calculated Field works well for straightforward arithmetic between numeric fields, such as:

Amount divided by Quantity.
Sales minus Cost.
Quantity multiplied by Rate.
One summarized numeric field expressed as a percentage of another.

Create a Calculated Field

1. Select the PivotTable

Click anywhere inside the PivotTable so the PivotTable Tools ribbons appear.

2. Open the Calculated Field Command

Select Fields, Items, & Sets > Calculated Field.

Excel PivotTable Fields Items and Sets menu showing Calculated Field

3. Build the Formula

In the Calculated Field dialog box:

Enter a name for the calculated field.
Insert the required numeric fields.
Enter the appropriate mathematical operators.
Select Add.
Select OK.

Excel Insert Calculated Field dialog box

Required Fields Must Already Be Selected

Every field used in the formula must already be included in the PivotTable.

4. Review the Result

The new calculated field appears in the PivotTable as an additional Values field.

Excel PivotTable showing the completed calculated field

Expected Result

The calculated field remains part of the PivotTable and adjusts automatically as the PivotTable expands, contracts, or refreshes.

Use Calculated Fields for simple arithmetic between numeric fields. Use PowerPivot and DAX when the calculation requires row-level conditions or filtered subsets.