Create a PivotChart report

Do one of the following:

When you create a PivotChart report, Microsoft Excel also creates an associated PivotTable report in the same workbook to supply the source data for the chart.

1. If you are basing the report on a Web query, parameter query, report template, Office Data Connection file, or query file, retrieve the data into the workbook and then base the report on the worksheet range containing the retrieved data, or if your query resulted in a PivotTable report instead of a worksheet range, click the report, click Chart Wizard , and then follow the steps below to lay out the report onscreen.

   If you are basing the report on an Excel list or database, click a cell in the list or database.

2. On the Data menu, click PivotTable and PivotChart Report.
3. In step 1 of the PivotTable and PivotChart Wizard, click the type of source data you're using, and then under What kind of report do you want to create?, click PivotChart (with PivotTable).
4. Follow the instructions in step 2 of the wizard.
5. In step 3 of the wizard, specify where you want to put the associated PivotTable report. Then decide whether to lay out the PivotChart report on the screen or in the wizard.

   Usually you can lay out the report on the screen. Use the wizard to lay out the report if you expect retrieval from a large external data source to be slow, or you need to set page fields to retrieve data one page at a time. If you aren't sure, try laying out the report on the screen. You can return to the wizard if necessary.

6. Do one of the following:

   Lay out the report on the screen

   1. From the PivotTable Field List window, drag the fields that you want to display on the category axis to the area on the PivotChart diagram labeled Drop Category Fields Here.

      If you don't see the field list, click within the outlines of the drop areas, and if necessary click Show Field List on the PivotTable toolbar to display the field list.

      To see what levels of detail are available in fields that have levels, click next to the field.

   2. Drag fields that you want to display as series (items shown in the legend) to the area labeled Drop Series Fields Here.
   3. Drag fields that you want to use as page fields to the area labeled Drop Page Fields Here.
   4. Drag fields that contain the data that you want to compare or measure to the area labeled Drop Data Items Here.

      Only fields that have the or icon can be dragged to this area.

   5. To rearrange fields, drag them from one area to another. To remove a field, drag it out of the PivotChart report.

   Note  If data is very slow to appear as you lay out the report, click the associated PivotTable report for the PivotChart report and then click Always Display Pivot Items on the PivotTable toolbar to turn off initial data display. If retrieval is still very slow or error messages appear, click the associated PivotTable report for the PivotChart report, click PivotTable and PivotChart Report on the Data menu, and then lay out the report in the wizard.

   Lay out the report in the wizard

   If you've exited from the wizard, click PivotTable and PivotChart Report on the Data menu to return to it.

   1. In step 3 of the wizard, click Layout.
   2. From the group of field buttons on the right, drag the fields that you want to display on the category axis onto the ROW area in the diagram.
   3. Drag fields that you want to display as series (items shown in the legend) onto the COLUMN area.
   4. Drag the fields that contain the data that you want to compare or measure onto the DATA area.
   5. Drag fields that you want to use as page fields onto the PAGE area.

      If you want Excel to retrieve external data one page at a time, so that you can work with large amounts of source data, double-click the page field, click Advanced, click Query external data source as you select each page field item, and then click OK twice.

   6. To rearrange fields, drag them from one area to another. Some fields can only be used in some of the areas; if you drop a field in an area where it can't be used, the field won't appear in the area.
   7. To remove a field, drag it out of the diagram.
   8. When you are finished laying out the report, click OK, and then click Finish.
   9. If you want, change the chart type and options to customize your chart.

      How?

      1. Click the chart sheet.
      2. Click Chart Wizard , and then select the chart type you want.
      3. Click Next, and then select the options you want for titles, axes, gridlines, legend, data labels, and data table.

If you don't want to see the associated PivotTable report for your PivotChart report, you can hide it. Click the worksheet containing the PivotTable report, point to Sheet on the Format menu, and then click Hide.