Page 269 - Excel Workbook for Dummies

27_798452 ch19.qxp 3/13/06 7:42 PM Page 252
252 Part V: Doing Data Analysis
Note that you change how the fields function in the pivot table by dragging them
to new positions in the table. To remove a field from the pivot table, drag it to a
cell outside the confines of the table. When you release the mouse button, Excel
deletes the field from the table (but not the PivotTable Field List in case you
need to add it again somewhere).
2. Drag the Gender field down and drop it on cell C4 to the immediate right of the
Location column field to convert it from a page field to a second column field
and then select M as the sole criteria to use in displaying the data.
Click the Gender field’s drop-down button, remove the check mark from the (All)
item, and then put a check mark in the M check box before you select OK.
Note that when the Gender field is added to the column fields, it acts like a
subtotaling field so that the pivot table can now display columns with subtotals
of the men’s and women’s salaries as well as the one with their grand total.
3. Add the Years of Service field as a second row field in the pivot table.
In the PivotTable Field List dialog box, click Row Area in the drop-down list, and
then click the Years of Service field in the list before you select the Add To
button.
Adding Years of Service as a second row field in the pivot table adds another
level of subtotals, this time across the rows of the table.
4. Set the criteria of the Gender column field to (Show All) so that pivot table con-
tains the salaries totals for both the men and women.
5. Right-click cell B6 and then select Hide on the shortcut menu.
Hiding a field is another method for removing it from the pivot table because the
only way to redisplay the field is to add it from the PivotTable Field List dialog
box to the pivot table again.
6. Hide the Gender field in the columns of the pivot table and then add it back to
the table as a second page field.
7. Position the cell cursor in cell A1 of the Pivot Table worksheet and then
save your modifications to the pivot table in a new workbook file named
Solved19-4.xls in your Chapter 19 folder in the My Practice Spreadsheets folder.
Leave the workbook file open for Exercise 19-5.

Changing the table summary function
and adding calculated fields

By default, Excel uses the SUM function to total the values in the numeric field(s) that
you assign as the data items in the pivot table. Some data summaries require the use
of another summary function such as the AVERAGE or COUNT function. To change
the summary function, double-click the label of the field used as a data item or click
this label (this label is located at the cell intersection of the first column and row field
in a pivot table that has only one data field and uses the default or classic table
format). Then, select Field Settings on the PivotTable drop-down list on the
PivotTable toolbar to open its PivotTable Field dialog box, where you can select any
of the following summary functions in the Summarize By list box:

Count to show the count of the records for a particular category (note that
Count is the default setting for any text fields that you use as Data Items in a
pivot table)
Average to calculate the average (that is, the arithmetic mean) for the values in
the field for the current category and page filter