PivotTable Reports and PivotChart Reports

Create a PivotTable Report and PivotChart Report in Excel

1. On the Data menu, click PivotTable And PivotChart Report.

2. In the Where Is The Data That You Want To Analyze area, specify whether the data is in a group of Excel cells, an external data source, multiple cell groups in one or more Excel files, or another PivotTable report or PivotChart report.

3. In the What Kind Of Report Do You Want To Create area, specify whether you want to create just a PivotTable report or a PivotTable report and a PivotChart report that uses the PivotTable report as its data source. Click Next.

4. Depending on which option you selected in step 2, do the following:

   • If you specified a group of Excel cells as the data source, click the Range box and then select the group of cells that you want to include in the PivotTable. (If the cells are in another Excel file, click the Browse button first to specify the file’s location.)

   • If you specified an external data source, click the Get Data button, select the data source, click OK in the Choose Data Source dialog box, and then follow the steps in the Query Wizard. When you’ve completed the wizard, click Next.

   • If you specified multiple cell groups in one or more Excel files as the data source, select an option for the page fields you want and then click Next. Click the Range box and select a group of cells that you want to include in your PivotTable report. Click Add to add the group of cells. Repeat this step for each group of cells that you want to include in your PivotTable report, and then click Next.

   • If you specified another PivotTable report or PivotChart Report in step 2, select the PivotTable report name in the Which PivotTable Report Contains The Data You Want To Use list and then click Next.

5. Select an option for where you want to put the PivotTable report area, either in a new worksheet or in the worksheet that’s currently displayed.

6. Click Finish. The reports are created.

7. To customize a PivotTable report or PivotChart report, do the following:

   • Include a field in a PivotTable report or PivotChart by dragging a field from the PivotTable field list (the PivotTable toolbar in Excel 2000) to the drop area.

   • Move a field in a PivotTable report or PivotChart report by dragging the field to another drop area.

   • Remove a field from a PivotTable report or PivotChart report by dragging the field to a screen location outside the report.

Filter Data in a PivotTable Report or PivotChart Report

1. With the PivotTable report or PivotChart report active, click the arrow beside the field name in the report.

2. For page fields, select the item associated with the data that you want to display. For fields other than page fields, select or clear check boxes to display or hide data for the field and then click OK.

Create a Calculated Field or a Calculated Item for Use in a PivotTable Report or PivotChart Report

1. Click a field inside a PivotTable or PivotChart report.

2. On the PivotTable toolbar, open the PivotTable menu, point to Formulas and then click Calculated Field or Calculated Item.

3. Type a name and formula to use for the calculated field or calculated item.

4. Click the Add button to add the calculated field or calculated item to the PivotTable field list.

5. Click OK.