Excel Pivot Table Calculated Field
“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:
.png)
