Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell.
You can configure data validation to prevent users from entering data that is not valid. If you prefer, you can allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.
When might Data Validation be used?
Here are some of the scenarios in which Excel Data Validation may be required:
- Restrict data to predefined items in a list – For example, you can limit types of departments to Sales, Finance, R&D, and IT. Similarly, you can create a list of values from a range of cells elsewhere in the worksheet.
- Restrict numbers outside a specified range – For example, you can specify a minimum limit of deductions to two times the number of children in a particular cell.
- Restrict dates outside a certain time frame – For example, you can specify a time frame between today’s date and 3 days from today’s date.
- Restrict times outside a certain time frame – For example, you can specify a time frame for serving breakfast between the time when the restaurant opens and 5 hours after the restaurant opens.
- Limit the number of text characters – For example, you can limit the allowed text in a cell to 10 or fewer characters.
- Validate data based on formulas or values in other cells – For example, you can use data validation to set a maximum limit for commissions and bonuses of £3,600, based on the overall projected payroll value. If users enter more than £3,600 in the cell, they see a validation message.
Example
The sheet below requires an age to be given in whole numbers and all people participating in this survey should be between 18 and 60 years old.
To ensure that data outside of this age range isn’t entered, go to Data->Data Validation->Setting, input the conditions and shift to Input Message to give prompts like, “Please input your age with whole number, which should range from 18 to 60.” Users will get this prompt when hanging the pointer in this area and get a warning message if the inputted information is unqualified.
