In this chapter you will learn how to use Calculated Field and Calculated Item in a pivot table.
1.Calculated Field:
In a pivot table you can add your own value field based on the available value fields calculation. In our data we have Sales and Revenue but we do not have “Revenue per sale”. we can add field in calculated field.
To add a Calculated Field–
- Click anywhere in the pivot table.
- Go to Analyze Tab>>Click on Field, Items & Sets available in Calculation group>>Click on Calculated Field
- Insert Calculated Field window will be opened.
- Put “Revenue Per Sale” in Name box.
- Put “=Revenue/Sales” in Formula box.
- Click on OK
Note: Always insert a field in Formula box from Field list( Available below the the formula box) to prevent any error.
A Calculated Field(“Revenue Per Sale”) has been added. Now the pivot will look like below-
2.Calculated Item:
Now if we want to added a item in Row Labels like Product(1+2) then we have to add a calculated item in the pivot table.
To add a Calculated Item–
- Click on any Product (Row Label item) available in the Row Labels of the pivot table.
- Go to Analyze Tab>>Click on Field, Items & Sets available in Calculation group>>Click on Calculated Item
- Insert Calculated item window will be opened.
- Put “Product(1+2)” in Name box.
- Put “=’Product – 1′ +’Product – 2′” in Formula box.
- Click on OK
Note: Always insert a item in Formula box from Items list( Available below the the formula box) to prevent any error.
In new item (“Product 1 + 2”) is added in Row Labels.
List Formulas:
List formulas option is used to see the list of calculated fields/Items along with their calculations.
To list the calculated fields/Items along with their calculations-
- Click anywhere on the Pivot Table.
- Go to Analyze Tab>>Click on Field, Items & Sets available in Calculation group>>Click on List Formulas
A new worksheet will be created, wherein all the calculated fields/Items along with their calculations will be available.
