Show All
PivotTable and PivotChart Wizard
Use the PivotTable and PivotChart Wizard to create PivotTable reports and PivotChart reports.
Step 1
Step 2a
When you create a PivotTable or PivotChart report from multiple consolidation ranges, you can use page fields to display data for one item independent of other items in a field. When you select an item in a page field, the data shown in the PivotTable or PivotChart report changes.
Create a single page for me.
Creates only one page field for a PivotTable or PivotChart report that you create from multiple Microsoft Excel consolidation ranges. With a single page field, you can view and combine all of your consolidated ranges with a drop-down list.
I will create the page fields.
Customizes page fields for a PivotTable or PivotChart report that you create from multiple Microsoft Excel consolidation ranges. You can assign separate names to your ranges in as many as four page fields.
Step 2b
Range
Specify the range for each consolidation source you want in your PivotTable or PivotChart report. Type the sheet name and range reference in the Range box. The Collapse Dialog button at the right end of this box temporarily moves the dialog box so that you enter the range by selecting cells on the worksheet. When you finish, you can click the button again to display the entire dialog box.
If the range is from another workbook, type the workbook name and sheet name in the Range box, using this syntax:
([workbookname]sheetname!range)
Click Add to enter the reference in the All ranges box. Repeat this procedure for each range you want to consolidate in your PivotTable or PivotChart report.
All ranges
Lists all the worksheet ranges that will be consolidated or combined in your PivotTable or PivotChart report.
Step 3
Specify the location of your PivotTable report. If you are creating a PivotChart report, an associated PivotTable report will be created in the location you specify. This PivotTable report must be in the same workbook as the PivotChart report. If you specify a location in another workbook, the PivotChart report will also be created in that workbook.
To set advanced options, click Options.
Layout
Click to lay out the PivotTable or PivotChart report in a dialog box rather than using on-sheet layout with certain external database sources, including large databases. For example, data retrieval is likely to be slow if you use on-sheet layout for a report based on a cube created by using the OLAP Cube Wizard in Microsoft Query.
You can also use this dialog box to change the page field settings to create page fields that retrieve data for each item separately. Page field options are available only for reports based on external non-OLAP source data.
Options
Sets advanced formatting, layout, memory management, and external data options for your PivotTable or PivotChart report.