Data Validation

Excel’s data validation feature enables you to set up specific rules that restrict a user from entering invalid data.

For example, you may want to limit data entry in a particular cell to whole numbers between 3000 & 20000. If the user makes an invalid entry, you can display an error message.

Excel makes it easy to specify the validation criteria, and you can also use a formula for more complex criteria.

Types of Validation Criteria You can Apply

The settings tab of the Data Validation dialog box enables you to specify a wide variety of data validation criteria.

Types of options which are available for Data Validation.
Also, Keep in mind that the other controls in the settings tab which will be vary depending on your choice in the allow drop-down box.

• Any Value: Selecting this option removes any existing data validation. Note, however, that the input message, if any still displays if the check box is checked in the Input Message tab.
• Whole Number: The user must enter a whole number. You specify a valid range of whole numbers by using the data drop-down list. For example, you can specify that the entry must be a whole number greater than or equal to 100.
• Decimal: The user must enter a number. You specify a valid range of numbers by using the Data drop-down list. For example, you can specify that the entry must be greater than or equal to 0 and less than or equal to 1.
• List: The user must choose from a list of entries you provide. When some items are to be entered into a column, you can create a drop-down list containing the options from which the user must choose. You can define the values within the source text box using a comma separator or a range of data can be determined using an equal sign (=) as a prefix.
• Date: The user must enter a date. You specify a valid date range by using the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, 2007, and less than or equal to December 31, 2007.
• Time: The user must enter a time. You specify a valid time range by using the data drop-down list. For example, you can specify that the entered data must be greater than 12.00 p.m.
• Text Length: The length of the data (number of characters) is limited. You specify a valid length by using the data drop-down list. For example, you can specify that the length of the entered data is 1 (a single alphanumeric character).
• Custom: To use this option, you must supply a logical formula that determines the validity of the user’s entry ( a logical formula returns either Ture or False). You can enter the formula directly into the Formula control (which appears when you select the Custom option), or you can specify a cell reference that contains a formula. This sessions contains examples of useful formulas.

Specifying Validation Criteria

To specify the type of data allowable in a cell or range, follow the below steps

• First Select the cell or range

• Then, go to Data tab-> Data Tools group-> Data Validation.
Excel displays its Data Validation dialog box.

• Click the Settings tab (see the above figure).

• Choose an option from the drop-down box labeled Allow. The contents of the Data Validation dialog box will change, displaying controls based on your choice. To specify a formula, select Custom.

• Specify the conditions by using the displayed controls.

• (Optional) Click the Input Message tab and specify which message to display when a user select the cell.

• (Optional) Click the Error Alert tab, and specify which error message to display when a user makes an invalid entry.

• Click Ok.

Circle around Invalid Entries

For the getting Circle for the Invalid Entries.

Go to Data-> Data Validation-> Setting. In setting-> Validation Criteria-> select whole number-> Min:-100 , Max: 1000.

Again go to Data-> Data Tools -> Data Validation drop-down control contains an item name Circle Invalid Data. When you click this item, circles appear around cells contain incorrect entries. If you correct an invalid entry, the circle will disappear. In this fig, invalid entries are defined as values less than 100.

Creating a Drop-Down List

Perhaps one of the most common uses of data validation is to create a drop-down list in a cell. Figure below shows an example that uses the month name in A1:A2 as the list source

To create a drop-down list in a cell

1)Enter the list items into a single-row or single-column range. These items are the ones that appear in the drop-down list.

2)Select the cell that will contain the drop-down list and access the Data Validation dialog box.

3)In the Settings tab, select the List option and specify the range that contains the list using the Source control. If the source cells are in the same worksheet you can specify the range will cell address, otherwise, you have to specify the name of the range of source cells.

4)Make sure that the In-cell Dropdown check box is checked.

5)You can set Input Message and Error Alert if you want.

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

Custom Data Validation

The following sections contain a data validation example that uses a formula entered directly into the formula control on the settings tab of the Data Validation dialog box. These examples help you understand how to create your own Data Validation Formula