Appendix A

When Your Worksheet Is a Database


Databases in Excel

Statistics in databases

Pivot tables

Excel’s main function in life is to perform calculations. As the chapters in this book show, many of those calculations revolve around built-in statistical capabilities.

You can also set up a worksheet to store information in something like a database, although Excel is not as sophisticated as a dedicated database package. Excel offers database functions that are much like its statistical functions, so I thought I’d familiarize you with them.

Introducing Excel Databases

Strictly speaking, Excel provides a data list. This is an array of worksheet cells into which you enter related data in a uniform format. You organize the data in columns, and you put a name at the top of each column. In database terminology, each named column is a field. Each row is a separate record.

This type of structure is useful for keeping inventories, as long as they’re not overly huge. You wouldn’t use an Excel database for recordkeeping in a warehouse or a large corporation. For a small business, however, it might fit the bill.

The Satellites database

Figure A-1 shows an example. This is an inventory of the classic satellites in our solar system. By classic, I mean that astronomers discovered most of them before the 20th century, via conventional telescopes. The three 20th century entries are so dim that astronomers discovered them by examining photographic plates. Today’s supertelescopes ...

Get Statistical Analysis with Excel For Dummies, 4th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.