Excel: Calculated Fields in a Pivot Table

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

   

2. Add a new formula.
   4. Add the following formula for GPPct: =Profit/Revenue.
   5. Add the following formula for AveragePrice: =Revenue/Quantity.
   6. 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.

   You can rename the fields that have misleading headings. Simply click on the heading and type a new heading.

   Gotcha: It is possible to use an Excel function in the Insert Calculated Field dialog. However, the function is applied to individual rows instead of using the population of matching rows. In the figure below, column I contains a calculated field of MEDIAN(Score). To calculate this, Excel takes the MEDIAN of cell B2. Of course, the median of B2 is the value from B2. They repeat this for each cell, then sum up the results. This is not a median at all. It is the same as the sum of the cells. To truly calculate a median, you will need PowerPivot.

   

4. Using functions in calculated fields may not work as you want.