Calculated Field and Calculated Item in Pivot Table
This example shows how to add a calculated field or item in a pivot table. Below you can find a pivot table. Return to Pivot Tables to review how to create this pivot table.
💎 Using Calculated Field in Pivot Table
A calculated field gets its value from the data in another field. To add a calculated field, follow these steps.
1. Click any cell inside the pivot table.
2. Click the PivotTable Analyze tab. Within the Calculations group, select Fields, Items & Sets.
3. Click Calculated Field.
The Insert Calculated Field dialog box appears.
4. Enter Tax for Name.
5. Type the formula =IF(Amount>100000, 3%*Amount, 0)
6. Click Add.
Note: Click the Insert Field button to add a field while typing a formula. To delete a calculated field, first select the field, then click Delete located under the Add option.
7. Click OK.
Excel automatically includes the Tax field in the Values section.
Result:
💎 Calculated Item in Pivot Table
A calculated item depends on the values of other items to calculate its value. To add a calculated item, follow these steps.
1. Click any Country in the pivot table.
2. Click the PivotTable Analyze tab. Within the Calculations group, select Fields, Items & Sets.
3. Click Calculated Item.
The Insert Calculated Item dialog box appears.
4. Enter Oceania for Name.
5. Type the formula =3%*(Australia+’New Zealand’)
6. Click Add.
Note: To insert a field while creating a formula, click the Insert Field button. To remove a calculated item, select it and click Delete located under Add.
7. Apply steps 4 to 6 again for North America (Canada and USA) with a 4% tax, and for Europe (France, Germany, and the UK) with a 5% tax.
8. Click OK.
Result:
Note: we created two groups (Sales and Taxes).
8/9 Completed! Learn much more about pivot tables ➝
Next Chapter: Tables