Data Validation Formulas

The simple use of Validation in Excel can serve the worthy purposes of maintaining database integrity. This tool in Excel is easy to use and can save you time and potential errors.

Where this Valuable Excel Feature really shines, however, is when you team it up with Formulas! We will look at some Great Examples of this, but first it is important to note that the formula you use must result in a True or False response. You can enter a formula of this type in Validation by selecting the Custom option in the dropdown box.

Here are Some Highly Useful Examples:

Accepting Non-duplicated Data Only
This example allows No Duplicate Entries in cells A1:B30:
      =COUNTIF($A$1:$B$30, A1) = 1

Accepting Text Only
Supposing your active cell is A1, the following will allow only Text entries:
      =ISTEXT(A1)

Accepting Fridays Only
For the cell A1, this formula will allow only dates that are Fridays:
      =WEEKDAY(A1) = 6

Accepting a Larger Value than Previous Cell
Simply insert the following formula in your Custom Validation box:
      =A2 > A1

Data Validation is a fine example of another tool any Excel Guru should have in his or her tool belt. Give it a try sometime!