Datasheet

Book VI
Chapter 1
Building and
Maintaining Data Lists
573
Data List Basics
2. Make the first entries in the appropriate columns of the row immedi-
ately below the one containing the field names.
These entries in the first row beneath the one with the field names con-
stitute the first record of the data list.
3. Click the Format as Table button on the Ribbon’s Home tab and then
click a thumbnail of one of the table styles in the drop-down gallery.
As soon as you click the Format as Table button, a marquee appears
around all the cells in the new data list including the top row of field
names. As soon as you click a table style in the drop-down gallery, the
Format As Table dialog box appears listing the address of the cell range
enclosed in the marquee in the Where Is the Data for Your Table text
box, and the My Table Has Headers check box is selected.
4. Click the OK button to close the Format As Table dialog box.
Excel formats your new data list in the selected table format and adds
AutoFilter (drop-down buttons) to each of the field names in the top row.
Figure 1-1 shows you a sample employee data list after formatting it as a
table using Table Style Light 1. This data list begins in row 1 of this work-
sheet, which contains the names for the ten fields in this data list (ID No
through Profit Sharing) all with AutoFilter buttons (thanks to the format-
ting as a table). Note that employees’ names are divided into separate First
Name and Last Name fields in this list (columns B and C, respectively).
Note too, that the first actual record of the data list is entered in row 2 of
the worksheet, directly under the row with the field names. When entering
your records for a new data list, you don’t skip rows but keep entering each
record one above the other going down successive rows of the worksheet.
When you’re entering the row with the first data record, be sure to format all
the cells the way you want the entries in that field to appear in all the sub-
sequent data records in the data list. For example, if you have a Salary field
in the data list, and you want the salaries formatted with the Currency style
number format without any decimal places, be sure to format the salary
entry in the first record in this manner. If you have a ZIP Code field, format
it with the Special Zip Code format or as Text so that Excel doesn’t drop
the initial zeros from codes that begin with them such as 00234. That way,
all subsequent records will pick up that same formatting for the salary field
when you enter them with Excel’s data form.
Creating calculated fields
When creating a new data list, you can make full use of Excel’s calculating
capabilities by defining fields whose entries are returned by formula rather
than entered manually. The sample employee list introduced in Figure 1-1
contains just such a calculated field (shown on the Formula bar) in cell I2
that contains the first entry in the Years of Service field.
30_489598-bk06ch01.indd 57330_489598-bk06ch01.indd 573 3/27/10 12:46 PM3/27/10 12:46 PM