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:
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
Highlighted cells in column H indicate duplicate rows.
A feature introduced in Excel 2007 makes it easy to delete duplicate rows. See Tip 33.