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.

datavalblanks01

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!

datavalblanks02

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.

datavalblanks03

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.

datavalblanks04

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.

______________

Excel Drop Down List Allows Invalid Entries http://blog.contextures.com/

Excel Drop Down List Allows Invalid Entries http://blog.contextures.com/

_________________