Highlight Duplicates in Column using Conditional Formatting

Use Excel conditional formatting to highlight duplicate entries in a specific column, or in a range of cells (multiple rows and columns):

In Excel 2007 or later:

Select the cells to format -- range A2:A11 in this example

On the Ribbon's Home tab, click Conditional Formatting

Click Highlight Cell Rules, then click Duplicate Values

Select one of the formatting options, and click OK

For Excel 2003:

Select the cells to format -- range A2:A11 in this example

Choose Format|Conditional Formatting

From the first dropdown, choose Formula Is

For the formula, enter

=COUNTIF($A$2:$A$11,A2)>1

Click the Format button.

Select a font or fill colour for highlighting.

Click OK, click OK