Page 264 - Excel Workbook for Dummies

27_798452 ch19.qxp 3/13/06 7:42 PM Page 247
Chapter 19: Generating Pivot Tables 247

8. Use the Format Report button on the PivotTable toolbar to reopen the
AutoFormat dialog box and there select the thumbnail for the Report 4 style
before you select OK.
Excel now redraws the pivot table so that its summary data now appears in a
more vertical report-type format (with the Location row field now appearing side
by side the Dept column field at the top of the report followed by the Average of
Salary column).
9. Restore the sum of the salaries to the pivot table by double-clicking the Average
of Salary cell, C4, to open the PivotTable Field dialog box and then selecting Sum
in the Summarize By list box before selecting OK.
10. Select Table 1 as the format for the pivot table.
Click the Format Report button on the PivotTable toolbar to open the AutoFormat
dialog box and then select the thumbnail for the Table 1 style before you select
OK.
11. Click the Location field’s drop-down list button in cell A5 and then click the
(Show All) check box to deselect all the check boxes for all the cities. Click the
check boxes for just Boston, Chicago, San Francisco, and Seattle before you
select OK.
The pivot table now just shows the sum of the salaries for all the departments in
just the Boston, Chicago, San Francisco, and Seattle locations.
12. Click the (Show All) item at the top of the Location field’s drop-down list to
reselect the check boxes for all the company locations on its list before you
select OK.
13. Select cell A1 on the Employee Data List worksheet and then save your changes
in a new workbook named Solved19-1.xls in your Chapter 19 folder in the My
Practice Spreadsheets folder. Close the workbook file.

Creating Pivot Tables

You use the PivotTable and PivotChart Wizard opened by choosing the Excel Data➪
PivotTable and PivotChart Report menu command. This wizard consists of the follow-
ing three dialog boxes:

Step 1 of 3, where you indicate the source of the data you want to summarize
and choose between creating a simple pivot table or a pivot chart, which repre-
sents the summary data graphically with a supporting pivot table. The data
source can be a Microsoft Excel List or Database, an External Data Source
(Chapter 16), Multiple Consolidation Ranges, or Another PivotTable, or
PivotChart Report.
Step 2 of 3, where you indicate what data you want to use in the Excel worksheet
(when specifying a Microsoft Excel List or Database, Multiple Consolidation
Ranges, or Another PivotTable or PivotChart Report as the data source), or exe-
cute an external data query that gets the data (when specifying an External Data
Source).
Step 3 of 3, where you indicate whether the pivot table should be placed in a
new worksheet or in a cell range somewhere in the current worksheet — when
generating a pivot chart, Excel places the chart on its own chart sheet and
places the support pivot table on the sheet you specify in this dialog box.

When you finish going through the options offered in the three dialog boxes of the
PivotTable and PivotChart Wizard, you end up with a new (and somewhat blank)
pivot table similar to the one shown in Figure 19-1.