Custom data validation

So far, you could find a lot of texts on the web site “Excel Kitchenette” about data validation. Most of them were about lists, since this functionality is often used, and further the choice of data type, setting the text length, number range or date were mentioned … When activating the Data Validation option, there are also custom rules, where it is possible to enter a formula based on which the validation is being performed.

To run the Data Validation option, you must first select the single cell or group of cells to which the rules for validation ​​apply. Then we run this option, which is located in the Data ribbon, and enter the necessary parameters in the dialog box. If we want to specify our own validation rules in the Allow drop-down list, we need to select the Custom value, and then enter the validation formula in the Formula field. Here we should always enter a logical expression. If it is true, the entry will be accepted. Otherwise, Excel will send you an error message. It can be a default message. Users can also define own messages and the format of the notification.

Let’s see how this works through a few simple examples. If, when validating cell B1, you enter the formula:

=EXACT(B1,PROPER(B1))

Excel will check if you have entered first characters of the word as capital letters. This rule is used, for example, when you need to enter someone’s name in a cell. The EXACT function compares two values. It’s case sensitive, so it matters whether you use uppercase or lowercase letters.

If you want to check if the user has entered numeric data you can enter the formula:

=ISNUMBER(B1)

The ISTEXT function is used to check if he has entered text.

If you want to define validation rules for a cell A2 and enter the formula:

=A2<>UNIQUE(A:A,TRUE)

Excel will forbid you to enter duplicate values in column A.

As you can see, there are many ideas, try to create your own validation rules in Excel document.