Prevent Duplicate Entries

 

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

1. Select the range A2:A20.

Prevent Duplicate Entries Example

2. On the Data tab, click Data Validation.

Click Data Validation

3. In the Allow list, click Custom.

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

Data Validation Formula

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.

Formula Check

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.

Prevent Duplicate Entries Result

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