To create a PivotTable from an existing list, start with Excel's PivotTable Wizard. In this simple three-step process, Excel prompts you for basic details about the PivotTable you want to create, including the location of the data source and where you want the PivotTable to appear. After you finish with the wizard, you'll be able to lay out your data directly on the worksheet.
Excel 2000 introduced the capability to create and edit a PivotTable layout directly on the sheet—an enormous improvement over the same feature in Excel 97, which required that you edit PivotTable layouts in a dialog box. The Layout dialog box is still available, however, and on slow machines or with extremely large lists, you might prefer to use this technique, because it doesn't actually begin rearranging data until you click OK. To open the Layout dialog box, click the Layout button in Step 3 of the PivotTable and PivotChart Wizard.
To build a new PivotTable, open the workbook that contains the list on which you plan to base the PivotTable. Then follow these steps:
Click anywhere in your list. To build a PivotTable from a subset of the data in your list, select the range that contains the data.
Choose Data, PivotTable and PivotChart Report. The PivotTable Wizard appears, as shown in Figure 27.6.
The wizard asks you to specify the range in which your data is located. The default selection is your current list, or any range you selected before starting the wizard. Adjust the selection, if necessary, and click Next.
If the wizard starts in Step 3 instead of Step 1, see "Adding Extra PivotTables"in the "Troubleshooting" section at the end of this chapter.
In its final step, the wizard asks you where you want to place the PivotTable. Choose the default option, New Worksheet.
Caution
The PivotTable Wizard offers the option to place a PivotTable or PivotChart on an existing worksheet. In general, you should always choose to place a PivotTable on its own sheet. Adding a PivotTable to a sheet that contains data exposes you to the risk that changes you make to the list design will affect your PivotTable, or vice versa.
Click Finish to close the wizard and create a blank PivotTable page. Excel jumps to the new worksheet you just created and displays the PivotTable toolbar.
Tip from
If you're uncertain about exactly where to drop field buttons, watch the screen for two important clues. As the mouse pointer passes over each region of the PivotTable, Excel displays informative ScreenTips. When dragging fields around, watch the mouse pointer—it changes shape to match the PivotTable layout, and a blue highlight in the pointer shows which of the four regions (row, column, data, or page) is under the pointer at any given moment.
Don't be surprised if the PivotTable doesn't display properly at first. In particular, summary fields in the data area default to the SUM function. If you want to use COUNT, AVERAGE, or another summary function instead, see the next section.
18.116.63.5