Excel has some great analysis and presentation tools, but these tools require data. In many cases, the data that you need is available in an external database. For example, your company may have a database that contains customer information, sales data, and so on. This chapter is an introduction to retrieving data from external database files for use in Excel.
When you work with an Excel workbook, the entire workbook must be loaded into memory before you can begin working. Although loading all the data provides you with immediate access to the entire file and all the data it contains, it also means that you can’t work with extremely large amounts of data. Although Excel 2007 supports more than a million rows, actually using that many rows can slow your system to a crawl—even if your system has plenty of memory.
When you access an external database file using Excel, you can perform a query to load just a subset of the data into your workbook.
Accessing external database files from Excel is useful in the following situations:
You need to work with a subset of a very large database.
You share the database with others; that is, other users have access to the database and may need to work with the data at the same time.
The database is in a format that Excel can’t import, or the database may be too large to import.
The database contains multiple tables with relationships between those tables.
If you need to work with external databases, you may prefer Excel to other database programs. The advantage? After you bring the data into Excel, you can manipulate and format it by using familiar tools such as formulas and pivot tables. Of course, real database programs, such as Access, have advantages, too. For example, creating a complex database report in Access may be easier than creating it in Excel.
Microsoft Access is included with some versions of Office 2007. An Access database contains one or more tables of data, and you can import an Access table directly into a worksheet by choosing Data 
Get External Data From Access. When you choose this command, Excel displays the Select Data Source dialog box, which you use to locate the Access file.
When you’ve located the Access database file, Excel displays the Select Table dialog box, in which you select the table (or view) to import. Figure 33.1 shows the tables and views available for Microsoft’s Northwind Traders database, a sample database that’s installed with Access. Select the table, click OK, and Excel displays its Import Data dialog box, shown in Figure 33.2. Use this dialog box to specify the location and whether you want a normal table or a pivot table.
Importing an Access table in this manner is all or none. You’re not given an opportunity to query the database for specific records.
Figure 33.3 shows part of the Customers table from the Northwind Traders database. Excel converts the data to a table.
This section discusses Microsoft Query, an application that ships with Excel. Use Query to import a subset of a database.
Note
To perform queries using external databases, Microsoft Query must be installed on your system. (This program is included with Excel.) If Query isn’t installed, you’re prompted to install it when you first choose Data Get External Data From Other Sources From Microsoft Query.
The best way to become familiar with Microsoft Query is to walk through an example. In the following sections, you learn how to use Query to open a database file and import a specified set of records.
On the CD-ROM
The database file used in this example is available on the companion CD-ROM. It’s an Access 2007 file named budget data.accdb.
The database file used in this example is a single-table Access file that consists of 31,680 records. This table contains the following fields:
Sort: A numeric field that holds record sequence numbers.
Division: A text field that specifies the company division (either Asia, Europe, N. America, Pacific Rim, or S. America).
Department: A text field that specifies the department within the division. Each division is organized into the following departments: Accounting, Advertising, Data Processing, Human Resources, Operations, Public Relations, R&D, Sales, Security, Shipping, and Training.
Category: A text field that specifies the budget category. The four categories are Compensation, Equipment, Facility, and Supplies & Services.
Item: A text field that specifies the budget item. Each budget category has different budget items. For example, the Compensation category includes the following items: Benefits, Bonuses, Commissions, Conferences, Entertainment, Payroll Taxes, Salaries, and Training.
Month: A text field that specifies the month (abbreviated as Jan, Feb, and so on).
Year: A numeric field that stores the year (either 2005 or 2006).
Budget: A numeric field that stores the budgeted amount.
Actual: A numeric field that stores the actual amount spent.
Variance: A numeric field that stores the difference between the Budget and Actual.
The objective of this exercise is to create a report that shows the first quarter 2006 (January through March) Compensation expenditures of the Training Department in the North American Division. In other words, the query will extract records that meet all of the following criteria:
The Division is N. America.
The Department is Training.
The Category is Compensation.
The Month is Jan, Feb, or Mar.
The Year is 2006.
One approach to this task is to import the entire Access file into a worksheet using the method described earlier in this chapter (see “Importing Access Tables”). When the table is imported, you can filter it to display only the rows that meet the specified criteria. This approach works because this particular table isn’t very large. However, in some cases, the number of records in the table may exceed the number of rows in a worksheet. The advantage of using Query is that it imports only the data that’s required. And, once you’ve imported the data, you can refresh the query at any time to bring in updated data.
Begin with an empty worksheet. Choose Data Get External Data From Other Sources From Microsoft Query, which displays the Choose Data Source dialog box, shown in Figure 33.4. This dialog box contains three tabs:
Databases: Lists the data sources that are known to Query—this tab may be empty, depending on which data sources are defined on your system.
Queries: Contains a list of stored queries. Again, this tab may or may not be empty.
OLAP Cubes: Lists OLAP databases available for query.
Your system may have some data sources already defined. If so, they appear in the list on the Databases tab. To set up a new data source, use the <New Data Source> option. For this example, choose <New Data Source> and click OK. The Create New Data Source dialog box, which has four parts, appears (see Figure 33.5):
Enter a descriptive name for the data source. For this example, use the name Budget Database.
Select a driver for the data source by selecting from the list of installed drivers. For this example, choose Microsoft Access 12 Driver
(*.mdb,*.accdb).Click the Connect button to display another dialog box that asks for information specific to the driver that you selected in Step 2. In this example, you need to click the Select button and then locate the budget data.accdb file. Click OK to return to the previous dialog box and click OK again to return to the Create New Data Source dialog box.
Select the default data table that you want to use. For this example, the database file contains a single table named
budget. If the database requires a password, you can also specify that the password be saved with the data source definition.When you’ve supplied all the information in the Create New Data Source dialog box, click OK. You’re returned to the Choose Data Source dialog box, which now displays the data source that you created.
Note
You only have to go through these steps once for each data source. The next time that you need to access this data source, the Budget Database (and any other database sources that you’ve defined) appears in the Choose Data Source dialog box.
Note
The preceding steps are general steps that work with all supported database types. In some situations, you may prefer to open the database file directly and not create a named data source that will appear in the Choose Data Source dialog box. For example, if you won’t be using the database again, you can open the file directly and not have to bother creating a new named data source. If you’re using an Access file, you can select MS Access Database from the Databases tab in the Choose Data Source dialog box. Then, you can specify the file, and you’re taken directly to Microsoft Query.
The Choose Data Source dialog box has a check box at the bottom that lets you specify whether to use the Query Wizard to create your query. The Query Wizard walks you through the steps used to create your query, and if you use the Query Wizard, you don’t have to deal directly with Query. I highly recommend using the Query Wizard, and the examples in this chapter use this tool.
In the Choose Data Source dialog box:
In the first step of the Query Wizard, select the database columns that you want to appear in your query. Select one or more columns and click the > button to add them (see Figure 33.6). To select all fields, click the table name (budget) and click the > button.
If you want to see the data for a particular column, select the column and click the Preview Now button. If you accidentally add a column that you don’t need, select it in the right panel and click the < button to remove it.
For this example, add all the fields and then click the Next button.
In the second Query Wizard dialog box, you specify your record selection criteria—how you want to filter the data. This step is optional. If you want to retrieve all the data, just click the Next button to proceed.
Figure 33.7 shows the Filter Data dialog box of the Query Wizard.
For the example, not all records are needed. Recall that you’re interested only in the records in which one of the following applies:
The Division is N. America.
The Department is Training.
The Category is Compensation.
The Year is 2006.
The Month is Jan, Feb, or Mar.
The criteria are entered by column. In this case, you have five criteria (one for each of five columns):
In the Column To Filter list, select Division. In the right panel, select equals from the first drop-down list and select N. America from the second drop-down list.
In the Column To Filter list, select Department. In the right panel, select equals from the first drop-down list and select Training from the second drop-down list.
In the Column To Filter list, select Category. In the right panel, select equals from the first drop-down list and select Compensation from the second drop-down list.
In the Column To Filter list, select Year. In the right panel, select equals from the first drop-down list and select 2006 from the second drop-down list.
In the Column To Filter list, select Month. In the right panel, select equals from the first drop-down list and select Jan from the second drop-down list. Because this column is filtered by multiple values, click on the Or option and then select equals and Feb from the drop-down lists in the second row. Finally, select equals and Mar from the drop-down lists in the second row.
To review the criteria that you’ve entered, just select the column from the Column To Filter list. The Query Wizard displays the criteria that you entered for the selected column.
When you’ve entered all the criteria, click Next.
The third step of the query lets you specify how you want the records to be sorted (see Figure 33.8). This step is optional, and you can click Next to move to the next step if you don’t want the data sorted or if you prefer to sort it after it’s returned to your worksheet.
For this example, sort by Category in ascending order. You can specify as many sort fields as you like. Click Next to move on to the next step.
The final step of the Query Wizard, shown in Figure 33.9, lets you save the query so that you can reuse it. To save the query, click Save Query and then enter a filename.
Select an option that corresponds to what you want to do with the returned data. Normally, you want to return the data to Excel. If you know how to use the Microsoft Query application, you can return the data to Query and examine it or even modify the selection criteria.
For this example, select Return Data to Microsoft Excel and click Finish.
Figure 33.10 shows the Import Data dialog box, which appears when you click the Finish button in the Query Wizard dialog box.
For this example, select the Table option and place the data beginning in cell A1 of the existing worksheet.
Note
If you choose PivotTable Report or PivotChart and PivotTable Report, you can specify the layout for a pivot table (see Chapters 34 and 35). In such a case, the database is used as the source for the pivot table, and the original data is not stored in your workbook.
Figure 33.11 shows the data that is returned to a worksheet.
Excel stores the data that Query returns in either a worksheet or a pivot table. When Excel stores data in a worksheet, it stores the data in a table that’s a specially named range known as an external data range; Excel creates the name for this range automatically. In this example, the external data range is named Table_Query_from_Budget_Database.
You can manipulate data returned from a query just like any other worksheet range. For example, you can sort the data, format it, or create formulas that use the data.
The following sections describe what you can do with the data that Excel receives from Query and stores in a worksheet.
You can adjust various properties of the external data range by using the External Data Properties dialog box (see Figure 33.12).
To display this dialog box, the cell pointer must be within the external data range. You can open this dialog box by using either of three methods:
Right-click and choose Table
External Data Properties from the shortcut menu.Choose Data
Manage Connections Properties.
For more settings (applicable for advanced users), click the Properties icon, which is directly to the right of the Name box in the External Data Properties dialog box. Excel displays the Connection Properties dialog box.
After performing a query, you can save the workbook file and then retrieve it later. The file contains the data that you originally retrieved from the external database. The external database may have changed, however, in the interim.
Fortunately, Excel saves the query definition with the workbook. Simply move the cell pointer anywhere within the external data table in the worksheet and then use one of the following methods to refresh the query:
Right-click and choose Refresh from the shortcut menu.
Choose Data
Manage Connections Refresh All.Click Refresh in the Workbook Connections dialog box (displayed by choosing Data
Manage Connections Connections).
Excel uses your original query to bring in the current data from the external database.
Tip
If you find that refreshing the query causes undesirable results, use Excel’s Undo feature to “unrefresh” the data.
Note
A single workbook can hold as many external data ranges as you need. Excel gives each query a unique name, and you can work with each query independently. Excel automatically keeps track of the query that produces each external data range.
Caution
After performing a query, you may want to copy or move the external data range, which you can do by using the normal copy, cut, and paste techniques. However, make sure that you copy or cut the entire external data range—otherwise, the underlying query is not copied, and the copied data can’t be refreshed.
If you decide that you no longer need the data returned by a query, you can delete it by selecting the entire external data range and pressing Delete. Excel displays a warning and asks you to verify your intentions.
If you bring the query results into your worksheet and discover that you don’t have what you want, you can modify the query. Move the cell pointer anywhere within the external data table in the worksheet. Right-click and choose Table Edit Query from the shortcut menu. You need to edit the query using Microsoft Query. See the next section to learn how to work with Query directly.
When you choose Data Get External Data From Other Sources From Microsoft Query, the Choose Data Source dialog box gives you the option of whether to use Query Wizard to create your query. If you choose not to use Query Wizard, Microsoft Query is launched in a new window. You also work directly with Query if you choose to edit a query that was created with Query Wizard.
Note
Microsoft Query is a relatively old application, and its user interface hasn’t been updated to match the other Office 2007 programs. It works fine. It just looks old-fashioned.
Before you can create a query, you must display the Criteria pane. In Query, open the View menu and place a check next to the Criteria command. The Criteria pane appears in the middle of the window. Figure 33.13 shows Microsoft Query, after selecting the Budget Database from Excel’s Choose Data Source dialog box. The Criteria pane also appears.
The Query window has three panes, which are split horizontally:
Tables pane: The top pane, which holds the selected data tables for the database. Each data table window has a list of the fields in the table.
Criteria pane: The middle pane, which holds the criteria that determine the rows that the query returns.
Data pane: The bottom pane, which holds the data that passes the criteria.
Creating a query consists of the following steps:
Drag fields from the Tables pane to the Data pane. You can drag as many fields as you want. These fields are the columns that the query will return. You can also double-click a field instead of dragging it.
Enter criteria in the Criteria pane. When you activate this pane, the first row (labeled Criteria Field) displays a drop-down list that contains all the field names. Select a field and enter the criteria below it. Query updates the Data pane automatically, treating each row like an OR operator.
Choose File
Return Data to Microsoft Excel to execute the query and place the data in a worksheet or pivot table.
Figure 33.14 shows how the query for the example presented earlier in this chapter appears in Query.
The example in this chapter uses only one database table. Some databases, however, use multiple tables. These databases are known as relational databases because a common field links the tables. Query lets you use any number of tables in your queries.
To add, delete, and edit data when you’re using Query, make sure that a check mark appears next to the Records Allow Editing command. Of course, you can’t edit a database file that’s set up as read-only.
If you don’t like the data’s appearance in the Data pane, you can change the font used by choosing Format Font. Be aware that selective formatting isn’t allowed (unlike in Excel); changing the font affects all the data in the Data pane.
This chapter isn’t intended to cover every aspect of Microsoft Query; rather, it discusses the basic features that are used most often. In fact, if you use the Query Wizard, you may never need to interact with Query itself. But if you do need to use Query, you can experiment and consult the online Help to learn more. As with anything related to Excel, the best way to master Query is to use it—preferably with data that’s meaningful to you.