Show All

Use a data entry form to edit a range or list

A data form is a dialog box that gives you a convenient way to enter or display one complete row of information, or record, in a range or list at one time.

Before you can use a data form to add a record to a new range or list, the range or list must have labels at the top of each column. Microsoft Excel uses these labels to create fields on the form.

1. Click a cell in the range or list you want to add the record to.
2. On the Data menu, click Form.

3. Do one or more of the following:

   Add a record

   1. Click New.
   2. Type the information for the new record.
   3. When you finish typing data, press ENTER to add the record.

   4. When you finish adding records, click Close to add the new record and close the data form.

   Change a record

   1. Find the record you want to change.

      How?

      • To move through records one at a time, use the scroll bar arrows in the dialog box. To move through 10 records at a time, click the scroll bar between the arrows.
      • To move to the next record in the range or list, click Find Next. To move to the previous record in the range or list, click Find Prev.
      • To set search conditions, or comparison criteria, click Criteria, then enter the criteria into the data form. To find records that match the criteria, click Find Next or Find Prev. To return to the data form without searching for records based on the criteria you specified, click Form.

      Wildcard characters you can use as criteria

      The following wildcard characters can be used as comparison criteria for filters, and when searching and replacing content.

      Use	To find
      ? (question mark)	Any single character For example, sm?th finds "smith" and "smyth"
      * (asterisk)	Any number of characters For example, *east finds "Northeast" and "Southeast"
      ~ (tilde) followed by ?, *, or ~	A question mark, asterisk, or tilde For example, fy91~? finds "fy91?"

   2. Change the information in the record.

      • Fields that contain formulas display the results of the formula as a label. The label cannot be changed in the data form.
      • If you change a record that contains a formula, the formula is not calculated until you press ENTER or click Close to update the record.
   3. To move to the next field, press TAB. To move to the previous field, press SHIFT+TAB.
   4. When you finish changing data, press ENTER to update the record and move to the next record.
   5. When you finish changing records, click Close to update the displayed record and close the data form.

   Delete a record

   1. Find the record you want to delete.

      • To move through records one at a time, use the scroll bar arrows in the dialog box. To move through 10 records at a time, click the scroll bar between the arrows.
      • To move to the next record in the range or list, click Find Next. To move to the previous record in the range or list, click Find Prev.
      • To set search conditions, or comparison criteria, click Criteria, then enter the criteria into the data form. To find records that match the criteria, click Find Next or Find Prev. To return to the data form without searching for records based on the criteria you specified, click Form.

      Wildcard characters you can use as criteria

      The following wildcard characters can be used as comparison criteria for filters, and when searching and replacing content.

      Use	To find
      ? (question mark)	Any single character For example, sm?th finds "smith" and "smyth"
      * (asterisk)	Any number of characters For example, *east finds "Northeast" and "Southeast"
      ~ (tilde) followed by ?, *, or ~	A question mark, asterisk, or tilde For example, fy91~? finds "fy91?"

   2. Click Delete.

Notes

• Data forms can display a maximum of 32 fields at one time.
• While you are adding or changing a record, you can undo changes by clicking Restore as long as the record is the active record in the data form.