Discover How To Calculate Fields In A Pivot Table With This Excel Tutorial

See all Microsof Excel tips

Strategy:

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

1) Build a pivot table.

2) From the PivotTable toolbar, select PivotTable – Formula – Calculated Field, as shown in Fig. 981.

3) Type a name for the field, in this case, Profit. Enter the calculation =

R e v e n u e – COGS. Choose the Add button, as shown in Fig. 982.

4) Here is the formula for Gross Profit Percent. See Fig. 983.

5) The formula for Average Price is shown in Fig. 984.

The resulting pivot table includes all of the fields, as shown in Fig. 985.

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. If you actually wanted to have Excel do all of the individual average prices and then sum them up, it would be impossible in a pivot table.

Summary:

Calculated Fields add a new measure that can be reported in the Data area of a PivotTable for all Measures.

Commands Discussed:

PivotTable – Formula – Calculated Field

 

See all Microsoft Excel tips

Images

Fig. 980
Fig. 981
Fig. 982
Fig. 983
Fig. 984
Fig. 985