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.

Download Practice Workbook

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

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

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.

• 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.

Read More: How to Create an Autofill Form in Excel (Step by Step Guide)

Similar Readings

• How to Automatically Insert Timestamp Data Entries in Excel (5 Methods)
• Record Time of Data Entry in Excel (2 Effective Ways)
• How to Automate Data Entry in Excel (2 Effective Ways)
• Create a Fillable PDF from Excel (2 Suitable Ways)
• How to Create a Printable Form in Excel (2 Easy Ways)

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.

Read More: How to Create an Excel Data Entry Form without a UserForm

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.

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.

Related Articles

• How to Populate an Excel Spreadsheet from a Web Form
• Make a Fillable Form in Excel (5 Suitable Examples)
• How to Create a Data Log in Excel (2 Suitable Ways)
• Restrict Data Entry in Excel Cell (2 Simple Methods)
• How to Create Data Entry Form in Excel VBA (with Easy Steps)
• Create an Excel Data Entry Form That Includes Checkboxes
• How to Create an Order Form in Excel (with Useful Steps)