Tip Printed from ExcelTip.com

---

Preventing Duplicates While Entering Data


To prevent duplicates while entering data:

1. Select a range of cells, for example, A2:A20.
2. From the Data menu, select Validation.
3. Select the Settings tab.
4. From the Allow dropdown list, select Custom.
5. In the Formula box, enter the following formula:
=COUNTIF($A$2:$A$20,A2)=1
6. Select the Error Alert tab.
7. In the Title box, enter Duplicate Entry.
8. In the Error message box, enter "The value you entered already appears in the list above".
9. Click OK.