Microsoft Office Tutorials and References
In Depth Information
5 Creating, Sorting, and
Querying a Table
A table , also called a database , is an organized collection of data. For example, a list of
friends, a list of students registered for a class, a club membership roster, and an instructor’s
grade book can be arranged as tables in a worksheet. In these cases, the data related to a per-
son is called a record , and the data items that make up a record are called ﬁ elds . For exam-
ple, in a table of sales reps, each sales rep would have a separate record; each record might
include several ﬁ elds, such as name, age, hire date, state, and sales quota. A record in a table
also can include ﬁ elds (columns) that contain formulas and functions. A ﬁ eld, or column,
that contains formulas or functions is called a calculated column . A calculated column
displays results based on other columns in the table.
A worksheet’s row-and-column structure can be used to organize and store a
table. Each row of a worksheet can store a record, and each column can store a ﬁ eld.
Additionally, a row of column headings at the top of the worksheet can store ﬁ eld names
that identify each ﬁ eld. Excel’s built-in data validation features help ensure data integrity
of the data entered in the table.
After you enter a table onto a worksheet, you can use Excel to (1) add and delete
records; (2) change the values of ﬁ elds in records; (3) sort the records so Excel displays
them in a different order; (4) determine subtotals for numeric ﬁ elds; (5) display records
that meet comparison criteria; and (6) analyze data using database functions. This chapter
illustrates all six of these table capabilities.
The project in the chapter follows proper design guidelines and uses Excel to create the
worksheet shown in Figure 5–1. Silver Photography Accessories sells equipment to photog-
raphy stores throughout the western United States. The company’s sales director has asked
for a workbook that summarizes key information about sales reps and their performance.
The data in the workbook should be easy to summarize, sort, edit, and query.