Data Validation feature in Excel prevents invalid entries from being entered into a cell in a sheet. Data validation can be set up to work with numbers, text string, date / time or customized formula so that we can restrict the set of values that can be entered in a cell and also prevent wrong entries being made at the time of data entry. Data validation can also be used to create drop down lists so that the user can choose a single value from a number of values. Let’s take a closer look at the data validation feature and how it works.
Accessing Data Validation Feature from Excel Menu
The data validation feature can be accessed in Excel 2007 and later versions using the ‘Data Validation’ option under the ‘Data’ tab.
The data validation feature can be accessed in Excel 2003 and earlier versions using the ‘Data’ -> ‘Validation’ option from the menubar.
Various Options of the Data Validation Feature in Excel
The ‘Settings’ tab on the data validation option box is the most important of all the three tabs. This tab allows us to specify the values to allow for as valid entries in to a cell. There are the following options that can be specified:
1. Any Value : Specifies that there is not restriction on the values that can be entered into the cell.
2. Whole Number : Specifies the numeric values that can be entered into the cell. You can specify negative, zero or positive values. It will NOT allow any decimal values. Ex. when you specify a Min = 1 and Max = 10, it will allow a value of 5 to be entered in the cell but will not allow a value like 5.50
3. Decimal : Specifies the numeric values that can be entered into the cell. You can specify negative, zero or positive values. Similar to ‘Whole Number’ option but will also allow any decimal values as well.
4. List : Allows a drop down list to be created from a specified set of values. You can either type in a the values or select from a range of cells. The user can pick and choose any value from the drop down list. Learn more about creating drop down lists using the data validation feature here.
5. Date : Similar to the whole number option, allows one to specify the data validation conditions that must be met by a date entered by the user.
6. Time : Similar to the whole number option, allows the valid conditions for checking a time string entered by the user in a cell.
7. Text : Allows checking for a text string to be entered by the user. Used to restrict the minimum and maximum length of the string entered by the user.
8. Custom : Allows checking for specific customized conditions such as the presence of a particular substring withing the string entered by the user.
The input message simply allows a message to be shown next to the cell when the cursor is placed in the cell. The moment some other cell is selected, the message box goes away. The input message is typically used to convey information regarding the valid set of values that can be entered in the cell.
By default, whenever an invalid value is entered by the user, an error alert pops up which will prevent the value from being entered in the cell. However Excel provides varying degrees of control over what type of message is displayed to the user and even to eliminate the data validation message altogether. If you want to allow the user the option to enter an value outside of the conditions or the drop-down list specified in the data validation, you can simply uncheck the “Show Error Alert After Invalid Data Is Entered” option. On the other hand, if this option is turned on, you can decide the type of error message that you would like to show the user :
1) A Stop message which will prevent the value from being entered and the user has to mandatory enter a new value again
2) A Warning message which will tell the user that an invalid value has been entered but if the user still wants to go ahead, he / she can go ahead. The user is also provided an option to try again.
3) An Information message simply lets the user know that that value entered is invalid. The user can then either choose to ignore or simply cancel the operation. The user does not get the option to try again. Selecting either the Warning and Information value will allow the user to override the data validation conditions specified.
Read more on Data Validation: Using Data Validation to Create a Drop Down List