Custom Data Validation :: Using Formulas

Posted: June 4, 2013 by MaxFraudy in Excel
Tags: , ,

Excel provides Data Validation feature that lets you restrict the type of data that the user enters into the cell. There are many types of built-in validations that are available, like below:

Data Validation Options

Data Validation Options

The last option, i.e. Custom, can be used to put in non-standard validations using Excel formulas. Let’s see how it works using an example. Suppose you have a data table in which the first column contains the Product Code.

Custom Data Validation

Custom Data Validation

Now, you will be sharing this file with a lot of other people who will be putting in some more data. The Product Code must always start with “ID-” and must be 10 characters long. So that other users follow this rule you can put a data validation like this:

  1. Open the Data Validation dialog box and select Custom from the dropdown.
  2. In the Formula bar, type this formula :=AND(LEFT(A2, 3) =”ID-“, LEN(A2) = 10)”
  3. Go to Input tab, fill in the instructions that you want to appear to the users when they select the validated cells.
  4. Go to Error Alert tab, select the Style and the message that you want to appear if the users try to enter a value that is now allowed.

The way that the formula works in the validation is that for a value to be allowed to be entered, the formula should evaluate to be TRUE. If the entered value evaluates to be FALSE, it will not be allowed.

For other more advanced uses of data validation, visit this site

Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s