Page 263 - Excel Workbook for Dummies
P. 263
27_798452 ch19.qxp 3/13/06 7:42 PM Page 246
246 Part V: Doing Data Analysis
workbook contains a Employee Data List worksheet with a copy of the Employee data
list you worked with in Chapter 16, followed by a Data List Pivot Table worksheet con-
taining a pivot table generated from the this data list. You will use this pivot table to
practice modifying what data from the Employee Data List are displayed in the table
as well as the manner in which they are displayed:
1. Click the Data List Pivot Table sheet tab to display the pivot table generated with
data from the Employee Data List along with the PivotTable toolbar.
The Employee List pivot table that appears on this worksheet uses the following
field from the Employee Data List:
• Profit Sharing and Gender fields as the two page fields (in cells B1 and B2,
respectively)
• Location field as the sole row field (in cell A5)
• Dept field as the sole column field (in cell B4)
• Salary field for the data items in the body of the pivot table (in the cell
range B6:G13) using the SUM function (A4)
Note that the PivotTable toolbar appears automatically whenever you position
the cell cursor in any cell of the pivot table and disappears just as automatically
when you move the cursor to a cell outside the pivot table.
2. Click the Show Field List button at the very end of the PivotTable toolbar.
Excel displays the PivotTable Field List dialog box indicating which fields from
the data list are used in the table in boldfaced type.
3. Click the drop-down button attached to the Profit Sharing page field in cell B1
and then select Yes on its drop-down list to display the sum of the salaries in
the various departments and locations where the employees participate in the
profit-sharing plan.
You use the drop-down buttons attached to the page, row, and column fields to
select the various filtering criteria for the data displayed in the body of the pivot
table.
4. Click the drop-down button attached to the Gender page field in cell B2 and then
select M on its drop-down list.
Now the body of the pivot table displays the sum of the salaries in the various
departments and locations only where the male employees participate in the
profit-sharing plan.
5. Restore the (All) settings to both the Profit Sharing and Gender page fields by
selecting (All) on their respective drop-down lists and then double-click Sum of
Salary in cell A4 to open the PivotTable Field dialog box.
6. Click Average in the Summarize By list box so that the Name text box contains
Average of Salary before you select OK.
The pivot table now displays the averages of the salaries for all the departments
and locations in the company regardless of whether the employees are part of
profit sharing or are male or female.
7. Click the Format Report button on the PivotTable toolbar (the one immediately
to the right of the first PivotTable drop-down button) and then select the thumb-
nail for the Table 10 style in the AutoFormat dialog box before you select OK.
This table format makes the cells in the pivot table with the department names
and departmental salary totals really stand out with their black background and
gold lettering.