How to Create Calculated Field in PivotTable

PivotTable report allows you to perform calculations on summary statistics generated in PivotTable. You can perform calculations directly on PivotTable and can add new Field (s) for the calculated values. For example you have a list of marketing executives and you want to calculate incentives for them. Incentives are paid on sales revenue that they generate. Similarly, you are selling products at discount price, you want to calculate discount amount and offer price. In such situations, PivotTable Calculated Field would be very helpful.

Steps to Create Calculated Field

First create a PivotTable using UsedCarsPriceList.xlsx from Menu bar click Insert tab, then click PivotTable (PivotTable dialogue box opens), check whether source data is properly selected, choose New Worksheet for PivotTable report, and click OK. A Skelton PivotTable appears on screen.Now drag Make to Row Labels, and Price to Values. PivotTable is filled with data.

Next click Formulas button and then click Calculated Field from popup menu. Insert Calculated Field dialogue box appears on screen.Now, in the Name box enter the name that you wish to give to Calculated Field. For this example give name as Discount.

Next from Formulas box delete “= 0” and from Fields select Price and click Insert Field tab. =Price appears in formula bar. After that enter the formula here. Assume that you want to give a discount of 10%. In formula bar enter *0.10 (10% denoted in decimal value) next to Price. PivotTable now shows the Discount field.

To calculate the offer price, once again click Formulas button and then click Calculated Field from popup menu. Insert Calculated Field dialogue box appears on screen.Now, in the Name box enter Offer Price.

Next from Fields select Price and click Insert Field tab, =Price appears in formula bar. Next enter Minus (-) symbol, then from Fields select Discount and click Insert Field tab. The formula bar will have =Price – Discount

Click OK, PivotTable now depicts Offer Price Field.