What is Data Validation in Excel
Data Validation is a tool that help you control the kind of information that is entered in your worksheet with data validation, you can
- Provide users with a list of choices
- Restrict entries to a specific type or size
- Create Custom Setting
Data Validation is not foolproof. It can be circumvented by pasting data into the cell, or by Home Tab> Choosing Editing> Clear> Clear All
Type of Validation
- Any Value
- Whole number
If you select Any value in data validation its mean you can input any text and numeric value.
Set a range of numeric value and limits of value that can be entered in a cell or range. Select cell A1, click on Data Tab, click Data Validation Button on the ribbon, Open Data validation popup, Select whole number on settings tab, select data on your requirement like "between","not between","equal to ","not equal to"......!, Input Minimum and Maximum value for data limitation. Example Minimum Value is 0 and Maximum value is 10. Enter value 4 in cell A1 the value is entered without error, if you enter value 44 in cell A1, because the data validation setting you create for cell A1, you receive a stop Alert message.
You can also create Drop Down list in excel using Data Validation. For Example
You have a list of Department name in range A2:A10, Define a name for a cell or range you should create easy drop down list on cell .Go to data validation and select the list (Note) Check on "Ignore blank" and "In-cell dropdown" in the source box enter the name with "=" sign Example " =product " =Department" and click ok button.
- Text length
You can also Create text length Valid using Data Validation. if you input Minimum Value is 8 digit and Maximum value is 12 digit in selected range or cell. if you input less then 8 digit value or maximum 12 digit value Data Validation show error on you screen.
Input Message With Data Validation
You can also input Data Validation Message. Display a Message when cell is selected. you can create a text box to message display. you can inform user this cell is valid for text or number or date....! using input message.
Error Alert In Data Validation
User entering invalid data in a cell. A stop alert show two option. Retry Cancel and Help
User entering invalid data in a cell. Warning Alert message appears, user can click yes accept invalid entry, user can click No to edit invalid entry and user can click Cancel to remove invalid entry.
User entering invalid data in a cell. information just inform that this data is invalid if user click Ok to accept invalid entry and click cancel to reject it...