How to create Drop Down list with Data Validation in Excel

In this article, we will learn how to create drop down list using data validation in Microsoft Excel.

Data Validation feature improves the efficiency of data entry in Excel and reduces mistakes and typing errors. You can have restricted entries as per the validation criteria or even create custom rules for what can be entered in the cell. In case of any invalid entry, it shows a message and prevents user to enter the data based on specified condition.

Let’s understand how you can use this feature, with the help of examples.

We have product list in Column A, and we want to make a drop down list of all the products.

Follow the steps given below:-

Select any cell in worksheet
In order to create drop down list, go to Data tab > Data Tools >click on Data Validation

Or we can use the shortcut ALT + D + L
Data Validation dialog box will appear

In Allow list, select “List” option

In Source tab, select the range of product list

Note:-Before clicking on OK, you will notice that by default, the “Ignore blank” and “In-cell Dropdown” check boxes are selected.The first check box “Ignore blank” is used to allow or ignore blanks as valid input in cell if checked. If it is unchecked, Excel will not allow blanks as a valid input, and an error message will pop-up. And the second checked option is “In-cell dropdown”, which is used to display an arrow when you click on the cell. So, if you uncheck this option, the drop down arrow will disappear.

Now, we go to “Input Message” and there we write in title, “Select Product Name” and in the message body, “Select the product name from the list”

Then, we go to “Error Alert”, and by default, the error style is selected as “STOP”. So, in title we write “Invalid Product Name” and then in the error message we write, “Please select the product name from the list”. And, now we click on OK.

Click on OK, we can see input message in yellow caption with drop down arrow

After clicking on drop down menu, we can see all the products, which we selected, are appearing in the drop down list. You can select any of these instead of typing or remembering them

Note:-In case you want to clear the text in the cell which has data validation, you can go to the cell and press delete command. The content will be removed from the cell but will be there in the drop down list

Also, when you are not in the cell where we have applied the data validation, you will see that now it has no drop down arrow. Drop down only appears if you select that cell

In case you want to remove the data validation completely from the cell or range, simply go to the data validation window, and there you see this option “Clear All”
Just click on it and now click on OK. The moment we hit enter, data validation will be removed from the cell

There is another way to create the drop down list. By defining the range name, we can create drop down list. To understand, follow the steps given below:-

So, we have the same list here in range A9 to A15
We select the range, and now we can either go to the name box to give some name to this range or we can use Define Name
Go to Formula tab > Defined Names >then click on Define Name

Now, we write “Product_Name” here in name box, and we have already selected the range, so we click on OK

Now, we again go to Data Validation window and we select “List” for the validation criteria. And, this time, instead of selecting range, we will have the defined name in this source box

Note:- If you remember the name which you have given to the list, you can type it here, preceded by an “equal to” sign or you can press F3. “PASTE NAME” window will appear and you can locate all the names of the range available in the current workbook.

We named the range as “Product_Name”, we click on it and then click on OK
We click on OK, and we get the drop down list in this cell

So, this is how you can create drop down menu using data validation with the help of given examples in Excel.

Video: How to create Drop Down list with Data Validation in Excel

Click on the video link for quick reference to the use of it. Subscribe to our new channel and keep learning with us!

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

John H.
December 22, 2016 at 7:42 pm

I'm working on my family budget. I want to Create spending categories with drop down lists. will this hide and expand the sheet when I want to enter data in one of the categories ?

Reply ↓