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
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.