Excel 2007: The Missing Manual

Chapter 24. Querying Databases and XML Files

An ordinary Excel workbook stores a bunch of information, and uses Excel tools (like formulas and charts) to analyze it. However, a great deal of the world’s business information isn’t stored in Excel files. Instead, it sits inside databases (organized catalogs of information managed by high-powered computer software).

Typical mid-size companies (or bigger ones) store product catalogs, customer lists, and purchase orders in databases. When they need to change this information (or just take a look at it), they use another program that talks to the database. Databases are extremely fast, secure, and durable. They’re the best place to put missioncritical information.

At first glance, this approach (storing big volumes of information in a database) seems to cut Excel out of the loop. However, Excel’s greatest strength isn’t storing information, but analyzing it. In fact, Excel is the perfect tool for analyzing information, no matter where it comes from. Your company might track orders using a custom application that dumps its results into a database. If you want to find out what all the numbers amount to, and make a prediction about your company’s future, you can pull that info out of a database (using a process called querying), and then start charting away. In other words, your Excel workbook gets a copy of the information that’s stored in the database so that you can put it under the microscope. Best of all, you can refresh your workbook ...