Excel 2019 Bible

Chapter 30: Analyzing Data with PivotTables

3030

The examples that follow create

■ A calculated field, to compute average sales per unit

■ (^) Four calculated items, to compute the quarterly sales commission
Creating a calculated field
Because a PivotTable is a special type of range, you can’t insert new rows or columns within
the PivotTable, which means you can’t insert formulas to perform calculations with the
data in a PivotTable. However, you can create calculated fields for a PivotTable. A calculated
field consists of a calculation that can involve other fields.
A calculated field is basically a way to display new information (derived from other fields)
in a PivotTable. It’s an alternative to creating a new column in your source data. In many
cases, you may find it easier to insert a new column in the source range with a formula that
performs the desired calculation. A calculated field is most useful when the data comes
from a source that you can’t easily manipulate, such as an external database.
In the sales example, suppose you want to calculate the average sales amount per unit. You
can compute this value by dividing the Sales field by the Units Sold field. The result shows
a new field (a calculated field) for the PivotTable.
Use the following procedure to create a calculated field that consists of the Sales field
divided by the Units Sold field:

1. Select any cell within the PivotTable.


2. Choose PivotTable Tools Analyze ➪ Calculations ➪ Fields, Items, & Sets ➪
   Calculated Field. The Insert Calculated Field dialog box appears.


3. Enter a descriptive name in the Name box, and specify the formula in the
   Formula box (see Figure 30.15). The formula can use worksheet functions and
   other fields from the data source. For this example, the calculated field name is
   Average Unit Price, and the formula is as follows:
   =Sales/'Units Sold'


4. Click Add to add this new field.


5. Click OK to close the Insert Calculated Field dialog box.

You can create the formula manually by typing it or by double-clicking items in the Fields list box. Double-clicking an

item transfers it to the Formula field. Because the Units Sold field contains a space, Excel adds single quotes around

the field name.

After you create the calculated field, Excel adds it to the Values area of the PivotTable. (It

also appears in the PivotTable Fields task pane.) You can treat it just like any other field,

with one exception: you can’t move it to the Rows, Columns, or Filters areas. It must remain

in the Values area.