Show All

Create a PivotTable report

1. Open the workbook where you want to create the PivotTable report.

   • 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 click a cell in the Microsoft Excel list containing the retrieved data.

     If the retrieved data is from an OLAP database, or the Office Data Connection returns the data as a blank PivotTable report, continue with step 6 below.

   • 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, follow the instructions, and click PivotTable under What kind of report do you want to create?
4. Follow the instructions in step 2 of the wizard.
5. Follow the instructions in step 3 of the wizard, and then decide whether to lay out the report onscreen or in the wizard.

   Usually you can lay out the report onscreen, and this method is recommended. Use the wizard to lay out the report only 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 onscreen. You can return to the wizard if necessary.

6. Do one of the following:

   Lay out the report onscreen

   1. From the PivotTable Field List window, drag the fields with data that you want to display in rows to the drop area labeled Drop Row Fields Here.

      If you don't see the field list, click within the outlines of the PivotTable drop areas, and make sure Show Field List is pressed in.

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

   2. Drag fields with data that you want to display across columns to the drop area labeled Drop Column Fields Here.
   3. Drag fields that contain the data that you want to summarize to the area labeled Drop Data Items Here.

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

      If you add more than one data field, arrange these fields in the order you want: Right-click a data field, point to Order on the shortcut menu, and use the commands on the Order menu to move the field.

   4. Drag fields that you want to use as page fields to the area labeled Drop Page Fields Here.
   5. To rearrange fields, drag them from one area to another. To remove a field, drag it out of the PivotTable report.

      To hide the drop area outlines, click a cell outside the PivotTable report.

   Note  If data is very slow to appear as you lay out the report, click Always Display Items on the PivotTable toolbar to turn off initial data display. If retrieval is still very slow or error messages appear, click PivotTable and PivotChart Report on the Data menu, and 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 onto the ROW and COLUMN areas in the diagram.
   3. Drag the fields that contain the data that you want to summarize onto the DATA area.
   4. Drag fields that you want to use as page fields onto the PAGE area.

      If you want Excel to retrieve data one page at a time, so 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. (This option is unavailable for some types of source data, including OLAP databases and Office Data Connections.)

   5. 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.
   6. To remove a field, drag it out of the diagram.
   7. When you are satisfied with the layout, click OK, and then click Finish.