To Find Out Duplicate Vavlues in coloumn

**france25** · 03-30-2013, 01:19 AM

If in coloumn customer enters any number/text and if that number/text is already in column then that number/text should get highlighted with any colour so that we come to now that this is a duplicate entry. So what is the formulae for this.

Thanks for your Reply.

**benishiryo** · 03-30-2013, 01:32 AM

hi france25, welcome to the forum. select the range you want to apply to (i.e. A2:A100)
in Excel 2007 & above, go to Home -> Conditional Formatting -> Highlight Cell Rules -> Duplicate Values

in Excel 2003, go to Format -> Conditional Formatting -> Formula is:
=COUNTIF($A$2:$A$100,A2)>1

Format the colour

Edit: careless part of mine to exclude >1. Tony's formula is right.

**Tony Valko** · 03-30-2013, 10:09 AM

Try this...

Let's assume the range of interest is A2:A10.

Select the *entire* range A2:A10 starting from cell A2.
Cell A2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the menu Format>Conditional Formatting

Select the Formula Is option

Enter this formula in the box on the right:

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

Click the Format button
Select the Patterns tab
Select the desired fill color
OK out