Go Back

How to Use Data Validation to Get 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 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.

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

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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need to create a data validation only using specific text values.
Solved by O. E. in 13 mins
Please help me again, i want to add some other conditions to the unique code data validation.
Solved by C. C. in 19 mins
My data validation is only returning an error message when I type in a value that is specified in the data validation options
Solved by V. H. in 30 mins

Leave a Comment

avatar