Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

excel data entry form with drop down list


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.

excel data entry form with drop down list

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

excel data entry form with drop down list

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

excel data entry form with drop down list

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

excel data entry form with drop down list

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


Similar Readings


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.

excel data entry form with drop down list

  • 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

excel data entry form with drop down list

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

excel data entry form with drop down list

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

excel data entry form with drop down list

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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo