Prevent Duplicate Entries

Data Validation is a very useful feature in Excel, you can use it to create drop down list in a cell and limit the values user can enter. But sometimes you may want to prevent users from entering duplicate values in a range of worksheet.

In this post we’ll learn how to use data validation to prevent a user from entering a value if it’s already been entered in the table.

This example teaches you how to use data validation to prevent users from entering duplicate values.

1. Select the range A2:A20.

2. On the Data tab, in the Data Tools group, click Data Validation.

3. In the Allow list, click Custom.

4. In the Formula box, enter the formula shown below and click OK.

Explanation: The COUNTIF function takes two arguments. =COUNTIF($A$2:$A$20,A2) counts the number of values in the range A2:A20 that are equal to the value in cell A2. This value may only occur once (=1) since we don't want duplicate entries. Because we selected the range A2:A20 before we clicked on Data Validation, Excel automatically copies the formula to the other cells. Notice how we created an absolute reference ($A$2:$A$20) to fix this reference.

5. To check this, select cell A3 and click Data Validation.

As you can see, this function counts the number of values in the range A2:A20 that are equal to the value in cell A3. Again, this value may only occur once (=1) since we don't want duplicate entries.

6. Enter a duplicate invoice number.

Result. Excel shows an error alert. You've already entered that invoice number.

Note: to enter an input message and error alert message, go to the Input Message and Error Alert tab.