Creating a PivotTable

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:

  1. 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.

  2. Choose Data, PivotTable and PivotChart Report. The PivotTable Wizard appears, as shown in Figure 27.6.

    Figure 27.6. If you don't select a range first, the PivotTable Wizard assumes you want to base the new PivotTable on the entire list. Change the selection, if necessary, in the next step.

  3. Specify the location of your data—typically an Excel list. If you choose the Multiple Consolidation Ranges option, Excel lets you select a group of data ranges from one or more worksheets. Click Next to move on.

Using External Databases with PivotTables

In a corporate setting, it's often useful to base a PivotTable on the result of a query to an external database. If you choose the External Data Source option, Excel starts the Query Wizard and prompts you for details about the format and location of the database. Excel then uses this query as the source for the PivotTable or PivotChart. Each time you refresh the data in the PivotTable, Excel runs the saved query and updates the PivotTable with the most recent information.

Excel 2000 and 2002 also offer the option to build PivotTables from special data structures called On-Line Analytical Processing (OLAP) databases. Instead of rows and columns, these files organize data into dimensions and levels. Instead of forcing Excel to chug through massive amounts of data, the server does the summarizing first and sends the summary values directly to your report.

When you connect to an OLAP database, Excel lets you save your data in local files called OLAP cubes and use them as the source for a PivotTable. There are some substantial differences in the way PivotTables based on OLAP data work compared with those based on Excel lists or non-OLAP databases.


  1. 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.

  1. 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.


  1. 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.

  2. Drag field buttons from the PivotTable Field List and drop them into the appropriate regions in the layout. You must have at least one row or column field, and you must specify a data item.

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.116.63.5