Building a Pivot Table

You can build a simple pivot table with the Data, PivotTable and PivotChart Report option, which displays a series of PivotTable Wizard dialog boxes. The wizard steps you through the process of creating a pivot table, and you get to see a basic breakdown of the data you have in your Excel list or database. A diagram with the labels PAGE, COLUMN, ROW, and DATA appears, and you just drag field buttons onto the diagram. This step tells Excel about the data you want to analyze with a pivot table.

Perform the steps in the following To Do exercise to create a pivot table from an Excel list. Get ready to use the Data Analysis workbook you used in Hour 17, "Analyzing Your Data."

To Do: Build a Pivot Table

1. In Sheet2 in the Data Analysis workbook, type the data shown in the worksheet in Figure 18.3. Click any cell in the list. Now the active cell is within the list, and Excel knows that you want to use the data in the Excel list to create a pivot table.

   Figure 18.3. The data you need to enter in an Excel list.

   

2. 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, as shown in Figure 18.4. From this point, until the pivot table appears in the worksheet, you are working in the PivotTable Wizard.

   Figure 18.4. The PivotTable and PivotChart WizardStep 1 of 3 dialog box.

   

3. 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 tabular data.

4. In the What Kind of Report Do You Want to Create? area, choose PivotTable. Now Excel knows that you want to create a pivot table.

5. Click the Next button. The PivotTable and PivotChart WizardStep 2 of 3 dialog box opens. In the Range box, the range should be $A$1:$D$20, which defines the data range you want to use for the pivot table. The range should include the columns headings in row 1.

   

   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.

   
   

6. Click the Next button. The PivotTable and PivotChart WizardStep 3 of 3 dialog box opens. You use this dialog box to tell Excel whether to place the pivot table on an existing or new worksheet. In this case, you want to place the table on a new worksheet.

7. If necessary, choose New Worksheet.

8. The next step is to design the layout of the pivot table. Click the Layout button. Excel opens the PivotTable and PivotChart WizardLayout dialog box, as shown in Figure 18.5.

   Figure 18.5. 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.

9. Drag the Month button to the PAGE area.

10. Drag the New Clients button to the DATA area.

11. Drag the Responses button to the ROW area.

12. Drag the Magazine button to the COLUMN area.

13. 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 table in the new worksheet called Sheet4, as you can see in Figure 18.6. Also, the PivotTable toolbar and the PivotTable Field List should appear.

    Figure 18.6. The pivot table, PivotTable toolbar, and PivotTable Field List.