Pivot table calculated field example

Standard Pivot Tables have a simple feature for creating calculated fields. You can think of a calculated field as a virtual column in the source data. A calculated field will appear in the field list window, but will not take up space in the source data. In the example shown, a calculated field called "Unit Price" has been created with a formula that divides Sales by Quantity. The pivot table displays the calculated unit price for each product in the source data.

Note: data ends on row 18, so the calculation is as follows: $1,006.75 / 739 = $1.36

Fields

The source data contains three fields, Product, Quantity, and Sales. A fourth field called "Unit Price" is a calculated field.

The calculated field was created by selecting "Insert Calculated Field" in the "Fields, Items, and Sets" menu on the ribbon:

The calculated field is named "Unit Price" and defined with the formula "=Sales/Quantity" as seen below:

Note: Field names with spaces must be wrapped in single quotes ('). Excel will add these automatically when you click the Insert Field button, or double-click a field in the list.

The Unit Price field is renamed "Unit Price " (note the extra space) after it has been added to the Values area:

The extra space is required because Excel won't allow you to use exactly the same field name that appears in the data in a pivot table.

Steps

1. Create a pivot table
2. Create the Calculated field "Unit Price"
3. Add Unit Price to field to Values area
   1. Rename field "Unit Price "
   2. Set number format as desired