Microsoft Office Tutorials and References
In Depth Information
Allow Only Certain Values in a Cell
Allow Only Certain
Values in a Cell
You can make Excel data entry more efficient
by setting up data entry cells to accept only
When you build a spreadsheet, you may find
that some cells can only take a particular range
of values. For example, an interest rate cell
should take a decimal value between 0 and 1 (or
a whole number between 0 and 100 if you have
formatted the cell with the Percent number
format). Similarly, a cell designed to hold a
mortgage amortization term should probably
take whole number values between 15 and 35.
To ensure that the proper values are entered,
you can set up a cell with data validation
criteria that specify the allowed value or values.
You can work with numbers, dates, times, or
even text length, and you can set up criteria
that are between two values, equal to a specific
value, greater than a value, and so on. Excel
also lets you tell the user what to enter by
adding an input message that appears when
the user selects the cell.
1 Click the cell you want to
2 Click the Data tab.
3 Click Data Validation.
The Data Validation
dialog box appears.
4 Click the Settings tab.
5 In the Allow drop-down
list, click the type of data
you want to allow in the
6 In the Data drop-down
list, click the operator you
want to use to define the
7 Specify the validation
criteria, such as the
Maximum and Minimum
allowable values as
Note: The criteria boxes you see
depend on the operator you chose
in step 6.