Page 270 - Excel Workbook for Dummies

27_798452 ch19.qxp 3/13/06 7:42 PM Page 253
Chapter 19: Generating Pivot Tables 253
Max to display the largest numeric value in that field for the current category
and page filter
Min to display the smallest numeric value in that field for the current category
and page filter
Product to display the product of the numeric values in that field for the current
category and page filter (all non-numeric entries are ignored)
Count Nums to display the number of numeric values in that field for the current
category and page filter (all non-numeric entries are ignored)
StdDev to display the standard deviation for the sample in that field for the cur-
rent category and page filter
StdDevp to display the standard deviation for the population in that field for the
current category and page filter
Var to display the variance for the sample in that field for the current category
and page filter

Varp to display the variance for the population in that field for the current cate-
gory and page filter

After you select the new summary function to use in the Summarize By list box of its
PivotTable Field dialog box, click the OK button to have Excel apply the new function
to the data presented in the body of the pivot table.
In addition to using various summary functions on the data presented in your pivot
table, you can create your own calculated fields for the pivot table. Calculated fields
are computed by assigning a formula using existing numeric fields in the data source.

After you finish defining a calculated field to a pivot table, Excel automatically adds
its name to the PivotTable Field List dialog box and assigns it as a data item in the
data area of the pivot table. The program also adds a new data field and makes it the
first column field in the pivot table.

If you want to hide a calculated field from the body of the pivot table, click the data
field’s drop-down button. Click the name of the calculated field to remove the check
mark from its check box before you click the menu’s OK button. To add the calculated
field back into the pivot table, click its field name in the PivotTable Field List dialog
box (by clicking the Show Field List button on the PivotTable toolbar), and then
select Data Area in the drop-down list box at the bottom of the dialog box before you
select the Add To button.

Try It

Exercise 19-5: Modifying the Summary Functions and Adding a Calculated
Field to a Pivot Table
Use the Solved19-4.xls worksheet with the modified pivot table you created in the pre-
vious exercise to practice modifying the summary function as well as to add a calcu-
lated field to your pivot table:
1. Double-click the Sum of Salary cell, A4, in the Pivot Table worksheet to open its
PivotTable Field dialog box and then select Average in the Summarize By list box
before you select OK.
The body of your pivot table now displays the average salary for each location
and department in the company.