Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query to Treat Excel
As a Relational Data Source

You can maintain ranges in Excel that work like relational tables.
You can join them by their common fields. Query them using SQL.
And use the queries with PivotTables. Here's how.

by Marty Ryerson
October, 2005

I work for a manufacturing company with plants in five states. When I started working there I used Crystal Reports to answer people’s questions about their data.

But the Crystal reports took a long time to create. And about 95% of the time, after I had written a Crystal report, the person I had done it for would ask to see it in Excel.

With more work, I figured out a way to export the reports from Crystal to Excel without spending a lot of time. But it still took a lot of time to create or modify new reports.

Several years ago, I discovered Microsoft Query, which is included with Excel. Although the program is less advanced than other Office programs, it obviously could give Excel users significant power in working with external data. But unfortunately, I could find very little documentation about the tool.

Finally, I learned that Timothy Zapawa had written about MS Query extensively in his 2005 book, Excel Advanced Report Development. With his information at hand, I finally have been able to use MS Query on the job.

MS Query gives Excel users the ability to access 3rd party databases, text files, and Excel workbooks as relational data sources. With text files, you can place them all in one folder to form a database. With Excel, you define several named ranges in a single workbook, and then use the ranges as database tables.

MS Query doesn't give you many of the built-in features of a “real” database query program, such as Microsoft Query Analyzer or TOAD from Quest. But you certainly can join two or more tables by their common fields. You can use SQL queries to access these tables. And you can send the SQL query results to worksheets or access them with PivotTables.

If you know SQL, you can slice and dice all you want. If you don’t know SQL, it's a lot easier to learn than VBA.

In this article, I'll show you how to define three ranges in an Excel workbook as relational tables, and then display queries against these tables in a worksheet. I'll also explain how to access the tables using a PivotTable.

If you want to follow along with this description, you can download the sample MS Query workbook from this link.

The Sample Database

The mini-database that I created has three ranges organized as tables. Each of the tables has more rows than are pictured below. I've included the images here to illustrate the data and how it's organized. The tables are:

1. A customer table named CUST:

2. An order table named ORD:

3. A sales rep table named SREP:

Notice that like standard relational tables, these have certain fields in common.

Also notice how the second table is formatted. These formats are among the few that MS Query will recognize. To assign these formats, choose columns B and C and then assign the first format listed in Format, Cells, Number, Date. Then choose column F and assign the first format listed in Format, Cells, Number, Currency.

When using Excel as the source of data, it's important that each of the tables be a named range, because when MS Query uses workbooks as a data source it will recognize only named ranges as tables. I usually place the ranges on separate sheets, but that isn't necessary.

Once you've set up these tables, save and close the workbook. The workbook must be closed when it is accessed by MS Query.

Create a Connection

Open another workbook where you will create your Excel report. Choose Data, Import External Data, New Database Query, which launches the Choose Data Source dialog box.

(If MS Query isn't installed, a message will appear asking if you want to install it. To do so, place your installation disk in the appropriate drive and follow the on-screen instructions.)

The first time you access a database, including a workbook database, you'll need to create a new Data Source. To do so, select the <New Data Source> line, and then click OK.

In the first edit box of the Create New Data Source dialog, give your data source a name that will remind you what it is connected to. This is the name you will select from a list when you create new queries later.

The item asks you to select the driver type. Because Excel is the source of data for this exercise, select the Excel driver shown from the drop-down list.

Choose the Connect button and select the version of Excel you're working with.  Notice that even if you use Excel 2003, the most-current version of Excel listed is Excel 97-2000.

1 2 3 4 5 next