Open In App

How to Create Excel Pivot Table Calculated Field with Examples

Improve
Improve
Like Article
Like
Save
Share
Report

You can add calculated fields to a pivot table using your own unique algorithms that add up to other pivot fields. A calculated field has some restrictions, but it gives the pivot tables in your Excel worksheet a strong tool.

What is a Pivot Table Calculated Field in Excel

“Pivot Table calculated field” is an option to include more data or calculations in the pivot table. Often, we need to add our own custom calculations that can refer to other fields in the data set for our reports.  

In the below examples, we create a sales report by-product with an additional column “Sales Differences” [2020_Sales – 2019_Sales].  The calculated field “Sales Differences” calculation refers to other fields in the dataset [subtract 2019_Sales from 2020_Sales].

Example: We have a data set in four columns “Product”, ”Customer”, “2019_Sales” and “2020_Sales” as below

data set

How to Create a Pivot Table in Excel

Follow the below steps to create a pivot table

Step 1: Select Insert >> Pivot >> From Table/Range (Img1)  to pop-up “Pivot Table from table or range” dialogue box (Img 2).

Creating a Pivot Table

Img 1

PibotTable from table or range dialogue box

 

Step 2: Enter your data set range in “Table/Range” input, choose New worksheet, and Click “OK

Creating a Pivot Table

Step 3: Above step, add a new sheet with a pivot table for your data set.

Pivot Table

Pivot Table

Step 4: Drag the field “Products” to the Rows pane

Creating a Pivot Table

Step 5: Drag 2019_Sales and 2020_Sales to Values pane.

Pivot Table fields

How to Add Calculated Field

You created a pivot table for your dataset.  Now we add a calculated field to display the sales_differences in the pivot table

Step 6. To pop up the “Insert Calculated Field” dialogue box.  Click anywhere in the pivot table. Goto PivotTable Analyze in Ribbon >> Fields, Items & Sets >> Calculated Field…

Adding Calculated Field

Step 7. Above step pop-up below dialogue box and type “Sales_Differences” [Calculated field Name] in the Name input box

Insert Calculated Field dialogue box

Step 8. Select “2020_Sales” from the fields list and Click Insert Field.

Insert Field dialogue box

Step 9. Just type “-“ in Formula (for Subtraction).

Step 10. Select “2019_Sales” from the fields list and Click Insert Field.

Insert Calculated Field dialogue box

Step 11. Please make sure your formula is like below

='2020_Sales'-'2019_Sales'  

Step 12: Finally click Add then OK, you can see the pivot with additional columns “Sales_Differences” [Calculated Field] as in the below output

Adding Calculated field

Output

Output

About Calculated Fields

  • For calculated fields, the sum of the individual values in the other value fields is used before doing the computation on the sum.
  • All pivot tables based on the same pivot cache have calculated fields exposed by default.

What are Limitations of Calculated Fields

  • Calculated field formulas cannot use the address or name of worksheet cells
  • The pivot table totals or subtotals cannot be referenced in calculated field calculations

How to Modify or Delete a Pivot Table Calculated Field?

Following the procedures below, you can change or remove the formula in a Pivot Table Calculated Field:

Step 1. Select any cell in the Pivot Table

Step 2.  Select Fields, Items, and Sets under Calculations from the Pivot Table Tools → Analyze 

Analyze Tab

Analyze Tab

Step 3. Select Calculated Field

Step 4. Click on the drop-down arrow, In the Name field to select the calculated field you want to delete or modify

Insert Calculated Field dialogue box

Insert Calculated Field

Modify or Delete Calculated Field

Modify or Delete Calculated Field

FAQs on Excel Pivot Table Calculated Field

1. What is the difference between calculated item and calculated field?

  • Calculated Fields are formulas that can refer to other fields in the pivot table.
  • Calculated Items are formulas that can make references to other items inside a certain pivot field.
     


Last Updated : 06 Dec, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads