It is great to create a data entry form in excel that helps save a lot of time and reduce mistakes when entering data into a worksheet. But you can also go a step further to validate the data by adding a drop down list to data entry form to make it even more error-free.
The drop down list restricts the user to a pre-defined list in the data entry form which limits the entries to that field. If you go to my earlier post, you will see an ultimate guide on how to create a drop down list in Excel.
To do this:
1. Open a new worksheet, different from the sheet with data table.
2. In the new worksheet, enter the list of possible options for that column. If there are more than one fields (column) that needs a drop-down list, enter the lists in the different columns in the same worksheet.
This list identifies all the possible options for a particular field (column) in the data table worksheet. Each record will have an ‘Item’ value which is restricted to only the options in the list.
This means that the user can only enter items in this list.
Note: If you delete the sheet where the list is created, the restriction will no longer work.
3. Go back to the worksheet that has the data table and select the column that has the dropdown list. In this case, it is column D.
4. Go to Data tab, and click on ‘Data Validation’ to display the Data Validation dialog box
5. Click on ‘Settings’ tab. In the ‘Allow’ field, click on the drop-down arrow and select ‘List’
6. Confirm that the ‘In-cell dropdown’ box is checked.
7. Place your cursor in the ‘Source’ field, then go to the worksheet that has the list and select the cell range of the list. In this case, it’s cells A1:A4 as shown below:
8. Click ok.
Now, if you click column D in a new row in the data table, you will see the list of items available for data entry for that particular field (column D).
9. Go to the data table to display the data entry form following the steps in how to create a data form entry.
The field (column) connected to the list of items will now allow the entries from the list. If a wrong item that is not in the list is entered into the field, it will reject the entry and Excel will display an error message as shown below:
Note:
In newer versions of Excel, 2019 and upwards, the drop-down list will show in the data entry form. In the older versions of Excel, 2016 and below, it does not show on the form, but the field will be restricted to the items in the list.