Excel 2013: The Missing Manual

Chapter 27. Analyzing Databases

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. Databases are extremely fast, secure, and durable. They’re the best place to put mission-critical 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, no matter where that information lives.

Say, for example, that your company has a decade of sales records stored in a database. Using Excel, you can extract this raw data and start working with it, using all of Excel’s awesome data analysis tools, from charts to pivot tables. However, the data you get is a copy of what’s in the database, and you can’t alter the original records.

This approach offers the best of both worlds, because it lets your database do what it does best (store massive amounts of data) and lets Excel do what it does best (summarize, chart, and analyze that data). Best of all, you can refresh your workbook any time to get the latest information from the same database, so your workbook never goes out of date.

Excel and Databases

Databases provide the best way to store huge amounts of data, like product ...