Custom Data Validation Rules in Microsoft Excel

This post describes the Custom Data Validation rules in Excel and the usage of custom features and formulas in Data Validation.

Custom Data Validation rules in Microsoft Excel

By using the custom data validation feature in Microsoft Excel, you can create a validation rule and validate cells with your own criteria. This custom option in Data validation makes the rules customizable and easier to get data from the user.

1. Select one or more cells to validate.
2. Select Data Tab -> Data Tools -> Data Validation.
3. A dialog box opens, select Custom in the allow section, and enter your data validation formula in the Formula box.
4. Click OK.

You can also add input message and error alert which displays when the user hovers over the validated cell or enters invalid data, respectively.

Here are some examples of custom data validation formulas :

Excel data validation to allow numbers only: =ISNUMBER(A2)

Excel data validation to allow text only: =ISTEXT(A2)

Allow text beginning with specific characters: =COUNTIF(A2,”aa-*”)