Check data entry for invalid entries

You can greatly reduce data-entry problems in your workbooks by making Excel check entries before entering them in specific cells. To do so, you need to define restrictions and data-validation rules for those cells.

To make Excel check data entry for invalid entries, follow these steps:

    1.    Select the cell or range you want Excel to check.

    2.    Choose Data -> Validation... to display the Data Validation dialog box:

Data Validation in Excel 2003

    3.    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.
    Number Validation in Excel 2003

    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).
    List Validation in Excel 2003
  • 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:

Message Validation in Excel 2003

When a user selects a restricted cell, Excel displays the information message (unless you chose not to display one), E.g.:

Input Message Validation in Excel 2003

    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.

Style Validation in Excel 2003
  • Stop alerts prevent the user from continuing until they enter a valid value for the cell.
    Stop Validation in Excel 2003
  • Warning alerts and Information alerts display the message but allow the user to continue after entering an invalid value in the cell. E.g.:
    Warning Validation in Excel 2003
Information Validation in Excel 2003

    7.    Click the OK button to close the Data Validation dialog box and apply the validation to the cell or range.

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.