Chapter 30: Analyzing Data with PivotTables
3030
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 meaningful names.
Creating a Calculated Field or Calculated Item
Perhaps the most confusing aspect of PivotTables is calculated fields versus calculated
items. Many PivotTable 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 the way they work.First, here are some basic definitions:Calculated field A new field created from other fields in the PivotTable. If your PivotTable
source is a worksheet table, an alternative to using a calculated field is to add a new col-
umn to the table and create a formula to perform the desired calculation. A calculated
field must reside in the Values area of the PivotTable. You can’t use a calculated field in the
Columns area, in the Rows area, or in the Filter area.
Calculated item Uses the contents of other items within a field of the PivotTable. If your
PivotTable 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 Columns area, Rows area, or Filters area of a PivotTable. You can’t use a calcu-
lated 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 into a dialog box, and they’re stored along with the PivotTable data.The examples in this section use the worksheet table shown in Figure 30.13. The table con-
sists of 5 columns and 48 rows. Each row describes monthly sales information for a particu-
lar 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 demonstrating calculated fields and items is available on this book’s website at http://www.wiley.com/
go/excel2019bible. The file is named calculated fields and items.xlsx.
Figure 30.14 shows a PivotTable created from the data. This PivotTable shows Sales (Values
area), cross-tabulated by Month (Rows area) and by SalesRep (Columns area).