Check data entry for invalid entries
To make Excel check data entry for invalid entries, follow these steps:
1. Select the cell or range you want Excel to check.
2. On the Data tab, in the Data Tools group, click the Data Validation list and choose Data Validation...:
3. In the Data Validation dialog box, on the Settings tab, specify the validation criteria to use.
Select the appropriate type in the Allow drop-down list, and then set parameters accordingly.
- Any Value Accepts any input (Excel's default setting for cells). This setting effectively turns off validation, so you normally select it only when you need to remove validation from a cell or range. But you can also use this setting to display an informational message for a cell or range. To do so, enter the title and message on the Input Message tab, as discussed in step 5.
- Whole Number Lets you specify a comparison operator and appropriate values. The user must not enter a decimal point.
The validation criteria use these self-explanatory comparison operators: Between, Not Between, Equal To, Not Equal To, Greater Than, Less Than, Greater Than or Equal To, and Less Than or Equal To.
- Decimal Lets you specify a comparison operator and appropriate values. The user must include a decimal point and at least one decimal place (even if it's .0).
- List Lets you specify a list of valid entries for the cell. You can type in entries in the Source text box, separating them with commas, but the best form of source is a range on a worksheet in this workbook. If you hide the worksheet, the users won't trip over it. Usually, you'll want to select the In-Cell Dropdown option to produce a drop-down list in the cell. Otherwise, users have to know the entries (or enter them from the help message).
- Date Lets you specify a comparison operator and appropriate dates (including formulas).
- Time Lets you specify a comparison operator and appropriate times (including formulas).
- Text Length Lets you specify a comparison operator and appropriate values (including formulas).
- Custom Lets you specify a formula that returns a logical TRUE or a logical FALSE value.
4. Select or clear the Ignore Blank check box as appropriate.
5. On the Input Message tab, choose whether to have Excel display an input message when the cell is selected. If you leave the Show Input Message When Cell Is Selected check box selected (as it is by default), enter the title and input message in the text boxes:
When a user selects a restricted cell, Excel displays the information message (unless you chose not to display one), E.g.:
6. On the Error Alert tab, choose whether to have Excel display an error alert after the user enters invalid data in the cell. If you leave the Show Error Alert After Invalid Data Is Entered check box selected (as it is by default), choose the style (Stop, Warning, or Information) in the Style drop-down list, and enter the title and error message in the text boxes.
- Stop alerts prevent the user from continuing until they enter a valid value for the cell.
- Warning alerts and Information alerts display the message but allow the user to continue after entering an invalid value in the cell.
7. Click the OK button to close the Data Validation dialog box and apply the validation to the cell or range.