Creating a Calculated Field or
Once you create a pivot table, you can create two types of formulas for further
A calculated field: A new field created from other fields in the pivot table.
A calculated field must reside in the Data area of the pivot table (you
can’t use a calculated field in the Page, Row, or Column areas).
A calculated item: A calculated item uses the contents of other items
within a field of the pivot table. A calculated item must reside in the Page,
Row, or Column area of a pivot table (you can’t use a calculated item in
the Data area).
The formulas used to create calculated fields and calculated items are not stan-
dard Excel formulas. In other words, you do not enter the formulas into cells.
Rather, you enter these formulas in a dialog box, and they are stored along with the
pivot table data.
Beginning with Excel 2000,you can use an OLAP database as the source for
a pivot table.You can’t, however, create calculated fields or items in a pivot
table based on an OLAP database.
The examples in this section use the worksheet database table shown in Figure
18-17. The table consists of five fields and 48 records. Each record describes
monthly sales information for a particular sales representative. For example, Amy
is a sales rep for the North region, and she sold 239 units in January for total sales
Figure 18-18 shows the basic pivot table created from the data. This pivot table
shows sales, broken down by month and sales rep.
The examples that follow will create:
A calculated field, to compute average sales per unit
A calculated item, to summarize the data by quarters
Part V: Miscellaneous Formula Techniques