How To Prevent Duplicates On Entry

To prevent duplicate entries in a range of cells, you can use Excel's Data Validation feature.

Let's assume you want to prevent duplicate entries in cells B5:B50.

1) Select cells B5:B50;

2) From the Data tab click Data Validation;

3) Click the Settings tab;

4) Click the drop down arrow in the Allow box and select Custom. A new Formula box appears.

5) Click in the Formula box and enter this formula...

=COUNTIF($B$5:$B$50,B5)=1

The COUNTIF part of this formula looks in the range B5 to B50 and counts the number of cells that are equal to the value in cell B5. The =1 part of the formula says that any value in this range may occur only once. Therefore 1=1 equals TRUE and the entry is allowed. When you try to enter a duplicate value, the result of the COUNTIF part of the formula is 2. The 2=1 equals FALSE and the error is detected.

Note that the formula uses absolute references for the range ($B$5:$B$50) you want to apply the Data Validation criteria to and relative referencing for the input cell (B5) you want to evaluate. This is critical for this to work
Absolute Referencing uses $ symbols to lock the column and/or row references so they do not adjust when a formula is moved or copied. Relative Referencing doesn't use $ symbols, therefore when a formula is move or copied, the references adjust based on the relative position of the rows and columns.

6) Click on the Error Alert tab and type 'Duplicate Entry' in the Title box;

7) In the Error message box you can type a custom message if you want, such as 'You cannot enter duplicate values in this list!';

8) Click OK and try entering a duplicate value to test it.

Cautions: The Data Validation feature is not foolproof. This feature works only on direct user input. Duplicate values resulting from (a) data being pasted in to the range, (b) Find & Replace operations, (c) the result of calculations or (d) changes made by macros (VBA) code will override this feature.