Chapter-7: Custom (Formula) Validation

We can put our own desired validation in the Excel cell or range by using the Custom Validation. We need to put the logical formula which will return TRUE or FALSE. Whenever formula will return TRUE, user will be allowed to enter the data.

Below are the steps to apply data custom validation:

  • Select the range.
  • Go to Data tab>>Data Validation>>Click on Data Validation.

 

Data Validation Option
Data Validation Option

 

  • Below given window will be opened.
  • Select Custom in Allow drop down.
  • Put a logical formula(which should return True or False only) in the Formula box.
  • Click on OK button.

Here we are putting validation to enter Numbers only.

Note: Cells reference should be taken only for active cell, as in below given example Active Cell (“A1”) has been taken.

Custom Validation Window
Custom Validation Window

 

Now it will allow to enter only numbers. whenever a text will be entered, it will show “The value you entered is not valid

 

Invalid Entry Error
Invalid Entry Error

 

Next ChapterPrevious Chapter