A PivotChart is basically a column chart (by default) that is based on the data in a pivot table. You can change the chart to a different chart type if desired.
The next To Do exercise creates a PivotChart from an Excel list. You need to use the Excel list you created on Sheet2 earlier in this hour .
-
Click the Sheet3 tab and click any cell in the Excel list. This step tells Excel that you want to use the data in the Excel list to create a PivotChart.
-
Choose Data, PivotTable and PivotChart Report. If the Office Assistant asks whether you want help with pivot tables, choose No. The PivotTable and PivotChart WizardStep 1 of 3 dialog box opens. From this point, until the PivotChart appears in the worksheet, you are working in the PivotTable and PivotChart Wizard.
-
In the Where Is the Data That You Want to Analyze? area, choose Microsoft Excel List or Database if it's not already selected. This step tells Excel the source of the chart data.
-
In the What Kind of Report Do You Want to Create? area, choose PivotChart (with PivotTable), shown in Figure 18.10. Now you've told Excel that you want to create a PivotChart with a pivot table.
Figure 18.10. The PivotTable and PivotChart WizardStep 1 of 3 dialog box.
-
Click the Next button. The PivotTable and PivotChart WizardStep 2 of 3 dialog box shows up. In the Range box, the range should be $A$1:$D$20, which defines the data range you want to use for the PivotChart.
 | If the range is incorrect, click in the Range box and type the correct cell references. A quick way to specify the range in the Range box is to highlight the range in the worksheet. |
-
Click the Next button. Excel asks whether you want to save memory and combine reports by placing the chart on the same sheet as the pivot table or keep the reports separate by placing the PivotChart on a chart sheet by itself. You want to keep the reports separate.
-
Choose No to separate the reports. The PivotTable and PivotChart WizardStep 3 of 3 dialog box opens. You can now tell Excel whether you want to place the PivotChart with pivot table on an existing or new worksheet. You want to place the chart and table on a new worksheet.
-
If necessary, choose New Worksheet.
-
The next step is to design the layout of the pivot table so that Excel can create the chart from the data in the table. Click the Layout button. Excel opens the PivotTable and PivotChart WizardLayout dialog box.
The fields appear on buttons to the right in the dialog box. These currently are the column fields. The four areas you can define to create your pivot table are ROW, COLUMN, DATA, and PAGE.
You drag the field buttons to the areas to define the layout of your pivot table. For example, to summarize the values in a field in the body of the table, place the field button in the DATA area. To arrange items in a field in columns with the labels across the top, place the field button in the COLUMN area. To arrange items in a field of rows with labels along the side, place the field button in the ROW area. To show data for one item at a time, one item per page, place the field button in the PAGE area.
-
Drag the New Clients button to the DATA area.
-
Drag the Magazine button to the ROW area. Your pivot table diagram should resemble the one in Figure 18.11.
Figure 18.11. The PivotTable and PivotChart WizardLayout dialog box.
-
Click OK to return to the PivotTable and PivotChart WizardStep 3 of 3 dialog box. Then click the Finish button. The PivotTable Wizard places the chart in the new worksheet called Chart1, as you can see in Figure 18.12. Also, the PivotTable toolbar and PivotTable Field List window should appear. Close the toolbar and the window.
Figure 18.12. The PivotChart.
