Prevent Duplicate Entries in Excel
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, Excelautomatically 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.