Prevent Duplicates

Highlight the cells

Data Validation can be used to restrict the entry of data within a column.
This example shows you how to prevent duplicate entries (both text and numbers) from being entered into a column.

Note that this technique is not restricted to just columns but can also be used with rows and cell ranges (see step 6).

Find the correct formula

You can use worksheet functions in the formulas given for the data validation.
In this example we will use the COUNTIF function.
This function returns the number of non blank cells with a value that satisfies a condition.
We can use this function to tell us if an entry already exists in a particular range.
=COUNTIF($B:$B,B1)=1
(we include the relative reference B1 since we have included the whole column in the formula)

Activate the Data Validation dialog box

Highlight the cells you want to apply the data validation to. In this case the whole of column B.
Press (Data > Validation) to display the Data Validation dialog box and select the Settings tab.
In the "Allow" drop-down box, select "Custom" (or List).
Is there any difference if you entered "=1", "<=1" or "<2".

Enter a duplicate value

Type the following data onto a worksheet and try and type a duplicate entry into column B.
In this example we have tried to enter the text "France" which already exists.
The following error message will appear and the text will be removed from the cell.