How to Create Data Entry Form With Drop-Down List in Excel

This tutorial will demonstrate how to create a data entry form from a table with a drop-down list in Excel.

 

 

The automatic form feature of Excel is often overlooked as you cannot find the command to create a form on any of the ribbon tabs available by default. If data entry is part of your day-to-day routine in Excel, then creating forms to help you enter the data can be very convenient. Data needs to be in a table format in order to use the form feature of Excel.

Add Form Button to Quick Access Toolbar

1. To create a form automatically, first add the form button to the Quick Access Toolbar (or to one of the tabs on the Ribbon).

2. Click on the small drop-down button on the right side of the Quick Access Toolbar in the Excel title bar, and then click on More Commands.

 

 

3. Then, (1) Select All Commands from the Choose Commands from drop down and then (2) scroll down to find Form. Click (3) the Add button to add the command to the Quick Access Toolbar.

 

 

4. Click OK to add the form button to the toolbar.

 

 

Create a Form

1. Select any cell within the table data, and then click on the form button in the Quick Access Toolbar. The form will automatically be shown.

 

 

2. You can use the Find Prev and Find Next buttons to move through your entries, as well as using the New button to create a new entry in your table, and the Delete button to remove an entry.

 

Restrict Form Data Entry

If your table contains a drop-down list, the drop-down list doesn’t show up in the form. However, you will only be able to enter data into that field in the form from that drop-down list.

Let’s have a look at the drop-down list in the table.

 

 

If you are entering data into the table, you are restricted by the Data Validation drop-down list in selecting a salesperson. The typed-in name has to be on the provided list.

 

 

If you try and type in a salespersons name that is not on the list, and then click Close, you will get a data validation error.

So, while you cannot see the drop-down list in the data form, any data validation restrictions you have applied in the table, will still apply in the drop-down list.