Mining for Data Gold Using Microsoft Excel

by Lee Hudspeth

This article series is in two parts:

• Mining for Data Gold Using Microsoft Excel (TNPC #5.09)
• Mining for Data Gold Using Microsoft Excel: Part 2 (TNPC #5.10)

Follow these steps to create a database based on Access' Northwind sample database, and then use this database to follow along in my artcile. The 12 steps we wrote for Excel 97 Annoyances apply today (see pp. 282-283). These steps apply to Excel 97, 2000, and XP. Of course, in some places where you see "97" just substitute the version number; those differences are purely cosmetic.

Note: These steps assume you have Microsoft Query installed. If you did a Typical setup of Office, Microsoft Query was not installed. For information on how to install Microsoft Query, ask Office Assistant, "install Microsoft Query". Microsoft Query may be installed on your PC but not necessarily active as an add-in. To activate it, select Tools, Add-Ins, locate "MS Query Add-in for Excel 5 Compatibility" in the list, check it, then click OK.

1. Create a new workbook, save it as Lists.xls, and select cell A1 in Sheet1.
2. Select Data, choose Get External Data, choose Create New Query, make sure the "Use the Query Wizard to create/edit queries" check box is selected, in the Databases file card select "<New Data Source>" then click OK.
3. In the Create New Data Source dialog box, type Northwind Orders Table in field 1 (the "What name do you want to give your data source?" field). Tab down to field 2, choose the Microsoft Access Driver (*.mdb), then click Connect.
4. In the ODBC Microsoft Access 97 Setup dialog box, click the Select button and browse until you locate Northwind.mdb (typically in the C:\Program Files\Microsoft Office\Office\Samples folder), select it, and click OK to dismiss the Select Database dialog. Click OK again to dismiss the ODBC Microsoft Access 97 Setup dialog.
5. In field 4 of the Create New Data source dialog, choose the Orders table, then click OK.
6. In the Choose Data Source dialog (Northwind Orders Table should be selected in the list now), click OK.
7. The Query Wizard--Choose Columns dialog box appears now, and Orders should be selected in the tree listing on the left (Available tables and columns), so click the > button to put all the Orders table's columns (read: fields) in your query. Click the Next> button.
8. In the Query Wizard--Filter Data dialog box, click Next>.
9. In the Query Wizard--Sort Order dialog box, click Next>.
10. In the Query Wizard--Finish dialog box the Return Data to Microsoft Excel radio button should already be selected, so click Finish.
11. Back in Excel, a few seconds later you'll see the Returning External Data to Microsoft Excel dialog box, indicating the data should pour in to the current sheet starting in cell $A$1. Perfect. Click OK.
12. Once the data has arrived, change Sheet1's name to Northwind Orders, save Lists.xls, and you're done.

Although the book is out of print, you can easily acquire copies through Amazon Marketplace:
Excel 97 Annoyances

You can reach Lee Hudspeth at:
leehudspeth@TheNakedPC.com

Return to Top