This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2003/2004

Database Basics

This document presents the basics of working with an Excel database.

return to topTips 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 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.

return to topCreating a Database

Excel recognizes lists of information as a database when the database functions are used. 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

return to topUsing 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 thus 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 the Form

NOTE: You cannot start from a blank worksheet. The first row must be completed with the column labels (i.e., the field names).

  1. Place the cell pointer within the database

  2. From the Data menu, select Form...
    A dialog box displaying the sheet name appears.
    Sheet Name dialog box

Using Form Options

Adding a New Record

  1. Access the form

  2. Click NEW
    The form will be cleared and ready for you to enter data.

  3. In each field, type the desired information 
    NOTE: To move between fields, press [Tab]
    WARNING: If you press [Enter] or [return] after you have typed the field information, the data will be added to your database and you will automatically go to a blank form to start a new record.

  4. When you have entered the last record, click CLOSE
    The new records are added to the end of your database.

Deleting a Record

  1. Access the form

  2. To select the existing record you want to delete, click and hold the scroll bar dragging up or down
    OR
    Click FIND PREV or FIND NEXT

  3. Click DELETE
    A confirmation dialog box appears.

  4. Click OK

  5. Click CLOSE

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).

  1. Access the form

  2. Click CRITERIA

  3. Within the appropriate field(s), type the desired search criteria 

  4. Click FIND NEXT or FIND PREV

  5. Repeat steps 3-4 as necessary

  6. Click CLOSE

return to topUsing 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

  1. Scroll to the bottom of your database

  2. Click the field in which you would like to start entering your data

  3. Enter data accordingly

  4. 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.

  1. In your database, scroll to the record that you would like to delete

  2. On the left-hand side of the record, click the record number

  3. From the Edit menu, select Delete
    OR
     Windows: Right click » Delete
    Macintosh: Press [control] + click the record number » Delete
    The old record is deleted and rows beneath that row move up.

Searching for a Record

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

  2. In the Find what text box, type the information that you are searching for 

  3. Click FIND NEXT
    The record will now be selected in the worksheet window.

  4. When finished, click CLOSE
    NOTE: For more information on using the Find and Replace dialog box, refer to Using the Find & Replace Features.

Excellence. Our Measure. Our Motto. Our Goal.