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