Excel Drop Down List Allows Invalid Entries
Drop down lists make it easier to enter data on a worksheet, and they help ensure that only valid items will be entered in the cell.
For example, in the screen shot below, the Manager column has a drop down list with 5 names. Only those names should be entered in the column.
However, if you type “Bill” in the cell (a name that isn’t on the list), it’s accepted in the cell, with no error alert. That could lead to problems – suddenly, anyone can be a manager!
Why It Is Allowed
There are a couple of reasons why invalid entries might be allowed in a cell with a drop down list. In this example, the cause is a blank cell in the named ranged that contains the manager names.
For some reason, this isn’t a problem if a range reference, such as “$A$2:A$20”, is used for the list, instead of a named range.
To fix the problem, you can turn off the Ignore blank setting in the data validation dialog box. The instructions for that are also in the video below, if you’d rather watch, than read.
Video: Excel Drop Down List Allows Invalid Entries
Watch this video to see how to set up the drop down list, and enter an invalid name. Then, make a simple change to the settings, to fix the problem.
Download the Sample File
Visit the Data Validation Tips and Quirks page on my Contextures site, to download the sample file for this video.