Excel 2010 Bible

Part V: Analyzing Data with Excel

724

FIGURE 35.12

The pivot table and pivot chart show the frequency distribution for the test scores.

Note
This example uses the Excel 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, right-click, and then choose Group from the shortcut
menu. Repeat these steps for each additional group. Then replace the default group names with more meaning-
ful names. n

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 once you understand how they work.

First, some basic definitions:

l A calculated field: 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 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

the Column Labels, in the Row Labels, or in a Report Filter.

l (^) 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.