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 built-in functions. We can apply data validation to ensure that only unique values are entered in each cell. This article shows you how to do this.
Data validation unique values only
This can be done by using a custom formula of COUNTIF function. This formula can turn out to be very helpful since it can help in keeping a check on a hefty amount of cells, i.e., even more than 1000 cells.
Formula using COUNTIF
To allow only unique values to be entered in a range of cells, we use the following custom formula in a Data Validation window:
=COUNTIF(range,address of cell)<condition)
Explanation of formula
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 a condition.
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
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
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.