Calculated Field/Item

 

Calculated Field    |   Calculated Item

This example teaches you how to insert a calculated field or calculated item in a pivot table.

Below you can find a pivot table. Go back to Pivot Tables to learn how to create this pivot table.

Pivot Table

Calculated Field

A calculated field uses the values from another field. To insert a calculated field, execute the following steps.

1. Click any cell inside the pivot table.

2. The PivotTable Tools contextual tab activates. On the Options tab, click Calculated Field.

Click Calculated Field

3. Enter Tax for Name.

4. Type the formula =IF(Amount>100000, 3%*Amount, 0)

5. Click Add.

Insert Calculated Field

Note: use the Insert Field button to quickly insert fields when you type a formula. To delete a calculated field, select the field and click Delete (under Add).

6. Click OK.

7. Drag the Tax field to the Values area.

Result:

Calculated Field

Calculated Item

A calculated item uses the values from other items. To insert a calculated item, execute the following steps.

1. Click any Country in the pivot table.

2. The PivotTable Tools contextual tab activates. On the Options tab, click Calculated Item.

Click Calculated Item

3. Enter Oceania for Name.

4. Type the formula =3%*(Australia+'New Zealand')

5. Click Add.

Insert Calculated Item

Note: use the Insert Item button to quickly insert items when you type a formula. To delete a calculated item, select the item and click Delete (under Add).

6. Repeat steps 3 to 5 for North America (Canada and United States) and Europe (France, Germany and United Kingdom) with a 4% and 5% tax rate respectively.

7. Click OK.

Result:

Calculated Items

Note: we created two groups (Sales and Taxes).

Did you find this information helpful? Show your appreciation, vote for us.

Go to Top: Calculated Field/Item    |    Go to Next Example: Structured References