How to work with Custom Data Validation

Custom Data Validation is one of the lesser used features in Data Validation. It can accept a formula to validate a cell input. Let’s take a look at how custom data validation works and then explore 2 of its applications

 

Where is Custom Data Validation & how it works?

When you open Data Validation (shortcut ALT A V V) one of the options is Custom, which allows you to write formulas to make sophisticated data validations in a cell

 

How does it work ?

While writing formulas for custom data validation, just keep one rule in mind : Write a formula that either returns a TRUE or FALSE

1. If the Formula returns a TRUE then the value is accepted
2. Else the Validation returns an error

 

Let me show you 2 real time applications of custom data validation

 

Application 1 – Restrict duplication of a Value

Let’s say you are preparing a spreadsheet for your accountant who often makes mistakes. The deal is that the invoice number should not be duplicated while entering data

Here is how we can achieve it

Here is the formula. Notice a few things

=COUNTIF($B$5:B5,B5)<=1

1. While selecting the range make sure the active cell is the first cell also the starting point of the range $B$5 is locked
2. This formula is resulting in a TRUE / FALSE depending on value entered in the cell
3. The COUNTIF formula is checking if the value in the active cell is already present in the previous cells or not

 

Application 2 – Limit minimum age to 18 years

Often we have the need to validate for the age before inputting data. This formula checks if the applicant is an adult or not

Note a few thing about the formula

=C6<=EDATE($C$3,-12*18)

1. The Cell C6 contain today’s date. You can use TODAY function for generating a dynamic date
2. The EDATE Function is calculating the date 18 years prior
3. This formula also returns a TRUE /FALSE depending on the date entered in the cell

The same formula can be tweaked to fit any other age criteria

 

Have you used custom data validation before? Share some of your tricks in the comments below!

 

Other Data Validation Tricks

1. Dependent Data Validation
2. Interdependent Data Validation – A value selected in the first drop down should not appear in the second drop down
3. Use Data Validation as Switches