Check data entry for invalid entries

Excel 365 2016 2013 2010 2003
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.    On the Data tab, in the Data Tools group, click the Data Validation list and choose Data Validation...:

Data Tools in Excel 2007

   3.    In the Data Validation dialog box, on the Settings tab, specify the validation criteria to use.

Data Validation in Excel 2007

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 2007

    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 2007
  • 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 2007

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 2007

   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 2007
  • Stop alerts prevent the user from continuing until they enter a valid value for the cell.
    Stop Validation in Excel 2007
  • Warning alerts and Information alerts display the message but allow the user to continue after entering an invalid value in the cell.
Warning Validation in Excel 2007
Information

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

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.