Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query to Treat Excel
As a Relational Data Source

(continued)

Because this is just an introduction, I will leave it at that. You can do a lot more.

• You can refresh this query by clicking a button, in case the data in the original tables has changed.
  
• You can add formulas and have them automatically “copy down” each time you refresh the query.
  
• You can add parameters, and have them refer to a cell in the worksheet, so that you can see different subsets of the data.

You can generate similar queries on text files and databases. For each new data source you need to create a “Data Source Name”. Once you've done so, you can use the data source repeatedly to create any number of queries against that database. You can save the queries and use them in a new workbook.

Return the Data to a Pivot Table

Let me show you one last trick, one that lets you analyze data when the data set you want to look at is too big to fit on an Excel spreadsheet.

From the Data menu, select Pivot Table and Pivot Chart. Select External Data Source when the Wizard comes up.

Choose Next.

In the "Step 2 of 3" dialog above, choose Get Data.

Choose your data source and proceed as before, or create an entirely new query. When you return, you'll have a PivotTable with all the data in the pivot cache, but not on a spreadsheet. Even if the data would not fit on a spreadsheet, this will allow you to create all the pivot reports you need.

The thing I find most appealing about this approach is that it is relatively easy to learn if you have some good documentation. I have been able to reduce my workload and stress significantly by teaching the people who want relatively simple, one-time reports, or those who want to see the data in numerous different configurations, how to use these tools.

If you think this could be of value to you, I highly recommend Mr. Zapawa’s book, “Excel Advanced Report Development” available now from Wiley Publishing, Inc.

previous 1 2 3 4 5