Basics of data validation in Excel

Data validation in Excel is a function that allows a person to control the input type of information into a worksheet. Data validation can be used to give users a selection list, creating customized settings and restricting the entries into the worksheet according to a specified size or type. It should be noted that a user can still paste data into a cell and thus bypass all the rules that you have set-up.

Creating, naming and applying the list

Type all the entries that you want to be included in the drop-down list in one row or column. To name the list range, select the cells in that list and then click in the Name box which will be located on the left side of the formula bar. Now type only a one word name for that list and ENTER. The final step is to apply data validation by selecting the cells where it should be applied. From the Data option on the menu select Validation and then List. A source box will be displayed. You will type =LISTNAME for instance, =BookList and then click OK.

As an alternative to using the list of items in the worksheet, you may want to type the list directly in the source box. In this instance you will type the items in the box separated by commas:

Child,Adult,Senior

It should be noted that the list will be case sensitive and if the user types CHILD an error message will come up.

Learn more about data validation through our free tutorials and online videos at our training page.