Excel Data Validation

Subject: Excel Data Validation

By Muneer Koliyat

(Ref # 001-07092K5 )

Data validation is used to arrange a list of information that you have entered in a worksheet of Microsoft Excel. You can use this tool to provide users a choice from a list of items or control entries.

Selecting Items from a list Box

Here, I have given an example of some computer items that can be selected from a list box.

Picture #1

Add the entries in one row or one column

Select the list

Click the name box left to formula bar

Give the name e.g.

Press Enter

Picture #2

Select the cell (any cell) to validate.

Picture #3

On the Data menu, click Validation…, and then click the Settings tab.

Specify the type of validation you want:

In the Allow box, click List.

Click the Source box and then do the following methods

1- By name of range

To use a range of cells with a defined name, type the equal sign (=) followed by the name of the range. ( =Computer)

Picture #4

Make sure the In-cell dropdown check box is selected.

If you want to allow blank (null) values, select the Ignore blank check box.

If you want to prevent entry of blank (null) values, clear the Ignore blank check box.

2- By Cell Reference

To use cell references, select the cells on the worksheet and then press ENTER.

Picture #5

Cell references have to be to cells on the same worksheet. If you have to refer to a different worksheet or a different workbook, use a defined name and make sure the workbook is already open.

3- Define List Locally

Instead of referring to items on a worksheet, you can define list locally. To define the list locally, type the list values separated by commas.

Picture #6

The result will be as follows

Picture #7

Other Validations

Other than allowing list, You can also validate a cell with some restrictions like the specified cell should only accept the numbers between 50 to 100. You have to select ' Whole number' from the Allow drop down of Data validation settings. And select 'between' from data list. Give minimum and maximum numbers as shown in Picture #8

Picture #8

Like this you can validate date, time, text length, decimals etc. by selecting different items from Allow drop down

Input Message

To display an optional input message when the cell is clicked, click the Input Message tab, and fill in the title and text for the message and make sure the Show input message when cell is selected check box is selected.

Picture #9

Error Alert

In the same way you are able to give your own error message when invalid data entered. Click the Error Alert tab, and make sure the Show error alert after invalid data is entered check box is selected. Select one of the following options for the Style box:

To display an information message that does not prevent entry of invalid data, click Information.

To display a warning message that does not prevent entry of invalid data, click Warning . To prevent entry of invalid data, click Stop. Fill in the title and text for the message (up to 225 characters).

If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."

Picture #10

This Tutorial is a portion from Learn with http://www.koliyat.com/. For more information and guidelines you can visit our website.