How to Create Data Entry Form with Drop Down List in Excel (2 Methods)

In Microsoft Excel, you can create various forms like data entry, calculator, etc. These types of forms help you to enter your data with ease. It also saves you a lot of time. Another useful feature of Excel is the drop down list. Typing limited values, again and again, can make the process hectic. But with the drop-down list, you can easily select values. Today, In this article, we’ll get to learn how to do a data entry form with a drop-down list in Excel effectively with appropriate illustrations.

Create Data Entry Form with Drop Down List in Excel: 2 Suitable Ways

Let’s assume we have an Excel large worksheet that contains the information about several students of Armani School. The name of the students, the Identification Number, and the securing marks in Mathematics are given in Columns B, C, and D respectively. We can easily create a drop-down list for the data entry form in Excel by using the IF Function, and so on. Here’s an overview of the dataset for today’s task.

1. Use Data Validation Command to Create Data Entry Form with Drop Down List in Excel

In this portion, from our dataset, we will create a drop-down list for the data entry form. This is an easy and time-saving task also. We will use the IF function to identify a student, whether he/she passes or fails. Let’s follow the instructions below to create a drop-down list for the data entry form!

Step 1:

First, select a cell. We will select E5 for the convenience of our work.

After selecting cell E5, write down the below function.

=IF(D5>=50,"Pass","Fail")

Where D5>=50 is the logical_test of the IF function. If the mark is greater than or equal to 50, he /she will pass or not fail.

Hence, simply press ENTER on your keyboard. As a result, you will get “Pass” as the return of the IF function.

Step 2:

Further, autoFill the IF function to the rest of the cells in Column E.

Step 3:

Now, we will create a drop down list. To do that, firstly, select a cell, and then from your Data tab, go to,

Data → Data Tools → Data Validation → Data Validation

Instantly, a Data Validation dialog box will appear in front of you. From the Data Validation dialog box, firstly, select the Settings tab. Secondly, select the List option from the Allow drop-down list. Thirdly, type =$D$5:$D$11 in the typing box named Source. At last, press OK.

As a result, you will be able to create a drop-down list which has been given in the below screenshot.

If we change the math mark of John from the drop-down list, the comments will change automatically. Let’s say, we will select 44 from the drop-down list, and the comments will change automatically which has been given in the below screenshot.

Similarly, you can create a drop-down list to the rest of the cell in column D.

2. Apply Quick Access Toolbar Command to Create Data Entry Form with Drop Down List in Excel

Now, we will use the Quick Access Toolbar command. Let’s follow the instructions below to create a drop-down list for the data entry form!

Step 1:

First of all, select the File option.

After that, a window will appear in front of you. From that window, select Options.

As a result, an Excel Options dialog box will appear in front of you. From the Excel Options dialog box, firstly, select the Quick Access Toolbar Secondly, select the Form option under the drop-down list named Choose commands from. Thirdly, press the Add option. At last, press OK.

Excel Options →Quick Access Toolbar → Form → Add → OK

Further, you will see the Form sign in the ribbon bar.

Hence, press on the Form sign in the ribbon bar. As a result, a data entry form named Data Entry Form with Drop Down will pop up. From that data entry form, you can change the value by pressing the Find Next option.

After pressing on the Find Next option, you will be able to change the data entry form which has been given in the below screenshot.

Things to Remember

👉 #DIV/0! error happens when a value divided by zero(0) or the cell reference is blank.

👉 In Microsoft 365, Excel will show the #Value! Error if you don’t select the proper dimension. The #Value! error occurs when any of the elements of the matrices is not a number.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Data Entry Form with Drop Down List.xlsx

Conclusion

I hope all of the suitable methods mentioned above to create a drop-down list for the data entry form will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

<< Go Back to Data Entry Form in Excel | Data Entry in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!