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:-
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.
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
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:-
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.
So, this is how you can create drop down menu using data validation with the help of given examples 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!
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 firstname.lastname@example.org
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.