PivotTable Wizard
With the PivotTable wizard, you can create pivot tables, which are interactive worksheet tables used to summarize and analyze data from existing, and perhaps large, tables. You can choose either to save a pivot table directly in Microsoft Excel or to add one as an object on a form.
You must have Microsoft Excel with Microsoft Query installed on your computer to create a pivot table. For more information, see Microsoft Excel Help.
To access the PivotTable wizard
- From Tools menu, choose Wizards, and then click PivotTable.
Step 1 – Select Fields
In this step, you can choose a free table or a table within a database as the source for your pivot table. You can select only fields from a single table or view. For information, see Creating Views.
To select the fields for your pivot table
- Use the Databases and Tables controls to locate and select the table you want to use.
- In the Available fields window, select three or four fields you want to use from the selected table, and use the arrow buttons to move them to the Selected fields window.
The wizard will not permit you to proceed to the next step until you have chosen at least three fields; one each for row values, column values, and data.
Step 2 - Define Layout
In this step, you can specify which field values will be calculated for the data. For example, if you have an Orders table that contains, among others, a field for city, a field for region, and a field for order amount, you could create a pivot table that would display in the data area, sums for all the cities, by region. At the bottom, the table will display totals for each column. At the far right the table will display totals for each row.
To define the pivot table layout
- From the Available fields list, drag a field to the Rows box. The pivot table will contain a row for each unique value in the field that you drag to the Rows box
- From the Available fields list, drag a field to the Columns box. The pivot table will contain a column for each unique value in the field that you drag to the Columns box
- From the Available fields list, drag a field to the Data box. Because this field will be summarized, generally it is best to drag a numeric field here.
You also can drag a field to the Page box. If you drag a field to the Page box, the pivot table will contain a drop-down list from which you can select the different pages. There will be one page for each unique value that exists in the field you drag to the Page box.
If you have a large table, you might want to first create a view that contains the desired fields, then create a pivot table from that view. For information, see Creating Views.
Step 3 - Finish
If you select Create a Microsoft Excel pivot table, the wizard will display your finished pivot table in Excel. The default calculation for the pivot table wizard is sums. If you create an Excel pivot table, then you can change the calculation easily to another supported by Excel, such as average or count.
If you select Create a new form containing an embedded pivot table, the wizard will create a new form containing the embedded pivot table and open it in the Form designer. Double-click the embedded object to modify it in the Form designer.