Invalid Entries Allowed in Data Validation

Why does Excel allow invalid entries sometimes? You set up a data validation drop down list, so you can select valid items from a drop down list.

datavalinvalid01

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

datavalinvalid02

It’s a great plan, but people are sometimes typing invalid entries in the month column, and the data validation isn’t stopping them.

datavalinvalid03

What went wrong, 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

__________

Save

You may also like...

1 Response

  1. Md Ibrahim Khalil says:

    Most Helpful post

    Thanks

Leave a Reply

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