Using the Query Wizard in Excel 2003

By Claire Jarrett

In Excel, a query can help you locate and extract precise information stored within a row or record. Queries can help you extract data from information sources that are large or closed. Additionally, you can create repeatable queries that can deliver updated data without requiring you to restructure your query each time you run it. You can easily create a query using the Query Wizard in Excel 2003.

Queries in Excel are not limited to Excel files. You may also use queries to retrieve data from Access and other database programs, as well as Web pages.

To create a query using the Query Wizard in Excel 2003, you'll need to first define your data source. The process for creating a query is similar, whether the data source is an Excel file, an Access database or another program.

Open the workbook that will receive the query and choose Data > Import External Data > New Database Query. Excel will open the Choose Data Source dialogue box. Select "New Data Source" and click OK.

Excel will open the Create New Data Source dialogue box. In Box 1, type a unique name for your data source. Next, you'll need to select the driver that matches your data source. In Box 2, use the pull-down menu to select the appropriate driver. If you're using an Excel file, choose Microsoft Excel Driver (*.xls).

Select Connect ...

Excel will bring up the ODBC Mircrosoft Excel Setup dialogue box. Choose Excel 97-2000 and choose Select Workbook. In the Select Workbook dialogue box, browse the file system to locate your data source. Click OK to return to the previous dialogue box, then click OK to complete the ODBC Microsoft Excel Setup. Click OK in the Create New Data Source dialogue box. Click OK to complete the setup for your query.

To create a query using the Query Wizard in Excel 2003, choose Data > Import External Data > New Database Query. From the Choose Source dialogue box, choose the data source you established earlier. The Query Wizard - Choose Columns dialogue box will appear. Each named range in the source will appear with a "+" symbol next to it. Click the "+" symbol to expand it and display all available columns within the range. Double-click each column header to move it to the "Columns in your query" pane. Click Next.

The Query Wizard - Filter Data dialogue box will allow you to customise filters for each column of data. Create as many filters as you need to find your desired data. Click Next.

From the Query Wizard - Sort Order, you may specify the sort order for your data. This is optional. Select Next. The Query Wizard - Finish dialogue box allows you to specify what you would like to do next. Simply choose your preferred option. If you plan to reuse this query, choose the Save Query ... button. Excel will save your query in a specific place. Don't change the location of the saved query. When you're done, click Finish.

Excel will run your query and ask you what you would like done with the results. Choose your preferred option and click OK. Excel will create an External Data toolbar to help you manipulate the extracted data. You can use the Refresh Data button on this toolbar to update your extracted information with a single click.

Claire Jarrett is the managing director of Computer Training Solutions in Bristol, Solihull and Bracknell. Computer Training Solutions offer Excel training courses call 0800 019 6882 for more details.

 
photo