Excel Data Validation

Use data validation in Excel to make sure that users enter correct values into a cell. Data validation option is found under Data Menu in Excel. This feature restricts user from entering incorrect values in cell.

For example , in Student Workbook , Marks is whole number greater than 0 and less than or equal to 100. 
Select Marks cell(s) and click on Data -> Data Validation
Data Validation Dialog box shows number of validation criteria options ( Any Value , Whole Number , Decimal , List , Date , Time , Text length , Custom )
For above marks data validation, we will select Whole number and specify minimum value as 0 maximum value as 100.
To configure Input message to be shown on selected cell , Navigate to Input Message tab and enter text in Title ( Total Marks = 100 ) and Input Message textbox ( Marks can be between 0 to 100 ).
To configure Error Alert click on Error Alert tab , Select Style ( Stop , Warning or Information ) and enter Title and Error Message.
Since we do not want user to enter number less than 0 and greater than 100 , so select Style as Stop and Enter Title : Enter Correct Marks and Error Message : Marks can be between 0 to 100
Then click on OK button to apply data validation on a particular cell(s)
When Marks cell is selected , it shows below message based on Data Validation setting done :
Enter incorrect number i.e. number below 0 or number above 100 , it will show below error message

Leave a Reply