How to Prevent Duplicate Entries in MS Excel


The below example show you how to use data validation to prevent users from entering duplicate values.






1. Select the range A2:A20.


2. On the Data tab, 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.




No comments