Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query with Text Files
For Excel PivotTable Analysis

(continued)

Create an Excel Report

Now that the query is done, it's time to return our data to Excel. Click on the "open door" icon shown here. When you do so, MS Query activates Excel and launches the Import Data dialog.

You now can select where you want your data to appear. After you click OK, the top of your range of data looks something like this:

One way to refresh your report is to right-click anywhere within it and select Refresh Data from the menu. You also can choose Data, Refresh Data.

Better yet, you can use the External Data toolbar shown here. To launch the toolbar, right-click on any toolbar and choose External Data from the list. Then, to refresh the data, click the tool with the red exclamation symbol.

Working With Your Report

MS Query provides several useful features for working with Excel reports.

One valuable feature is that MS Query maintains a named range where the data is displayed in a spreadsheet. This range is dynamic, which means that it re-sizes automatically each time you refresh your report.

Because the default range name tends to be quite long, you probably will want to change it. You can right-click on the data range and select Data Range Properties. Then, in the External Data Range Properties dialog, you can rename the range and perform many other customizations. The range name is sheet-level, so if you want to refer to it from another sheet, you will need to include the sheet name.

To illustrate, we changed the name to MyData. Then, we use this formula to return the grand total of the AMT column:

=SUM(OFFSET(MyData,0,6,,1))

Here, the OFFSET function returns a reference to the column with the AMT data and SUM returns the total. The arguments for the OFFSET function are: OFFSET( reference, rows, cols, height, width)

Another useful feature is that you can change the query parameter. You can do so in three ways, as shown in the Parameters dialog below. To launch this dialog, click the third tool from the left in the External Data toolbar shown above, or right-click your data range and choose Parameters.

The third choice in this dialog offers a particularly convenient solution. You can specify a cell that contains your parameter value. You can enter this value manually, return it by formula, return it by using a dropdown list box, and so on.

Finally, you can use MS Query to feed a PivotTable. However, you can not use a PivotTable with a parameterized query. Therefore, click the first button (the Edit Query button) in the External Data toolbar. Modify your query to either hard-code a desired criteria or to remove it entirely. Then save the query with a new name.

Then, to use that query with a PivotTable, follow the general instructions that Marty provided in the last page of Use MS Query to Treat Excel As a Relational Data Source. However, when Excel launches Microsoft Query, close the Add Tables window as shown here.

Next, in the Microsoft Query window, choose File, Open to open the query you created for use with PivotTables. (Instead, of course, you could at this point create and save a new query for your PivotTable to use.) Close your Microsoft Query window. You then can create a PivotTable the way you normally would.

Zapawa's book offers additional ways to bring external data into Excel. You'll find a link to it at the beginning of this article.

previous 1 2 3 4 5