HOW TO USE DATA VALIDATION IN EXCEL 2016

How to use Data Validation In Excel

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

Note:


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

  • If you select Any value in data validation its mean you can input any text and numeric value.

  • Whole number

  • 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.

  • Decimal

    Same Concept of Whole number but this setting is Decimal Value only


  • List

  • 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.

Create Data Validation List in excel 2013 2016


create drop down list using data validation in excel

  • 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.

  •   Custom

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.

Input Message Using Data Validation


Error Alert In Data Validation

  • Stop
User entering invalid data in a cell. A stop alert show two option. Retry Cancel and Help

  • Warning
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.

  • Information
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...


EmoticonEmoticon