Why are invalid entries allowed in Data Validation sometimes?
Have you ever set up a data validation drop down list, so you can select valid items from a drop down list. But instead, Excel allows people to type anything they want into that cell?
See why that happens, and how you can prevent the invalid entry problem.
Select Month From Drop Down List
For example, select a season in column B, and then choose from a list of months for that season, in column C
Data Validation Error Message
After you select a season, if you type an invalid month name in the adjacent cell, you’ll see a warning message.
Invalid Entries Allowed in Data Validation
However, despite the data validation rules, people sometimes type invalid entries in the month column, and the Excel data validation doesn’t stop them.
What went wrong with the data validation setup, and how can you fix it?
Problem With Blanks
In this example, the Month column has dependent data validation, based on the season that was selected in column B.
If the Data Validation Ignore Blank setting is turned on, then you’ll be able to enter any value in a Month cell, if the Season cell is blank.
Prevent the Problem
To prevent those invalid entries:
- Select the Data Validation cells
- On the Ribbon’s Data tab, click Data Validation
- In the Data Validation dialog box, on the Settings tab, remove the check mark for Ignore Blank, then click OK
Invalid Entries Are Blocked
With the Ignore Blanks setting changed, if the Season cell is blank, you won’t be able to enter anything in the Month column.
Blank Cells in Source List
You might see this problem with invalid entries in other situations too.
For example, if the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message.
To prevent this, turn off the Ignore Blank setting, as described above.
There are more instructions, and screen shots here.
Error Alerts
If the data validation Error Alert is turned off, users will be able to type any entry, without receiving an error message. To turn the Error Alert on:
- Select the Data Validation cells
- On the Ribbon’s Data tab, click Data Validation
- In the Data Validation dialog box, on the Error Alert tab, add a check mark for Show Error Alert After Invalid Data is Entered, then click OK
__________
Most Helpful post
Thanks
I would like to be able to use a list for the dropdown menu but also allow the entry of items not included in the list, sort of an “optional” list or a list of suggestions. Is that possible?
Chip:
You can indeed allow any data to be entered in a cell that contains data validation. If you uncheck “Show Error Alert After Invalid Data is Entered” on the “Error Alert” tab, users can enter any data they choose (this is described in the blog above). You could also leave “Show Error Alert After Invalid Data is Entered” checked and choose either the “Warning” or “Information” alert style without preventing users from entering “invalid data” (i.e., data that is not contained in your predefined list). These alert styles allow any data to be entered after notifying the user. In older versions of Excel, you could simply check a box that said something like “Allow Invalid Entries”, but that unfortunately is no longer an option.
I hope this helps!
Thanks Brian. I’ve adjusted the settings as you’ve suggested in order to allow the drop down lists to be edited as well as free text to be added below the lists. However, this is causing duplicates. The selected lists appear in both the unedited and edited forms. Are you aware of a solution to this?
I’ve found another odd scenario – if the Source for a List selection is an INDIRECT, even if it points to a valid range of values for the list, any data can be entered (although the dropdown still presents the correct set of values). Again turning off ‘Ignore blank’ restores the reporting of invalid entries.
Thanks, Geoff!