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.
   258   259   260   261   262   263   264   265   266   267   268