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.

image 1


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

image 2


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

image 3


  • In Allow list, select “List” option

image 4


  • In Source tab, select the range of product list

image 5


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”

image 6


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

image 7


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

image 8


  • 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

image 9


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

image 10


  • 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

image 11


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

image 12


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

image 13


  • 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

image 14


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

image 15


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

image 48


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

Users are saying about us...

  1. 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 ?

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube