» Preventing Duplicates While Entering Data in Excel 2007

To prevent duplicates while entering data:

1. Select a range of cells, for example, A2:A20.
2. Select Data -> Data Validation (in Data Tools Group).
3. In Data Validation dialog box, select Settings -> Allow -> Custom.
4. In the Formula box, enter the following formula:
=COUNTIF($A$2:$A$20,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.
Screenshot // Preventing Duplicates While Entering Data in Excel 2007