Data Validation in Microsoft Excel

Data validation enables you to set up certain rules that dictate what you can enter into a cell.  For example, a number greater than 10.  You can display a custom input message when the user selects the cell, and if the user makes an invalid enter, you can display a custom error message.

To use data validation - select a cell and then select Data > Validation - you can now specify your validation criteria, create an input message and an error message.


The setting tab enables the user to specify a variety of data validation criteria.  The following option are available:

Any Value - This removes any existing data validation.  However, any existing input message will still be displayed.

Whole Number - The user must enter a whole number.

Decimal - The user must enter a number.

List - The user must choose an entry from a drop down list that you create.  The source of the list can be a range in the current worksheet, a named range or it can be typed into the Source control using commas to separate the items.  A named range must be used to refer to a range in another worksheet of the same workbook.  One of the most common uses of data validation is to create drop down lists of valid items.

Date - The user must enter a date.

Time - The user must enter a time.

Text Length - The length of the data (numbers of characters) is limited.

Custom - This allows you to enter a logical formula - the formula must evaluate to either True (valid data) or False (invalid data). 

There are also two check boxes on the setting tab:

Ignore Blank - This will allow blank entries.

Apply These Changes to All Other Cells with the Same Settings - This applies any changes you have made to all other cells that contain the original data validation criteria.

Caution - The user can still enter invalid data if the style setting in the Error Alert tab is set to Warning or Information - or if error alerts are disabled.

Caution - Data validation does not apply to the calculated results of formulas.

Tip - The Formula Auditing Tool contains a button named Circle Invalid Data.  When you click this button, circles appear around cells that contain invalid entries.  The circles will disappear when the cells are corrected.

Using Formulas for Data Validation Criteria

To use formulas as your data validation criteria you must select Custom from the Allow drop down list in the Settings tab.

Accepting text only - Type =ISTEXT(A1) - assuming that A1 is the active cell.

Accepting a larger value than the previous cell - Type =A2>A1 - assuming that A2 is the active cell.

Accepting non-duplicate entries - Type =COUNTIF($A$1:$C$20,A1)=1 - assuming that the range of cell that you wish to apply data validation to is A1:C20 and that A1 is the active cell within that range.  Once you apply this criteria to cell A1, you must copy it to the other cells in the range.  Notice that the range is an absolute reference and A1 is a relative reference.

Accepting text that begins with A - Type =LEFT(A1)="a" - assuming that A1 is the active cell.

Accepting text that begins with A and is exactly five characters long - Type =COUNTIF(A1,"A????")=1 - assuming that A1 is the active cell.

This is the end of our tutorial on using Data Validation in Microsoft Excel.

Return to Excel Exchange homepage.