Excel 2003 Creating Pivot Tables
To create a pivot table for a data list, open the worksheet with the list and then choose Data → Pivot Table and PivotChart Report. The PivotTable and PivotChart Wizard launches, which contains the following three dialog boxes:
- Step 1 of 3 is where you indicate the source of the data that you want to summarize as well as choose between creating a simple pivot table or a pivot chart, which represents the summary data graphically with a supporting pivot table. The data source options here are Microsoft Excel List or Database, an External Data Source, Multiple Consolidation Ranges, or Another PivotTable or PivotChart Report.
- Step 2 of 3 is where you indicate the data - including field names - that 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 execute an external data query that gets the data (when specifying an External Data Source).
- Step 3 of 3 is where you indicate whether to place the pivot table 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 that you specify.
After you finish going through the options offered in the three dialog boxes of the PivotTable and PivotChart Wizard, you end up with a blank pivot table.
This new pivot table contains a blank framework with the various areas of the pivot table identified in light gray type. Excel opens a floating PivotTable Field List task pane that contains a complete list of the field names in your data source. You use these field names to bring the blank pivot table to life. In addition to the PivotTable Field List task pane, Excel also displays a floating PivotTable toolbar in the Excel window.
The key to completing the new pivot table is to assign the fields in the Field List task pane to the various parts of the table. You can do this job one of two ways: by dragging a field name from the task pane and then dropping it on a particular part of the pivot table; or by selecting the field name in the Field List task pane, selecting the part of the table to which to attach the field from the task pane's dropdown list, and then clicking the Add To button.
Before you begin this procedure, however, you need to understand the use and significance of the various areas of a pivot table:
- Drop Page Fields Here: This area contains the fields that enable you to page through the data summaries shown in the actual pivot table by filtering out sets of data. For example, if you designate the Date Hired field from the sample Employee data list as a Page Field, you can display data summaries in the pivot table for individual years entered into this field or for all years it contains.
- Drop Column Fields Here: This area contains the fields that determine the arrangement of data shown in the columns of the pivot table.
- Drop Row Fields Here: This area contains the fields that determine the arrangement of data shown in the rows of the pivot table.
- Drop Data Items Here: This area contains the fields that determine which data are presented in the cells of the pivot table and then summarized in its last column (totaled by default).
