Table of Contents
Prevent Duplicate Entries
To prevent duplicate values from being entered into Excel use data validation and the COUNTIF function.
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$20A2) 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 lock 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.
