# Excel: Calculated Fields in a Pivot Table

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I need to include in a pivot table a calculation that is not in my underlying data. My data includes quantity sold, revenue, and cost. I would like to report gross profit and average price.

Strategy: You can add a calculated field to a pivot table. Follow these steps:

1. Build a pivot table with Product and Revenue columns.
2. The Calculated Field command moved between versions. In both versions, it is found in a dropdown on the Options ribbon tab. In Excel 2007, it is under the Formulas menu. In Excel 2010, it is under the Fields, Items, and Sets menu.

1. Choose Calculated Field.
1. In the Insert Calculated Field dialog, type a field name such as Profit in the Name text box. In the Formula text box, type an equals sign. Double-click the Revenue entry in the Fields list. Type a minus sign. Double-click the COGS entry in the Fields List. The Formula text box should say =Revenue-COGS. Click the Add button to accept this formula.

1. Add the following formula for GPPct: =Profit/Revenue.
2. Add the following formula for AveragePrice: =Revenue/Quantity.
3. Click OK to close the Insert Calculated Field dialog box.

Results: The resulting pivot table will include all the fields.

3. Excel adds the new fields to the pivot table.

Gotcha: The label Sum of GPPct is somewhat misleading, as is Sum of Average Price. In reality, Excel finds the sum of Revenue, finds the sum of Quantity, and then divides the values on the total line in order to get the average price. This makes calculated fields fine for any calculations that follow the associative law of mathematics. Having Excel do all the individual average prices and then sum them up would be impossible in a pivot table unless you are using PowerPivot.