Validating Cell Entries

To do this:

Select the Cell to Validate

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

2. Specify the type of validation you want:

Allow Values from a List

1. In the Allow box, click List.

2. Click the Source box and then do one of the following:

• To define the list locally, type the list values separated by commas.
• To use a range of cells with a defined name, type the equal sign (=) followed by the name of the range.
• To use cell references, select the cells on the worksheet and then press ENTER.

Note: 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.

• Make sure the In-cell dropdown checkbox is selected.

Allow Numbers Within Limits

1. In the Allow box, click Whole Number or Decimal.

2. In the Data box, click the type of restriction you want. For example, to set upper and lower limits, click between.

3. Enter the minimum, maximum, or specific value to allow.

Allow Dates or Times Within a Time Frame

1. In the Allow box, click Date or Time.

2. In the Data box, click the type of restriction you want. For example, to allow dates after a certain day, click greater than.

3. Enter the start, end, or specific date or time to allow.

Allow Text of a Specified Length

1. In the Allow box, click Text Length.

2. In the Data box, click the type of restriction you want. For example, to allow up to a certain number of characters, click less than or equal to.

3. Enter the minimum, maximum, or specific length for the text.

Calculate What's Allowed Based on the Content of Another Cell

1. In the Allow box, click the type of data you want.

2. In the Data box, click the type of restriction you want.

3. In the box or boxes below the Data box, click the cell that you want to use to specify what's allowed.

Use a Formula to Calculate What's Allowed

1. In the Allow box, click Custom.

2. In the Formula box, enter a formula that calculates a logical value (TRUE for valid entries or FALSE for invalid). For example, to allow the value in the cell for the marks obtained in English (cell E6) and the total marks (I6) is also less than the 600, you could enter =AND(E6<100,I6<600) for the custom formula.

Specify Whether the Cell Can Be Left Blank

1. If you want to allow blank (null) values, select the Ignore blank checkbox.

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

Note: If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank checkbox allows any values to be entered in the validated cell. This is also true for any cells referenced by validation formulas. If any referenced cell is blank, setting the Ignore blank checkbox allows any values to be entered in the validated cell.

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

Specify How You Want Microsoft Excel to Respond When Invalid Data is Entered

1. Click the Error Alert tab, and make sure the Show error alert after invalid data is entered checkbox is selected.

2. 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.

3. Fill in the title and text for the message (up to 225 characters).

Note: Applying data validation to a cell does not format the cell.

To Find All Cells with Data Validation

1. On the Edit menu, click Go To.
2. Click Special.
3. Click Data validation.
4. Click All.

Find Cells That Match Certain Data Validation Settings

1. Click a cell that has the data validation settings for which you want to find matches.
2. On the Edit menu, click Go To.
3. Click Special.
4. Click Data validation.
5. Click Same.