Invalid Entries Allowed in Data Validation

Invalid Entries Allowed in Data Validation

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, and Excel allows people to type anything they want into that cell? See why that happens, and how you can prevent the problem.


For example, select a season in column B, and then choose from a list of months for that season, in column C

datavalinvalid01

After you select a season, if you type an invalid month name in the adjacent cell, you’ll see a warning message.

datavalinvalid02

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.

datavalinvalid03

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 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.

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

datavalinvalid04

With the Ignore Blanks setting changed, if the Season cell is blank, you won’t be able to enter anything in the Month column.

datavalinvalid05

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

datavalinvalid06
__________