Summary

To allow only unique values in a given range, you can use data validation with a custom formula based on the COUNTIF function. In the example shown, the data validation applied to C5:C9 is:

=COUNTIF(emails,C5)<2

where emails is the named range C5:C9.

Generic formula

=COUNTIF(range,A1)<2

Explanation 

Data validation rules are triggered when a user adds or changes a cell value. In this example, we are using a formula that checks that the input doesn't already exist in the named range "emails":

COUNTIF(ids,B5)<2

COUNTIF returns a count of the value in C5 inside the named range emails (C5:C9). If the count is less than 2, the expression returns TRUE and validation succeeds. If not, the expression returns FALSE and validation fails.

Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case B5.

Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists

Related formulas

Excel formula: Data validation allow numbers only

Data validation allow numbers only

Data validation rules are triggered when a user adds or changes a cell value. The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. As a result, all numeric input will pass validation. Be aware that numeric input includes dates and times, whole numbers, and decimal values...
Excel formula: Data validation allow text only

Data validation allow text only

Data validation rules are triggered when a user adds or changes a cell value. Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined. The ISTEXT function returns TRUE when a value is text and FALSE if not. As a...
Excel formula: Data validation require unique number

Data validation require unique number

Data validation rules are triggered when a user adds or changes a cell value. The AND function takes multiple arguments (logical expressions) and returns TRUE only when all arguments return TRUE. In this case, we need two conditions: Logical 1 tests if the input is a number using the ISNUMBER...

Related functions

Excel COUNTIF function

COUNTIF Function

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count cells...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Articles