Leading the way in Microsoft Office Development

Excel

What is a Calculated Field ?

It is possible to add additional fields to your pivot table which are actually functions of existing fields.

You can create an additional field in your pivot table which can be based on other fields in your pivot table.

(PivotTable > Formulas > Calculated Field).

Type in the name of the field

You can use the normal operators to calculate your field.

Creating a Calculated Field

This dialog box lets you modify and delete and customised fields.

 (PivotTable > Formulas > Calculated Field) dialog box

You can use any of the standard Excel formula options on this dialog box.

It includes all the normal mathematical operators including all the worksheet functions.

The arguments used in your formula must match the field names in your pivot table.

You cannot use any cell references or range names in your formulas.

Each calculated field that you add displays as an additional Data Field.

These will also automatically be added to the Field List Pane and the Layout dialog box.

Removing a Calculated Field

To remove a calculated field, find the name in the drop-down list and press Delete.

If you have other calculated fields that referred to a calculated field that has been deleted then #NAME? will be displayed in your data area.

Things to Remember

The arguments used in your formula must match the field names in your pivot table.

You can turn off the field calculation by double clicking on the field and set it to None.

Calculated Fields can only be used in the Data Area of your pivot table.