Microsoft Office Tutorials and References
In Depth Information
Using Data Validation to Control Data Entry
● Date When this option is selected, Excel will accept input only in a recognizable
date format. You must choose an operator and a date or range against which to
compare input cells. To allow any date but disallow text or numeric input, choose
Greater Than Or Equal To from the Data list and enter 1/1/1900 in the Start Date box.
● Time Your choices here are similar to those provided by the Date option, except
that the values entered for Start Time or End Time or both must be in a recognizable
time format. If you choose Less Than Or Equal To from the Data list and enter 18:00
in the End Time box, Excel will accept 3:32 PM or 14:32:21 but will reject 8:00 PM.
● Text Length Use this option with comments fields to prevent long-winded entries
or to insist on a minimum number of characters.
● Custom When you choose this option, you must enter a formula that returns a
logical TRUE or FALSE. This option is most useful when the current input cell is part of
a calculation and you want to allow or restrict input based on that calculation. Thus,
if you have a formula in cell E5 that sums the values of the four cells above it, you can
choose Custom from the Allow list and then enter =E$5 < 100 as the validation
criteria for E1 through E4. You can enter any value under 100 in any of those four cells,
but if the running total exceeds 100, your input will be rejected.
Use the Input Message tab of the Data Validation dialog box to define a helpful message
that appears when you select a cell. The message appears in a pop-up window, with a title
in bold and the input message text beneath it. The text you enter here helps prevent
confusion by making it clear exactly what type of input is allowed.