Microsoft Office Tutorials and References
In Depth Information
Using Data Validation to Control Data Entry
The exact choices available on the Settings tab vary depending on the data type you select
from the Allow list. For most data types, you select an operator from the Data list (Between,
Less Than Or Equal To, and so on) and then ill in values or formulas that the input data is
compared with. In this example, we chose Date as the data type and Between as the
operator, adding formulas in the Start Date and End Date boxes that reject any date that is in the
future or is more than 30 days earlier than the current date.
The following list summarizes what you can do with each of the options available when you
● Any Value Ironically, this default setting does not allow any restrictions on allowed
input data. Its purpose is to allow you to display pop-up help text by filling in the
boxes on the Input Message tab.
● Whole Number This data type requires numeric input and rejects any value that
contains a decimal point, even if the decimal point is followed by a 0.
● Decimal This setting allows any numeric input, with or without a decimal point,
provided it meets the other criteria you establish.
● List When you choose this option from the Allow list, any values entered in the
input cells must match an item in the list. For a short list, click in the Source box and
enter the items directly, separated by commas, as shown here. (Note that list items
are case sensitive; if you include Northwest as a term, it is allowed only with an initial
capital N .) Alternatively, you can click the Collapse Dialog button to the right of the
Source box and enter the address or name of a range that contains the list of allowed
values. If you want users to be able to pick from a list containing these values, select
the In-Cell Dropdown check box.