Microsoft Office Tutorials and References

In Depth Information

**Creating a Calculated Field or Calculated Item**

This example used Excel’s Grouping dialog box to create the groups automatically. If

you don’t want to group in equal-sized bins, you can create your own groups. For

example, you may want to assign letter grades based on the test score. Select the rows

for the first group and then select Group from the shortcut menu. Repeat these steps

for each additional group. Then replace the default group names with more meaningful

names.

Creating a Calculated Field or Calculated Item

Perhaps the most confusing aspect of pivot tables is calculated fields versus calculated items.

Many pivot table users simply avoid dealing with calculated fields and items. However, these

features can be useful, and they really aren’t that complicated after you understand how they work.

First, some basic definitions:

h
Calculated field:
A calculated field is a new field created from other fields in the pivot

table. If your pivot table source is a worksheet table, an alternative to using a calculated

field is to add a new column to the table and then create a formula to perform the

desired calculation. A calculated field must reside in the Values area of the pivot table.

You can’t use a calculated field in Column Labels, Row Labels, or a Report Filter.

h
Calculated item:
A calculated item uses the contents of other items within a field of the

pivot table. If your pivot table source is a worksheet table, an alternative to using a

calculated item is to insert one or more rows and write formulas that use values in other rows.

A calculated item must reside in the Column Labels, Row Labels, or Report Filter area of a

pivot table. You can’t use a calculated item in the Values area.

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 18-31. The table consists of

five fields and 48 rows. Each row 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 of $23,040.

A workbook that demonstrates calculated fields and items is available on the

companion CD-ROM. The file is named
calculated fields and items.xlsx
.

Figure 18-32 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).