Tip Printed from ExcelTip.com

---

Preventing Duplicates When Entering Data


Problem:

We want to make it impossible to enter duplicate values in List1 (cells A2:A10).

Solution:

Use Data Validation as follows:
1. Select the range to validate (cells A2:A10)
2. Data --> Data Validation --> Settings
3. From the Allow dropdown list, select Custom.
4. Enter the following COUNTIF formula in the Formula box:
=COUNTIF($A$2:$A$10,A2)=1
5. Select the Error Alert tab.
6. In the Title box, enter "Duplicate Entry".
7. In the Error Message box, enter "The value you entered already appears in the list above."
8. Click OK.

After following this procedure, any duplicate entry will cause the error message to pop up, forcing the user to enter a different value.