Page 220 - Excel Workbook for Dummies

23_798452 ch16.qxp 3/13/06 7:40 PM Page 203

Chapter 16

Building and

Maintaining Data Lists

In This Chapter
Creating and editing data lists
Sorting the records in a data list
Subtotaling values in a data list
Filtering out unwanted data in data lists
Querying data in external database tables

n addition to its considerable computational abilities, Excel is also very accomplished
Iat maintaining vast collections of related data in what are referred to as data lists or
database tables. This chapter gives you a chance to practice all the basic aspects of
creating and maintaining data lists in Excel including building the list, adding and editing
its data, filtering the list to temporarily remove unwanted data, subtotaling values in a data
list, and creating lists from data external to the worksheet.

Creating a Data List

In Excel, a data list or database table is a table of worksheet data with a special structure.
Unlike the other types of data tables that you might create in an Excel, a data list uses only
column headings (technically known as field names) to identify the different kinds of items
the data list tracks. Each column in the data list contains information for each item you
track in the database, such as the client’s company name or telephone number (technically
known as a field of the data list). Each row in the data list contains complete information
about each entity that you track in the data list, such as ABC Corporation or National
Industries (technically known as a record of the data list).

All you have to do start a new data list in a worksheet is to enter the names of the fields
that you want to track in the top row of the worksheet and then enter the first record of
data beneath. When entering the field names (as column headings), be sure each field name
in the data list is unique and, whenever possible, keep the field name short. When naming
fields, you can align the field name in the cell so that its text wraps to a new line (by press-
ing Alt+Enter). Also, you should not use numbers or formulas that return values as field
names. (You can, however use formulas that return text such as a formula that concatenates
labels entered in different cells.)

When deciding on what fields to create, you need to think of how you’ll be using the data that
you store in your data list. For example, in a client data list, you split the client’s name into
separate first name, middle initial, and last name fields if you intend to use this information in