Here's how: Optionally, you can add a custom input message and Error alert that will show up when the user selects the validated cell or enters invalid data, respectively.
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.
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.
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.
In yesterday's tutorial we started to look at Excel Data Validation - what its purpose is, how it works, and how to use built-in rules to validate data in your worksheets.
Today, we are going to take a step further and talk about the nitty-gritty aspects of custom data validation in Excel as well as experiment with a handful of different validation formulas.
If you want more control over data validation in your worksheets, you can replicate the inbuilt functionality with a custom rule or write your own formula that goes beyond the built-in capabilities of Excel data validation.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.But this can be easily done with a custom data validation formula based on the ISNUMBER function, like this one: Where D2 is the uppermost cell of the selected range.For example, to limit the data entry to 6 days from now (7 days including today), we are going to use the built-in Date rule with the formula-based criteria: For starters, copy your validation formula into some cell to make sure it does not return an error such as #N/A, #VALUE or #DIV/0! If you are creating a custom rule, the formula should return the logical values of TRUE and FALSE or the values of 1 and 0 equating to them, respectively.If you use a formula-based criteria in a built-in rule (like we did to validate times based on the current time), it can also return another numeric value.