Excel 2010 Bible

Chapter 35: Analyzing Data with Pivot Tables

725

The formulas used to create calculated fields and calculated items aren’t standard Excel formulas.

In other words, you don’t enter the formulas into cells. Rather, you enter these formulas in a dialog

box, and they’re stored along with the pivot table data.

The examples in this section use the worksheet table shown in Figure 35.13. The table consists of

five columns and 48 rows. Each row describes monthly sales information for a particular sales rep-

resentative. For example, Amy is a sales rep for the North region, and she sold 239 units in January

for total sales of $23,040.

FIGURE 35.13

This data demonstrates calculated fields and calculated items.

On the CD
A workbook demonstrating calculated fields and items is available on the companion CD-ROM. The file is
named calculated fields and items.xlsx.

Figure 35.14 shows a pivot table created from the data. This pivot table shows Sales (Values area),

cross-tabulated by Month (Row Labels) and by SalesRep (Column Labels).

The examples that follow create

l (^) A calculated field, to compute average sales per unit
l Four calculated items, to compute the quarterly sales commission