Prevent Duplicate Entries in Excel
You can prevent duplicate entries in Excel by applying data validation and the COUNTIF function.
1. Select the range A2:A20.
2. Go to the Data tab, then find Data Tools, and click Data Validation.
3. In the Allow list, click Custom.
4. Enter the formula provided into the Formula box, then click OK.
Explanation: The COUNTIF function takes two arguments. =COUNTIF($A$2:$A$20,A2) counts how many times the value in cell A2 appears in the range A2 to A20. This value can only appear once (=1) because we don’t want duplicates. We selected the range A2:A20 first, so when we used Data Validation, Excel filled the formula for all the cells. Notice that an absolute reference ($A$2:$A$20) was created to maintain a constant cell range.
5. To verify this, first select cell A3 and then click Data Validation.
As you can see, this function counts how many times the value in cell A3 appears in the range A2 to A20. This value may appear only once (=1) because we don’t want duplicates.
6. Enter a duplicate invoice number.
Result: Excel shows an error alert. You’ve already entered that invoice number.
Excel warning for duplicate data
Note: Go to the Input Message and Error Alert tabs to type your input message or error alert.
4/9 Completed! Learn much more about data validation ➝
Next Chapter: Keyboard Shortcuts