Tom’s Tutorials for Excel: Identify unique and duplicate items in a list.
Today we have the second of Tom Urtis’ super tips to include screen shots. Enjoy!
In the example, Conditional Formatting is applied to range A2:A12.
Select range A2:A12 and then…
…in Excel version 2003 or before, from the worksheet menu:
Format > Conditional Formatting
From the drop down arrow list, select “Formula is”.
Identify unique items in the formula field with:
=COUNTIF($A$2:$A$12,A2)=1
Identify non-unique items in the formula field with:
=COUNTIF($A$2:$A$12,A2) > 1
Identify only repeated items in the formula field with:
=COUNTIF($A$2:A2,A2) > 1
…in Excel version 2007 or 2010, from the Ribbon, click the Home tab in
the Styles panel.
Conditional Formatting > New Rule > Use a formula to determine which
cells to format.
In the field below “Format values where this formula is true”, enter the
same formulas as above to format which items you want to identify.
We realize that versions 2007 and 2010 include common Conditional
Formatting circumstances in the Highlight Cell Rules flyout menu, “Duplicate Values”
being among them. This example uses duplicate identification to illustrate
how to access 2007/2010’s equivalent of 2003’s “Formula Is” field.
At this point, in any version you can click the Format button, click the Patterns tab, and select a color from the palette.
Click OK, then click OK again.
Leave a Reply