Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Excel Pivot Table Calculated Field

  • Last Updated : 30 Nov, 2021

“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].  Calculated field “Sales Differences” calculation refers to other fields in the dataset [subtract 2019_Sales from 2020_Sales].

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

Create a PIVOT TABLE:

Follow the below steps to create a pivot table:

Step 1: Select Insert >> Pivot >> From Table/Range (Img1)  to popup “PivotTable from table or range” dialog box (Img 2).

Img 1

Img 2

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

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

Pivot Table

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

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



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 popup the “Insert Calculated Field” dialog box.  Click anywhere in the pivot table. Goto PivotTable Analyze in Ribbon >> Fields, Items & Sets >> Calculated Field…

Step 7: Above step popup below dialog box and type “Sales_Differences” [Calculated field Name] in the Name input box

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

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

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

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

Output:

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!