Exploring Excel's Functions, Part 4: Database Functions

• By Peter Aitken
• Feb 24, 2006

From the author of
Manage Your Money and Investments with Microsoft Excel

Why on earth would Excel have database functions? After all, Excel is a spreadsheet program and Access is the database program that is part of the Microsoft Office suite. Well, yes, this is true, but Excel’s spreadsheet structure—that is, rows and columns—lends itself to certain kinds of simple databases, and the database functions are designed specifically to work with this kind of data.

Database Basics

What exactly is an Excel database? It’s a way of storing data that is made up of records and fields. Many kinds of data can be organized in this way, including such common information as an address book, a check register, or a catalog of your book collection. In a database, each record is one unit of data (for example, a book in your collection), and each field is a specific piece of information (for example, the book’s title). An important aspect of databases is that each record contains the same fields. Thus, each book record will contain a title field, an author field, and so on.

In Excel, databases are organized with records in rows and fields in columns. The topmost row always contains the field names, and you’ll see why this is important soon. Figure 1 shows an example of an Excel database with six fields and seven records. This is a very small database, used for demonstration only. Your number of records is limited by Excel’s maximum number of rows (65,636) and the number of fields by the column limitation (A through IV, 256 columns).

Figure 1 An Excel database organizes data into records (rows) and fields (columns).

Now that you understand the way an Excel database is organized, you are ready to learn about the database functions.