How to create Drop down data validation list in Excel?

1. Add Drop down list with Excel Data validation?

A data validation list in Excel enable users to choose only predefined values from a drop down option. If user attempts to type any other value, Excel will not allow it & will display error message. To add drop down data list to Excel Sheet, follow these steps:

1. Select cell.
2. Click on ‘Data’ Menu.
3. Then ‘Data Validation’  in Data Tools tab.
4. Select ‘List’ under Validation Criteria ‘Allow’ field.
5. Enter values in ‘Source Field’
   • Enter delimited list as value1, value2, value3 or
   • Choose Cell Range that has the list for drop down values.
6. Click Ok.

Now, you can see a drop down box in the selected worksheet cell. If you click on the drop down list, the values you entered or data from source will be listed one by one. Users are allowed to only enter or choose any of the values in the list. If they type or copy-paste any other value in a cell with validation rule, then Excel will throw an error message asking to type valid value. Also Read: How to create this drop down list in Excel using VBA code?

2. Add/Delete Item from Data Validation List

To remove any item from the drop down, the steps are same as above. If you have entered the list with comma delimited, then add or remove item in the source field box. If it is range, then you can change or delete the value in the source range. Once it is changed, the value in drop down list will be changed. But, this will not automatically modify the values already chosen by the user. Neither will Excel throw any error. It is users responsibility to modify the already values one by one. Or if there are numerous values to be changed, it is advised to write a macro to change each incorrect value in the drop down list.