Find Data That Appears Two or More Times Using Conditional Formatting

If you need to identify data that appears two or more times, you can use Conditional Formatting with as many conditions/rules as your system memory will allow (note that you can use up to three different conditions only in versions pre-2007) and color-code each condition for visual identification. To do this, select cell A1 (the cell in the top-left corner) and drag it down to H100. Again, it is important that A1 is the active cell in your selection.

Now, select Home → Conditional Formatting → NewRule under Styles options. For Select Rule Type, choose "Use a Formula to determine which cells to format" and then click in the white strip under "Format values where this formula is true."

Pre-2007, select Format → Conditional Formatting... and, from the box containing the text Cell Value Is, select Formula Is. Click in the white box to the right of Formula Is.

Enter the following formula:

=COUNTIF($A$1:$H$100,A1)>3

Click the Format button, select a color you want to apply to identify data that appears more than three times, and click OK; or for pre-2007 versions, go to the Patterns page tab, select a color, and click OK.

Click NewRule (on the Mac, click Add>>) and repeat the previous steps, entering the following formula (pre-2007, use the Condition 2 box and select Formula Is) and selecting a different color this time:

=COUNTIF($A$1:$H$100,A1)=3

Instead of retyping the formula, highlight it after you have entered it, then press Ctrl-C to copy, then paste where necessary by pressing Ctrl-V to paste, and then change >3 to =3.

Click NewRule, repeat the steps a third time (pre-2007, from the Condition 3 box, select Formula Is), and add the following formula:

=COUNTIF($A$1:$H$100,A1)=2

Again, select a different color from those previously chosen. You will have different cell colors depending on the number of times your data appears within your table of data.

Again, it is vital to note that as A1 was the active cell in your selection, the cell address is a relative reference and is not absolute (unlike your table of data, $A$1:$H$100). By using conditional formatting in this way, Excel automatically knows to use the correct cell as the COUNTIF criterion. In Excel 2007, your Conditions are limited only by your system memory, whereas in pre-Excel 2007 versions, the limit is 3 Conditions.