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/

_________________

You may also like...

5 Responses

  1. Rudra says:

    Good information Debra,
    I have been experiencing this since long time and wasn’t able to fix. Thanks for sharing this useful tip.

    Now no one can become manager in my company other than me……..:)

    Regards
    Rudra

  2. MF says:

    Hi Debra,

    I ran into similar situation before; but I actually want to input anything other than the drop-down list.

    For sharing, here’s my post of using the trick in the opposite way:
    http://wmfexcel.com/2015/01/10/in-cell-dropdown-by-data-validation-allow-input-not-from-a-list/

    Cheers,

  3. Stephaie says:

    What if I do not want users to leave a cell blank. How do I make sure they fill in a particular cell.

  4. Lou Barletta says:

    If you define a Data Validation List source range as a Table, then when the Table expands or contracts (adding or deleting entries), the Data Validation automatically adjusts to the new Table cells. Even though the cell reference is defined as a range of cells (not the “structured table reference” notation, with items in square brackets), Excel is smart enough to associate the Table’s current content with the Data Validation list. And this holds true not just for Data Validation, but for a PivotTable source as well. I’ve tested this in both Excel 2010 and 2013.

Leave a Reply to Lou Barletta Cancel reply

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