To create a PivotTable based on external data, choose Data->PivotTable and PivotChart Report from the menu bar.
In the first step of the wizard, make sure that the External Data source radio button is selected.
When you click next button, the following dialogue box will appear.
Click the Get Data button to reveal another dialogue box that will let you specify what type of file to use for the external data. For this example, we will use a Microsoft Access Database stored locally.
Notice that the Use the Query Wizard check box has been checked.
Click the OK button to reveal another dialogue box that will let you browse to the database where the data is located.
Once you select the particular database file that you want to use, click OK to continue.
Select the fields that you want to include in your PivotTable and use the (>) button to move them to the area under Columns in your query heading. When you are finished, click next.
In the Filter Data stage of the query wizard, make sure that you have chosen the right fields for your PivotTable, then click next to continue.
When you see the Query Wizard Sort Order dialogue box, click next again to display the Query wizard finish box.
In the query wizard finish dialogue box, select the Return Data to Microsoft Office Excel radio button and click finish.
When you click finish, you will be returned to the PivotTable wizard dialogue.
Click next to move to the final stage of the wizard.
In the final stage of the PivotTable wizard, select a location for your PivotTable and click the layout button to specify the data for your table, or just click finish to create an empty PivotTable frame.
This is the empty pivot table frame and PivotTable field list based on the external Access database data.
You can arrange the fields by dropping and dragging just as before. If you click the refresh (!) button, any pertinent changes that have been made to the external database will be updated in the PivotTable
|
