Microsoft Office Tutorials and References
In Depth Information
Chapter 9: Tables and Worksheet Databases
In this section, I define the terms that I use throughout this chapter.
h Worksheet database: An organized collection of information contained in a rectangular
range of cells. More specifically, a worksheet database consists of a row of headers
(descriptive text), followed by additional rows of data comprising values or text. I use the
term database loosely. An Excel worksheet database is more like a single table in a
standard database. Unlike a conventional database, Excel does not allow you to set up
relationships between tables.
h Table: A worksheet database that has been converted to a special range by using the
Table command. Converting a worksheet database into an official table
offers several advantages (and a few disadvantages), as I explain in this chapter.
A worksheet database example
Figure 9-1 shows a small worksheet database that contains employee information. It consists of 1
Header row, 7 columns, and 20 rows of data. Notice that the data consists of several different
types: text, numerical values, dates, and logical values. Column E contains a formula that
calculates the monthly salary from the value in column D.
Figure 9-1: A typical worksheet database.
In database terminology, the columns in a worksheet database are fields, and the rows are
records. Using this terminology, the range shown in the figure has seven fields (Name, Location,
Sex, Salary, Monthly Salary, Date Hired, and Exempt) and 20 records.
The size of a database that you develop in Excel is limited by the size of a single worksheet. In
theory, a worksheet database can have more than 16,000 fields and can consist of more than one
million records. In practice, you cannot create a database of this size because it requires an
enormous amount of memory, and will cause even a state-of-the-art computer to slow to a crawl.