Prevent Duplicate Entries in Excel

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$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 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.

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Excel Functions and Data Analysis in Excel.