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

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

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.

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

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

Fig. 982

Fig. 983

Fig. 984

Fig. 985