How to prevent users from entering duplicate values using Data Validation?


Excel has some amazing features and as you keep on exploring it, you find it more interesting. Today we are going to talk about how we can use formula to prevent duplicate entries.

Download file:

Download320 downloads

Let's see one example:

1. We have entered data as shown in below image. We have Roll no's in Column B. We want to prevent data from duplicate Roll Nos.


2. To do this, Select the whole range, Go to Data-> Data Validation and click on Data Validation.


3. In Settings field-> In Allow-> Select Custom from dropdown list. In formula bar write down the formula as below.

Here the Formula

           =COUNTIF(range,cell)=1 means the value may only occur once (=1) since we don't want duplicate entries. So,COUNTIF($B$2:$B$12,B2)=1 means in range B2:B12, cell B2 value i.e 1 should be only once. It should not repeat further. So , if you try to put that, you will get an error message.

 Related Link: How to Use COUNT ,COUNTIF, COUNTIFS Function?


4. To provide an Error message,  select tab Error Alert and provide appropriate Title and Error message.


5. Now try to enter any repeated value in cell B6 and you will get error message.


Done. Now don't worry about any duplicate entries in you data!!

Explore more, It's Amazing…

How to remove duplicate data from the table in excel?

How to use SUM, SUMIF, SUMIFS functions in excel?

Hide cells with Errors using ISERROR or IFERROR fuction



4,678 total views, 1 views today

4 thoughts on “How to prevent users from entering duplicate values using Data Validation?

  1. Hii, Joe.

    By default, data validation doesn't work if we copy the cells. It has to be manual entry only. We can use VBA to check duplicate entries while copying the data.


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>