Excel’s Data Validation feature is similar in many respects to the Conditional Formatting feature. This feature enables you to set up certain rules that dictate what you can enter into a cell, it is a very useful feature of Microsoft Excel. Data validation is a very useful feature of Microsoft Excel, a later example will more fully explore this tool.
For now this example demonstrates the use of Data Validation to create a drop down list to ease data entry and limit the operator's choice. This is often more useful than a ComboBox because there is no problem with re-sizing or moving.

Validate for numbers

Click to enlarge

Specifying validation criteria

Click to enlarge 

 2) Choose Data=>Validation. Excel displays the Data Validation Dialog Box.

Click to enlarge

3) Click on the settings Tab

4) Choose an option from the drop down box labelled ‘Allow’. e.g. “Whole number”

5) Specify the conditions by selecting the drop down box labelled Data. As shown below

Click to enlarge

6) (Optional) Click on the input Message Box Tab and specify which message to display when a user selects the cell. You can also use this step to tell the user what type of data is expected. As shown below

7) (Optional) Click the Error Alert tab and specify which Error message to display when a user makes an invalid entry. As shown below

Click to enlarge

8) Click OK to see the result as shown in picture below

 

Click to enlarge

Types of Validation Criteria 

The settings tab of the data validation dialog box enables you to specify a wide variety of data validation criteria. The following options are available in the Allow Drop-Down box

a) Any Value: Selecting this option removes any existing data validation.
b) Whole Number: The user enters only whole numbers
c) Decimal: The user must enter a decimal
d) List: The user must choose from a list of entries you provide (covered later in this thread)
e) Date: The user must enter a date
f) Time: The user must enter a time
g) Text Length: The user can enter text up to a certain length
h) Custom: A logical formula determines the validity of the user’s entry

The settings tab of the data validation dialog box contains two other check boxes:

i) Ignore Blank: If checked, blank entries are allowed
j) Apply these changes to 'All' other cells with the same setting: If checked, the changes you make apply to all other cells that contain the original data validation criteria.

IMPORTANT NOTE

Even with Data Validation in effect, the user could enter invalid data. If the Style setting in the Error Alert Tab of the Data Validation dialog Box is set to anything except Stop, invalid data can be entered. Also remember that Data validation does not apply to the calculated results of formulas. In other words, if the cell contains a formula, applying conditional formatting to that cell will have no effect.
 

Data Validation Dropdown List   

Perhaps one of the most common uses of data validation is to create a drop down list of items. To set up this feature create a list to choose from in some unobtrusive place on your Worksheet. Select the cell where you want the dropdown to appear. From the Data Menu, select Validation.. In the allow box, choose List. In the list box, use the RefEdit (red and white icon at the right) and select your range. That is all there is to setting up a data validation.

However, you may well want to refer to a list already on another Worksheet. This is slightly more complicated because Microsoft Excel will display a warning that "You may not use references to other worksheets for data validation criteria". This is not actually totally true. The trick is to use a Named Range. - highlight your list range and give it a name with Insert Menu - Names - Define. So if you call the list MyMonths you can now type in the Allow Box - =MyMonths. The data validation will now refer to the list on a different sheet.

First, enter the list items into a single-row or single-column range. (These are the items that will appear in the drop-down list). Then select the cell that will contain the drop down list and access the data validation dialog box. In the settings tab, select the list option and specify the range that contains the list using the source control. Also, make sure that the In-Cell Dropdown check box is checked. See picture below.

Click to enlarge

After performing these steps, the cell displays a drop-down arrow when it is activated. Click the arrow and choose an item from the list that appears.

(a) Accepting text only

To force a range to accept only text (no values), use the following data validation formula:

=ISTEXT(A1)

This formula assumes that the active cell in the selected range is cell A1.

b) Accepting a larger value than the previous cell

The following data validation formula enables the user to enter a value only if it’s greater than the value in the cell directly above it:

=A2>A1

This formula assumes that A2 is the active cell in the selected range. Note that you can’t use this formula for a cell in Row1

c) Accepting non duplicate entries only

The following data validation formula does not permit the user to make a duplicate entry in the range A1:C20

=COUNTIF($A$1:$C$20,A1)=1

This formula assumes that A1 is the active cell in the selected range. Note that the first argument for COUNTIF is an absolute reference. The second argument is a relative reference, and it adjusts for each cell in the validation range.

d) Accepting text that begins with a specified letter

The following data validation formula demonstrate how to check for a specific character(s). In this case, the formula ensures that the user’s entry is a text string that begins with the text “k” (either uppercase or lowercase) see picture below.

=LEFT(A1)= “A”

This formula assumes that the active cell in the selected range is Cell A1