Microsoft Office Tutorials and References
In Depth Information
Using the Excel Built-In Data Entry Form
When the Data Form dialog box appears, the first record (if any) in the list is displayed. Notice
the indicator in the upper right corner of the dialog box; this indicator tells you which record is
selected and the total number of records in the list.
To enter a new record, click the New button to clear the fields. Then you can enter the new
information into the appropriate fields. Press Tab or Shift+Tab to move among the fields. When you
click the New (or Close) button, the data that you entered is appended to the bottom of the list.
You also can press Enter, which is equivalent to clicking the New button. If your list contains any
formulas, they’re also entered automatically into the new record in the list for you.
If your list is named Database, Excel automatically extends the range definition to
include the new row or rows that you add to the list by using the Data Form dialog
box. Note that this method works only if the list has the name Database; no other
name works. Use the Formulas➜Defined Names➜Define Name command to name the
range. If your list is in a table (created using Insert
Table), there’s no need
to name the range. The table will be expanded automatically when you add new data.
You can use the Data Form dialog box for more than just data entry. You can edit existing data
in the list, view data one record at a time, delete records, and display records that meet certain
The dialog box contains a number of additional buttons:
h Delete: Deletes the displayed record.
h Restore: Restores any information that you edited. You must click this button before you
click the New button.
h Find Prev: Displays the previous record in the list. If you entered a criterion, this button
displays the previous record that matches the criterion.
h Find Next: Displays the next record in the list. If you entered a criterion, this button
displays the next record that matches the criterion.
h Criteria: Clears the fields and lets you enter a criterion on which to search for records.
For example, to locate records that have a List Price less than $200,000, enter <200000
into the List Price field. Then you can use the Find Next and Find Prev buttons to display
the qualifying records.
h Close: Closes the dialog box (and enters any data that you were entering).
If you like the idea of using a data entry dialog box — but don’t care much for the
Excel implementation — try out my Enhanced Data Form. This add-in, which provides
a much more versatile data entry form, is available for downloading at my Web site: