Add, edit, find, and delete rows by using a data form

When a row is long and requires repeated scrolling, consider using a data form to add, edit and delete rows.

What do you want to do?



Learn more about data forms

A data form provides a convenient means to enter or display one complete row of information in a range or list without scrolling horizontally. You may find that a data form can make data entry easier than typing across the columns when you have a wide range with more columns than will fit on the screen at one time. Use a data form when a simple form listing the columns is sufficient and you don't need more sophisticated or custom form features.

Example of a typical data form

Microsoft Office Excel can automatically generate a built-in data form (data form: A dialog box that displays one complete record at a time. You can use data forms to add, change, locate, and delete records.) for your range or list. The data form displays all of your column headers as labels in a single dialog box. Each label has a blank text box that you can use to type data for each column up to a maximum of 32 columns. You can enter new rows, find rows by navigating, or based on cell contents, update rows and delete rows. If a cell contains a formula, the formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) result is displayed in the data form, but you cannot change the formula by using the data form.

 Note   You can't print a data form. A data form is a modal dialog box, which means that you can't use the Excel Print command or button until you close the data form. Alternatively, you can use the Windows Print Screen key to make an image of the form, and then copy it to Microsoft Paint or another program. For more information, see the help topic, Copy the window or screen contents.

Top of Page Top of Page

Create a data form

  1. If necessary, add a column header to each column in the range or list. Excel uses these column headers to create labels for each field on the form.
  2. Click a cell in the range or list to which you want to add the row.
  3. On the Data menu, click Form.

ShowI receive a message that reads "Too many fields in the data form".

You can only create a data form for a range or list with 32 columns or less. If you can, reduce the number of columns.

Top of Page Top of Page

Use a data form

Using a data form, you can add, find, change, and delete rows.

Add a row

  1. Click New.
  2. Type the data for the new row.

To move to the next field, press TAB. To move to the previous field, press SHIFT+TAB.

  1. When you have finished typing data, press ENTER to add the row to the bottom of the range or list.

ShowI receive the message "Cannot extend list or database".

If you add a row to a list or range through a data form, Excel expands the list downward. If expanding the list overwrites data, Excel displays the message "Cannot extend list or database". Rearrange the data on your worksheet so that the list or range can expand from the last row down.

 Note   Before you press ENTER, you can undo any changes by clicking Restore.

Top of Page Top of Page

Find a row by navigating

Do one or more of the following:

  • To move through rows one at a time, use the scroll bar arrows in the dialog box. To move through 10 rows at a time, click the scroll bar between the arrows.
  • To move to the next row in the range or list, click Find Next. To move to the previous row in the range or list, click Find Prev.

Top of Page Top of Page

Find a row by entering search criteria

  1. Click Criteria and then enter the comparison criteria (comparison criteria: A set of search conditions that is used to find data. Comparison criteria can be a series of characters that you want to match, such as "Northwind Traders," or an expression, such as ">300.") into the data form.

All items that begin with the comparison criteria are filtered. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis." To find text values that share some characters but not others, use a wildcard character.

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?"
  1. To find rows that match the criteria, click Find Next or Find Prev.
  2. To return to the data form so that you can add, change, or delete rows, click Form.

Top of Page Top of Page

Change a row

1. Find the row that you want to change.

ShowHow to find a row

ShowFind a row by navigating

Do one or more of the following:

  • To move through rows one at a time, use the scroll bar arrows in the dialog box. To move through 10 rows at a time, click the scroll bar between the arrows.
  • To move to the next row in the range or list, click Find Next. To move to the previous row in the range or list, click Find Prev.

ShowFind a row by entering search criteria

  1. Click Criteria and then enter the comparison criteria (comparison criteria: A set of search conditions that is used to find data. Comparison criteria can be a series of characters that you want to match, such as "Northwind Traders," or an expression, such as ">300.") into the data form.

All items that begin with the comparison criteria are filtered. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis." To find text values that share some characters but not others, use a wildcard character.

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?"
  1. To find rows that match the criteria, click Find Next or Find Prev.
  2. To return to the data form so that you can add, change, or delete rows, click Form.

2. Change the data in the row.

To move to the next field, press TAB. To move to the previous field, press SHIFT+TAB.

3. When you finish changing data, press ENTER to update the row.

Excel automatically moves to the next row.

 Note   Before you press ENTER, you can undo any changes by clicking Restore.

Top of Page Top of Page

Delete a row

1. Find the row that you want to delete.

ShowHow to find a row

ShowFind a row by navigating

Do one or more of the following:

  • To move through rows one at a time, use the scroll bar arrows in the dialog box. To move through 10 rows at a time, click the scroll bar between the arrows.
  • To move to the next row in the range or list, click Find Next. To move to the previous row in the range or list, click Find Prev.

ShowFind a row by entering search criteria

  1. Click Criteria and then enter the comparison criteria (comparison criteria: A set of search conditions that is used to find data. Comparison criteria can be a series of characters that you want to match, such as "Northwind Traders," or an expression, such as ">300.") into the data form.

All items that begin with the comparison criteria are filtered. For example, if you type the text Dav as a criterion, Excel finds "Davolio," "David," and "Davis." To find text values that share some characters but not others, use a wildcard character.

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?"
  1. To find rows that match the criteria, click Find Next or Find Prev.
  2. To return to the data form so that you can add, change, or delete rows, click Form.

2. Click Delete.

 Warning   Excel prompts you to confirm the operation. You cannot undo a row deletion once you confirm it.

Top of Page Top of Page

Delete a data form

To remove the data form and return to the worksheet, click Close.

Top of Page Top of Page

 
 
Applies to:
Excel 2003