This post describes the data validation feature in Microsoft Excel and how to imply various data validations into your worksheet.
Data Validation in Microsoft Excel
The Data Validation in Excel allows us to restrict the type of data entered into a cell in the worksheet. It prompts the user to enter valid data and displays errors for invalid data. Depending on the requirements, different data validations can be performed.
Select the cells to which the validation is to be applied.
Select Data tab -> Data Tools-> Data validation.
A dialog box appears, under Allow, select an option based on your need.
- Whole Number – allows only whole numbers.
- Decimal – allows only decimal numbers.
- List – pick data from the drop down list.
- Date – allows only date.
- Time – allows only time.
- Text Length – restricts the length of the text.
- Custom – custom formula.
Under Data, select a condition:
- between
- not between
- equal to
- not equal to
- greater than
- less than
- greater than or equal to
- less than or equal to
Click on Ignore blank checkbox if you want to ignore blank spaces.
Based on what you choose for options Allow and Data. Set the other required values. For example, if you select decimal in allow and between in data, then give the minimum and maximum values for the cells.
Then move to Input message, and set a title and message that is to be displayed when the user hovers over the selected cells. This tells the user how the input must be.
Now when a user enters invalid data into the selected cell then an error message displays.
