How to use Data Validation in Excel 2016

Data Validation

Data validation is MS Excel’s feature to allow you to create a certain rules that prescribe what can be entered into a cell. For example, you want to enter a number 0 to 9 in a particular cell, then you need to create rules in data validation. If the user enter an invalid entry, then user get a system error message but you can also display a custom message as shown below. Click here for how to use data validation from vba into excel sheet

How to use Data Validation in Excel 2016

  • Select the cells or range, where you want apply Data Validation.
  • Choose Data Tab» Data Tools » Data Validation. Excel displays the Data Validation dialog box, which have 3 tabs (A) Settings (B) Input Message (C) Error alert.
  • After applying Data validation, if you want apply same data validation in other place, then you have to copy that cells or range and paste as validation by paste special to other range or cells.

Settings Tab

Here you can apply difference types of validation rules, you need. When you choose an option from the Allow drop-down list then, the contents of the Data Validation dialog box will change. Displaying controls based on your choice.

  • Any Value− Use this option for removes any existing data validation.
  • Whole Number– When the user use this option, user have to enter a whole number in a cell. For example, you can set rules that, the entry must be a whole number greater than or equal to 10.
  • Decimal− When the user use this option, user have to enter a whole number in a cell. For example, you can set rules that, the entry must be greater than or equal to 10 and less than or equal to 20.
  • List− When the user use this option, user have to choose a value from a list of entries, which you mention. You can create drop-down list with this option. You can create list validation from two way first you can write value in list or you can put value from range in excel sheets, values will appear in the drop-down.

How to use Data Validation in Excel 2016

  • Date− When the user use this option, the user have to enter a date. You can set a valid date in the Data drop-down list. For example, you can set rules that, the input data must be greater than or equal to January 1, 2016, and less than or equal to December 31, 2016.
  • Time− When the user use this option, the user have to enter a time. You can set a valid time in the Data drop-down list. For example, you can set rules that, the input data must be greater than or equal to 12:00:00 PM, and less than or equal to December 16:00:00 PM.
  • Text Length– You can specify that entered data must have equal length of all cells. For Example, You have to enter mobile no., then you can specify the length of mobile no. 10 digit. You can specify text length for numerical and alphabetical.
  • Custom– When user use this option, you can apply a logical formula for user to enter a valid data into cell.

How to use Data Validation in Excel 2016

Input Message Tab

You can set the custom input help message for enter a valid value with this tab. Write the message for title and write the message for the Input message and the input message must appear when the cell is selected.

How to use Data Validation in Excel 2016

Error Alert Tab

You can set the custom input error message for enter a valid value with this tab. Write the message for title and write the message for the Input message.

How to use Data Validation in Excel 2016

Clear Data validation

If you want clear validation rule then, you have to open dialog box of data validation after that you see Clear All option in Data validation dialog box as given below picture and click this option. 

Leave a Reply

Your email address will not be published. Required fields are marked *