3) Pivot Tables based on external data
You can also use data that is stored in an external database or even a text file to populate a Pivot Table. This section will guide you trough an example that uses the "Wine 97" Access database to build a Pivot Table.
Download the "Wine 97" Access database, unzip the file and save it in a directory.
Open a new workbook. Save this, so far empty, workbook in the same directory as the database (call it something imaginative, like Book1). Then use the menu item Data - Pivot Table
to call the Pivot Table Wizard. In Step 1, choose the External Data Source option;
Click the Next button, the dialog box tells you that no data fields have been retrieved. You cannot proceed until you press the Get Data button.
Click the Get Data button now. Excel will ask you to select a data source. If you have already set up the Wine 97 database as a data source it will appear in the list at the bottom of the dialog. If Wine 97 is not listed, select "New Data Source" from the list and Click OK.
Another dialog will appear. In box 1, give your new data source a name. You can type whatever you like here, but just use "Wine 97" for now. In box 2, select Microsoft Access Driver from the list of types of databases, Then Click the Connect
In the next dialog box, Click the Select
At last, you are asked to find the database from your directory tree. If you have saved the workbook in the directory containing these examples, the Wine 97 database should be right there, just select it and Click OK. Otherwise, find the database by using the drive and directory boxes.
You are back to the previous dialog, except that now the Database group includes the path and filename of your database. By the way, if all this is starting to sound complicated; 1) don't blame me, I didn't design this, and 2) the good news is that you only have to do this once.