How to Add Records to Data Lists via the Data Form in Excel 2013 - dummies

How to Add Records to Data Lists via the Data Form in Excel 2013

By Greg Harvey

Excel 2013 allows you to add records to data lists by using the data form. The first time you click the custom Form button you added to the Quick Access toolbar, Excel analyzes the row of field names and entries for the first record and creates a data form.

This data form lists the field names down the left side of the form with the entries for the first record in the appropriate text boxes next to them. You can see the data form for the new Employee Data database; it looks kind of like a customized dialog box.


The data form includes the entries you made in the first record. The data form also contains a series of buttons that you use to add, delete, or find specific records. Right above the first button, the data form lists the number of the record followed by the total number of records. When creating new entries it will display New Record above this button instead of the record number.

All the formatting that you assign to the particular entries in the first record is applied automatically to those fields in subsequent records you enter and is used in the data form.

For example, if your data list contains a telephone field, you need to enter only the ten digits of the phone number in the Telephone field of the data form if the initial telephone number entry is formatted in the first record with the Special Phone Number format.

That way, Excel takes a new entry in the Telephone file, such as 3075550045, for example, and automatically formats it so that it appears as (307) 555-0045 in the appropriate cell of the data list.

The process for adding records to a data list with the data form is simple. When you click the New button, Excel displays a blank data form (marked New Record at the right side of the data form), which you get to fill in.

After you enter the information for the first field, press the Tab key to advance to the next field in the record.

Whoa! Don’t press the Enter key to advance to the next field in a record. If you do, you’ll insert the new, incomplete record into the database.

Continue entering information for each field and pressing Tab to go to the next field in the database.

  • If you notice that you’ve made an error and want to edit an entry in a field you already passed, press Shift+Tab to return to that field.

  • To replace the entry, just start typing.

  • To edit some of the characters in the field, press → or click the I-beam pointer in the entry to locate the insertion point; then edit the entry from there.

When entering information in a particular field, you can copy the entry made in that field from the previous record by pressing Ctrl+’ (apostrophe). Press Ctrl+’, for example, to carry forward the same entry in the State field of each new record when entering a series of records for people who all live in the same state.

When entering dates in a date field, use a consistent date format that Excel knows. (For example, enter something like 7/21/98.) When entering zip codes that sometimes use leading zeros that you don’t want to disappear from the entry (such as zip code 00102), format the first field entry with the Special Zip Code number format.

In the case of other numbers that use leading zeros, you can format it by using the Text format or put an ’ (apostrophe) before the first 0. The apostrophe tells Excel to treat the number like a text label but doesn’t show up in the database itself. (The only place you can see the apostrophe is on the Formula bar when the cell cursor is in the cell.)

Press the ↓ key when you’ve entered all the information for the new record. Or, instead of the ↓ key, you can press Enter or click the New button. Excel inserts the new record as the last record in the database in the worksheet and displays a new blank data form in which you can enter the next record.


When you finish adding records to the database, press the Esc key or click the Close button at the bottom of the dialog box to close the data form.