Understanding Data Lists
Fields
You use fields to break down your database list into
manageable pieces. In Excel, fields are typically the
columns you use to define each part of your list. For
example, an address database includes fields such
as name, address, and phone number. Field names,
also called labels, appear at the top of a list.
Records
You use rows to enter each database entry for your
list of data. Database entries are called records. For
example, in an inventory database, a single row
contains all the information about an item in the
inventory. Each row represents one record in the
database.
Tables
An entire database list of information is called a table.
You can create multiple tables in Excel. For example,
one table might list customers and addresses, and
another might list product items and prices. You
might pull information from two tables to create a
third table, such as a table listing customers and the
items they buy.
Forms
You can use a Data Form to speed up the task of
entering records into a database list. In Excel, the
Data Form is a special dialog box consisting of all the
fields in your table. To enter a record, you can fill out
the form fields.
Plan a Database
Before you create a database list in Excel, take time
to plan it out. Planning beforehand can alleviate
having to reorganize your database later when you
discover you left out important fields. Start by
determining what kind of data you want to store and
how it should be organized. Decide in what order you
want to enter data. Each database table should have
a specific topic, such as product inventory or client
addresses. Most databases are comprised of at least
two or more fields or columns.
Database Tips
Do not include blank rows in a database table. For
best results, break out data into separate fields. For
example, break City and State into two separate
fields, instead of combining them into one. This can
help you perform better analysis tasks later. It is not a
good idea to place multiple tables on a single sheet;
instead, place each one on a separate sheet in the
workbook file.
Create a Database Table
Type Field Labels
1. Click where you want to insert the first
column.
2. Type a field label.
3. Press TAB .
4. Type the next field label.
5. Repeat steps 3 and 4 to continue
entering as many field labels as your
list requires.
Enter Records
1. Click in the first row beneath the field
labels.
2. Type the data for the first field.
3. Press TAB.
4. Type the next field data.
5. Repeat steps 3 and 4 to continue filling in a
complete record.
6. Press ENTER.
*Excel starts a new record for the table by
moving automatically to the next row.
7. Repeat steps 2 to 6 to continue
entering records for your table.
Create a Table
1. Select the data you want to turn into a
database table.
2. Click the Insert tab.
3. Click Table.
The Create Table dialog box opens.
*By default, the selected range appears
here. If the range is not correct, you can
select the correct cell references.
*Select this option ( changes to ) if
necessary to include the headers in your
table.
4. Click OK.
* Excel turns the data into a table, fills the
cells of the table with blue shading, and
displays filter arrows ( ) for each field.
The Table Tools Design tab also appears
on-screen.
* To filter a list, click and click the data
you want to filter out.
Tips
Do I have to turn my data into a table?
No. You can still treat your data as a database without having to turn it into an official Excel
table. The advantage to the Create Table command is that it automatically adds to
your field labels. You can also sort by ascending or descending order from the AutoFilter
drop-down lists.
Can I turn my list back into a regular range?
Yes. To convert the list back to a regular Excel range, select the list, click the Table Tools
Design tab, and then click Convert to Range.
Click Yes when prompted. Excel removes the AutoFilter arrows from the field labels.
You can still treat the data as a database, performing sorts and filters, even without the
official list status.
Add Records Using Data Form
1. Enter a record in a row.
2. Select the first cell of a record.
3. Click Form on the Quick Access
toolbar.
Note: If your database has no records yet, a prompt box
appears. Click OK to continue.
4. The data entry dialog box opens.
Click New.
5. Type the data for the first field.
6. Press TAB.
7. Repeat steps 3 to 4 to continue filling in
form fields.
You can click New to enter another
record.
8. Click Close.
Excel adds the record or records to the
database list.
TIPS
How do I navigate through my records using a form?
You can use the Data Form dialog box to navigate between all the records in your table.
Click Find Prev to move backward through the table, or click Find Next to move forward.
Any time you want to add a new record, click New and fill out the fields.
What methods can I use to delete a record?
You can open the Data Form dialog box and navigate to the record.
Click the Delete button and confirm the deletion, and the record is gone.
You can also click and drag your mouse across the fields of the record in the table
on the Excel worksheet and then press Delete.
Bookmarks