Part V: Analyzing Data with Excel

FIGURE 33.11

The results of the query.

Working with Data Returned by Query

Excel stores the data that Query returns in either a worksheet or a pivot table cache. 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.

Adjusting the external data range properties

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. Open this dialog box by using either of these methods:

•Right-click and choose Table External Data Properties from the shortcut menu.

•Choose Data Connections Properties.

688

Chapter 33: Getting Data from External Database Files

For more settings (applicable for advanced users), click the Properties icon, which is directly to the right of the Name field in the External Data Properties dialog box. Excel displays the Connection Properties dialog box.

FIGURE 33.12

The External Data Properties dialog box enables you to specify various options for an external data range.

Refreshing a query

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 Connections Refresh All.

•Click Refresh in the Workbook Connections dialog box (displayed by choosing Data 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 the Undo button to “unrefresh” the data. n

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. n

689

Part V: Analyzing Data with Excel

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. n

Deleting a query

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. Your data source definition remains intact, so you can always re-specify your original query.

Changing your query

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.

Using Query without the Wizard

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 programs. It works fine. It just looks old-fashioned. n

Creating a query manually

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 menu item. The Criteria pane appears in the middle of the window. Figure 33.13 shows Microsoft Query, after selecting the Budget Database from the Choose Data Source dialog box.

690

Chapter 33: Getting Data from External Database Files

FIGURE 33.13

Display the Criteria pane as shown here so that you’ll be able to create your query.

Tables pane

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:

1.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.

2.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.

3.Choose File Return Data to Microsoft Excel to execute the query and place the data in a worksheet or pivot table.

691

Part V: Analyzing Data with Excel

Figure 33.14 shows how the query for the example presented earlier in this chapter appears in Query. Recall that the goal is to retrieve records in which all of the following applies:

•The Division is N. America.

•The Department is Training.

•The Category is Compensation.

•The Year is 2009.

•The Month is Jan, Feb, or Mar.

FIGURE 33.14

Add the fields and criteria to complete your query.

Tip

Double-clicking a criteria box to display the Edit Criteria dialog box enables you to select an operator and value. n

Using multiple database tables

The example in this chapter uses only one database table. Some databases, however, use multiple tables. These databases are relational databases because a common field links the tables. Query lets you use any number of tables in your queries.

692