My Kiva


Ed Ferrero

Pivot Table Tutorial - Part 4


3) Pivot Tables based on external data

a) Setting up

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;

Pivot Table - Data Source

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.

Pivot Table - Get Data

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.

Choose Data Source

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… button.

Create Data Source

In the next dialog box, Click the Select… button.

ODBC Setup

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.

Select Database

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.

ODBC Setup