Purpose of the guide
Understand how data validation allows you to limit the type of data or values that users enter into a cell.
Apply data validation to cells in Excel
- Select the cell or cells for which you want to create a rule.
- On the Data tab on the ribbon, choose Validate Data .
- On the Settings tab, select an option under Allow :
- Integer : to limit the cell to accept only integers
- Decimal : to limit the cell to accept only decimal numbers
- List - allows you to select data from the drop-down list
- Date : only accepts entry of dates in cells
- Time : to limit the cell to accept time only
- Text Length : Limits the length of the text
- Custom : Allows you to enter a custom formula
- Under Data , select a condition:
- between
- not included between
- equal to
- different from
- greater than
- less than
- greater than or equal to
- less than or equal to
- On the Settings tab, select an option under Allow :
- Set the other necessary values based on the option chosen in Allow and Data . For example, if you select between , select the Minimum: and Maximum: value for cells
- Select the Ignore blank cells check box if you want to ignore blanks
- If you want to add a title and message for the rule, select the input message tab and then type a title and input message
- Select the Show input message when cell is selected check box to display the message when the user selects or places the mouse pointer over the selected cells
- Select OK .
If the user now tries to enter an invalid value, a pop-up window appears with the message “This value does not match the data validation restrictions defined for this cell”.