Prevent duplicate entries in a column

1. Select the range of cells in which you want to prevent duplicates (for example, B2:B1048576).
2. From the Data menu select Validation.
3. Click on the Settings tab.
4. From the Allow dropdown box select Custom.
5. In the formula box type =COUNTIF($B$2:$B$1048576,B2)=1 (see explanation below)
6. Click on the Error Alert tab.
7. In the Title box type "Duplicate Entry!"
8. In the Error Message box type "You cannot enter a value already in the list."

Explanation:

This formula tells Excel to count how many times the data from the current cell is included in the validation range. If the answer is equal to 1 then the formula =COUNTIF($B$2:$B$1048576,B2)=1 is TRUE and everything is OK. However, if the answer is not equal to 1, then the formula is FALSE and an error occurs causing the Error Alert.

Courtesy: http://www.theexceladdict.com/_t/t040818.htm