< Go Back

Data validation unique values only

Data validation unique values only

Often, we have to keep a check on the data we enter. This may seem an extremely tedious task but it can be made very easy with Excel’s inbuilt function. We can apply data validation to ensure that only unique values are entered in each cell. This can be done by using a custom formula of COUNTIF function. This formula can turn out to very helpful since it can help in keeping a check on a hefty amount of cells, i.e., even more than 1000 cells.

Formula

To allow only unique values to be entered in a range of cells, we use the following custom formula in Data Validation window:

=COUNTIF(range,address of cell)<condition)

Explanation

Excel COUNTIF function is an inbuilt function in Excel which helps in counting the number of cells which meets some criteria. This function is a statistical function and returns the count of cells that meet the criteria and you can put a condition so that count should be less than the required count in condition.

Example

In this example, we have applied a data validation in the range C5:C9 so that unique values are entered in the column. After selecting the range C5:C9, in a data validation custom rule, we will use the below COUNTIF formula in the formula field

=COUNTIF(PHONE,C5)<2

Where ‘PHONE’ is a named range. We use the above formula to check if the input value exists already in the named range “PHONE”. The Excel COUNTIF function returns the count of the value entered in each cell of range and the condition part in formula checks if the count is less than 2. If the above expression returns TRUE, validation succeeds and if it returns FALSE then the validation fails. Thus, the function helps in data validation unique values only.

Figure 1. Example of  Data validation unique values only

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar