Stop Duplicate Data Entry through Data Validation

Stop Duplicate Data Entry through Data Validation

As we know Data Validation tool can be used to create a drop down list in a cell and limit the values that user can enter.

But along this we can use Data Validation to stop users from entering duplicate data. Below are the steps for doing so.

Step 1: Select the range i.e. A1:A100 where we want only unique records’ entry and go to Data Validation (Data Tab > Data Validation).

Now select the validation type as Custom so we could use a formula. Now go to the formula field and type the below mentioned Countif formula.

=COUNTIF($A$1:$A$100,A1)<=1

Along this we can also setup the Error Alert for a custom message whenever a duplicate record is typed.

Once it’s setup, it will show like the below.