Microsoft Excel XP/X

Database Basics

This document presents the basics of working with an Excel database. Topics include the following:

Tips for Setting Up a Database
Creating a Database
Using Database Forms
Using the Worksheet Window

Tips for Setting Up a Database

Be sure not to leave any blank rows in the middle of the database. Blank rows will cause only the part of the database above the empty portion to be selected when using the database analysis functions (e.g., dsum, dcount, and sort).

Make sure you enter numeric data as numbers or as all text. Numbers will be counted first when using the analysis functions. To enter a number as text, precede the number with an apostrophe or format the necessary cells as text. If a number is treated as "text," you cannot use the number in mathematical formulas (e.g., SUM and AVERAGE). For more information on entering values and text, refer to Getting Started with Excel.

Creating a Database

Excel recognizes lists of information as a database when you use the database functions. These functions, such as finding, sorting, or subtotaling, can make organizing and analyzing your information easier. A worksheet is made up of columns, rows, and column labels. In a database these are called fields, records, and field names, respectively.

sample database

Using Database Forms

Entering a lot of database information to the worksheet can be time-consuming and inefficient. You often have to move the cursor around the page and can lose valuable time. Excel has an easier way of entering data called the Forms feature. Forms allows you to switch from field to field in your record by pressing [Tab]. When a record is added, Excel automatically clears the form and is ready for you to enter the next information. The Forms feature is also helpful for searching for a record. The Find Next and Find Prev options make locating a specific record easier.

Accessing Forms

Place the cell pointer within the database
From the Data menu, select Form...
A dialog box displaying the sheet name appears.

To perform your task, follow the instructions under Using Form Options
To return to your spreadsheet, click CLOSE

Using Form Options

Adding a New Record

Place the cell pointer within the database
From the Data menu, select Form...
A dialog box displaying the sheet name appears.
Click NEW
The form will be cleared and ready for you to enter data.
In each field, type the desired information
NOTE: To move between fields, press [Tab]
If you press [Enter] after you have typed the last field information, the data will be added to your database and you will automatically go to a blank form to start a new record.
When you have entered the last record, click CLOSE
The new records are added to the end of your database.

Deleting a Record

Place the cell pointer within the database
From the Data menu, select Form...
A dialog box displaying the sheet name appears.
Locate and select the record that you want to delete
HINT: Use the scroll bar to scroll through the records.
Click DELETE
A confirmation dialog box will appear.
Click OK
Select CLOSE on the sheet name dialog box when finished

Searching for a Record

To ensure that you search the entire database, move to either the first or last record of the database (use the scroll bar).

Place the cell pointer within the database
From the Data menu, select Form...
A dialog box displaying the sheet name appears.
Click CRITERIA
Within the appropriate field(s), type the desired search criteria
Click FIND NEXT or FIND PREV
Repeat steps 3-5 as necessary
When you have completed your search, click CLOSE
You are returned to your spreadsheet.

Using the Worksheet Window

If you need to make a few minor changes to the information in your database, sometimes it is quicker to make them through the worksheet window.

Adding a New Record

Scroll to the bottom of your database
Click the field in which you would like to start entering your data
Enter data accordingly
Press [Tab]
OR
Use the arrow keys to move to the next field of the record

Deleting a Record

WARNING: By deleting a row, the entire contents of the row will be deleted.

In your database, scroll to the record that you would like to delete
On the left-hand side of the record, click the record number
From the Edit menu, select Delete
OR
Windows: Right click » select Delete
Macintosh: Press [Control] and click » select Delete
The old record is deleted and rows beneath that row move up.

Searching for a Record

From the Edit menu, select Find...
Windows: The Find and Replace dialog box appears.

Macintosh: The Find dialog box appears
In the Find what text box, type the information that you are searching for
Click FIND NEXT
The record will now be selected in the worksheet window.
Click CLOSE
Windows: The Find and Replace dialog box closes.
Macintosh: The Find dialog box closes.
You are returned to your spreadsheet.
For more information on using the Find and Replace dialog box, refer to Using the Find & Replace Features.