Home > Data > Data Entry > Data Form

Excel Built-In Data Entry Form

Debra Dalgleish - Contextures

There's a hidden data entry form in Excel, that you can use to enter data, edit records, or delete old data. In my video below, you'll see the shortcut to open the form, and tips for using it.

Excel's built-in Data Form

Excel Hidden Data Entry Form

Use Excel's built-in Data Form to make it easier to enter data in a list.

Note: This Excel data entry form will display a maximum of 32 fields.

Excel's built-in Data Form

Video: Enter Data With Built-In Form

Watch this short video, to see how the built-in Excel data form works. There are written instructions below the video.

Open Built-In Data Form

The Data Form command isn't on the Excel Ribbon, so you can:

--A) use a keyboard shortcut to open it

--B) or add its command to the Quick Access Toolbar (QAT).

A) Use Keyboard Shortcut

To open the data form from the keyboard:.

  1. Press Alt + D, then type o (lower case letter O)
Old Menu Command

This keyboard shortcut is based on the old Excel 2003 menu command to open the data form.

In the screen shot below, the hot keys are underlined, and you could press those keys to activate that menu or command:

  • the D is underlined in the Data menu
  • the o is underlined in the Form command

menu command in Excel 2003

B) Add QAT button

Follow these steps to add the Data Form command to the Quick Access Toolbar (QAT):

  1. Right-click the QAT, and click Customize Quick Access Toolbar
  2. From the drop down list, select All Commands
  3. Scroll down and click on Form..., then click Add, to put it on the QAT
  4. Close the window, then click the Form button on the QAT

add data form to QAT

Use Excel Data Form

After you open the Data Form, use the following steps to add, view, and edit the records

  • To add a record, click New, and enter the data
  • To move through the records, click the Up arrow and Down arrow keys, or use the scroll bar on the Data Form.
  • To edit a record, move to that record, and change the data in the entry boxes.
    • While editing a record, you can click the Restore button, to restore the current values.
    • To complete the edit, click Close, or move to another record. NOTE:
    • After editing a record, if you click Find Prev or Find Next, the changes are not saved.
  • To delete a record, scroll to that record, then click the Delete button
  • To find a specific records, or the first record that meets your criteria, click the Criteria button, and enter the criteria, then click Find Prev or Find Next.

formula fields with no text box in data form

Formula Fields

Fields which contain a formula, such as Total in the Data form shown above, will not have a text box.

The formula will be entered and calculated automatically.

Data Form Quirks

Here are a few quirks that you'll encounter, when using the built-in Excel data entry form.

  • Data Validation lists, and other data validation settings, are ignored.
  • It doesn't matter which cell in the table is selected -- the Data Form will open at the first record.
  • If there is a range named Database on the worksheet, the Data Form will always show that list, even you have selected a cell in a different table.
  • The width of the boxes in the Data Form is connected to the widest column in the underlying table. If you widen that column, the Data Form will adjust automatically.
  • If numeric columns are too narrow, and some cells show number signs, those number signs might be shown in the Data Form, even if its data entry boxes are wide enough to show the number. And unlike the worksheet, where you can point to a cell with number signs, and see a popup with the value, there's no such feature in the Data Form.

number signs from worksheet cells in data form

Get the Sample File

Get the sample file to test the built-in data entry form, to follow along with the video. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

Data Entry Tips

Worksheet Data Entry Form

Worksheet Data Update Form

User Form Data Entry Basic

User Form Data Entry ComboBoxes

 

 

Last updated: April 1, 2024 11:25 AM