Below you will find a few examples of custom validation rules for different data types. All Excel data validation rules, built-in and custom, verify only new data that is typed in a cell after creating the rule.Copied data is not validated, nor is the data input in the cell before making the rule.The following sections describe how to use the validation with a list of strings, like in the last example above, Excel stores the strings internally as a Comma Separated Variable string.The total length for this string, including commas, cannot exceed the Excel limit of 255 characters.To pin down existing entries that do not meet your data validation criteria, use the Circle Invalid Data feature as shown in How to find invalid data in Excel.Surprisingly, none of the inbuilt Excel data validation rules cater for a very typical situation when you need to restrict users to entering only numbers in specific cells.When the option is off an input message is not displayed even if it has been set using parameter is used to set the title of the error message that is displayed when the data validation criteria is not met. The default error message is “The value you entered is not valid.
In situations when a certain column or a range of cell should not contain any duplicates, configure a custom data validation rule to allow only unique entries.When the option is on the data validation is not applied to blank data in the cell.It is on by default: parameter is used to toggle on and off the ‘Show input message when cell is selected’ option in the Excel data validation dialog. parameter is used to set the error message that is displayed when a cell is entered.For this, we are going to use the classic COUNTIF formula to identify duplicates: When a unique value is entered, the formula returns TRUE and the validation succeeds.If the same value already exists in the specified range (count greater than 1), COUNTIF returns FALSE and the input fails validation.Please pay attention that we lock the range with absolute cell references (A:$A) and use a relative reference for the top cell (A2) to get the formula to adjust properly for each cell in the validated range.