Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

Here’s an example of using Data Validation to NOT allow a particular data item entry.

Suppose you want to insure that anything can be entered into a cell, EXCEPT for certain items you specify. In the pictured example, an entry of Jewelry, Car, Boat, or House would not be allowed.

The following steps show how this can be done.

Step 1 of 5
Select the cell(s) where you want your data entry validated.

Step 2 of 5
From your keyboard, press ALT+D+L to show the Data Validation dialog box.

Step 3 of 5
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 4 of 5
In the Formula field, enter the formula that is relative to your selected range.
In this example it is just one cell being data validated: =ISERROR(MATCH(A2,C2:C4,0))

Step 5 of 5
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.

This entry was posted in Tom's Tutorials for Excel and tagged , , . Bookmark the permalink.

Leave a Reply