MS-Excel / Excel 2003

Only the Valid Need Apply

The data validation feature is the Settings tab of the Data Validation dialog box (Data → Validation). The Settings tab is where you specify the criteria under which Excel considers any data entry made in the current cell to be kosher.

The first control on the Settings tab is the Allow drop-down list box. By default, this box is set to Any Value to allow you to do any type of data entry in the current cell. To change all that anything-goes stuff by making only a certain type of entry permissible, select one of the other Allow options:

  • Whole Number restricts data entry to a whole number that is equal to, greater than, less than, or within a range of numbers that you then specify.
  • Decimal restricts data entry to a decimal number that is equal to, greater than, less than, or within a range of values that you then specify.
  • List restricts data entry to one of the items on a list (whose items must already have been entered in a range of cells in the worksheet). When using this Allow option, Excel enables you to create an in-cell drop-down list (with its very own tiny drop-down button) from which you or your users can select the entry.
  • Date restricts data entry to a date that is equal to, before, after, or within a range of dates that you then specify.
  • Time restricts data entry to a time that is equal to, before, after, or within a range of times that you then specify.
  • Text Length restricts data entry to a number of characters that is equal to, greater than, less than, or within a range of numbers that you then specify.
  • Custom restricts data entry to the parameters specified by a Logical formula (one that evaluates to TRUE for allowable entries or FALSE for invalid ones) that's already been entered into a cell of the worksheet to which you then refer.

By default, all data validation settings regard blank cells as valid. If you want to prevent users from leaving a cell blank, while at the same time making their entries conform to your other data validation criteria, clear the Ignore Blank check box on the Settings tab.

[Contents] [Next]