Use the Built-In Data Entry Form in Excel

Excel has a built-in data entry form that most people never find. It shows one record at a time in a small dialog box, with a labelled box for each column, so you can add, find, edit and delete rows without scrolling across a wide sheet. This guide shows how to switch on the data entry form in Excel, how to use each button and where its limits are. It is for anyone who types lists by hand, such as contacts, orders or stock counts.

What is the Excel data entry form?#

The data form is a dialog box that Excel builds from your column headers. Each header becomes a label, and next to it is a box for that column's value. You see one row, called a record, at a time.

It helps when:

• Your list has many columns, and you lose your place scrolling sideways.
• Someone new is entering data and needs clear labels for each value.
• You want to find and fix one record without filtering the whole list.

The form works on any list with one header row and no blank rows inside it. It is a desktop Excel feature, so Excel for the web doesn't have it.

How to add the Form button in Excel#

The Form command isn't on the ribbon, so add it to the Quick Access Toolbar once. After that, it is always one click away. On Windows:

1. Go to File > Options > Quick Access Toolbar.
2. In Choose commands from, select Commands Not in the Ribbon.
3. Scroll to Form…, select it and click Add.
4. Click OK.

A small form icon now appears in the Quick Access Toolbar. If you don't see the toolbar, right-click the ribbon and choose Show Quick Access Toolbar.

Newer versions of Excel for Mac also include a data form. If your version has it, you'll find it under the Data menu in the menu bar.

How to set up the list for the form#

The form reads your headers to build its fields, so a clean list matters. Here is a contact list that works well:

Follow these rules:

• One header row, with a short, clear name in every column.
• No blank rows or columns inside the list. The form stops at the first gap.
• One kind of value per column, so each field means one thing.

Turning the list into an Excel table (Ctrl+T, or ⌘T on a Mac) keeps the formatting and formulas consistent as rows are added. See how Excel tables work. The lesson on cells, rows and columns covers the basic layout every list should follow.

How to add a new record with the form#

Click any cell inside the list, then click the Form button. The dialog opens on the first record. To add a new one:

1. Click New. The boxes clear.
2. Type the value for the first field and press Tab to move to the next one. Shift+Tab moves back.
3. Press Enter to save the record. Excel adds it to the next empty row below the list.
4. Type the next record, or click Close when you are done.

If a column holds a formula, the form shows its result as plain text, not as a box, and you can't edit it in the form. Check that column after adding rows to make sure the formula covers them.

How to find and edit a record#

Use Criteria to search the list without filtering it. Click Criteria, type what you are looking for in one or more boxes, and click Find Next. For example:

• Type Marketing in Department to step through the Marketing staff.
• Type >103 in ID to find records with an ID above 103.
• Type L* in Name to find names starting with L. The * means "any characters".

When the record you want appears, change the value in its box and press Enter to save it. If you make a mistake, click Restore before you press Enter or move to another record, and the old value comes back. Find Prev and Find Next move through the matches, and the scroll bar moves through every record.

To change the same value in many rows at once, Find and Replace is faster than the form.

How to delete a record#

Find the record, click Delete and confirm. Excel removes the whole row and moves the rows below it up.

Be careful: deleting through the form is permanent, and Ctrl+Z (Mac: ⌘Z) can't bring the row back. Save the workbook first if you are unsure. To delete rows directly on the sheet, see how to insert and delete rows and columns.

Limits of the data entry form#

The form is simple, and that brings some limits:

After a data entry session, check the results. Filter the list to spot blanks, or count them with =COUNTBLANK(D2:D7). In the example contact list, two emails are missing, so the result is 2. The lesson on filtering a table shows how to find those rows fast.

If you are preparing for a typing-and-accuracy assessment, read what an Excel data entry test checks.

Practise it#

A form helps you type, but clean lists and quick checks are what employers test. Practise filtering a table in a live spreadsheet: you work on real data and each step is checked instantly. Then try the exercise on this page and count the missing emails with COUNTBLANK.