Microsoft Office Tutorials and References

In Depth Information

**Finding Duplicates by Using Conditional Formatting**

5.
Click the Format button and specify the formatting to apply when the condition is true.

Changing the fill color is a good choice.

6.
Click OK.

Figure 168-2 shows the result. The six highlighted cells are the duplicate values in the range.

Figure 168-2:
Conditional formatting causes the duplicate cells to be highlighted.

You can extend this technique to identify entire rows within a list that are identical. The trick is to

add a new column and use a formula that concatenates the data in each row. For example, if your

list is in A2:G500, enter this formula in cell H2:

=A2&B2&C2&D2&E2&F2&G2

Copy the formula down the column and then apply the conditional formatting to the formulas in

column H. In this case, the conditional formatting formula is

=COUNTIF($H$2:$H$500,H2)>1

Highlighted cells in column H indicate duplicate rows.

A feature introduced in Excel 2007 makes it easy to delete duplicate rows. See Tip 33.