Microsoft Office Tutorials and References
In Depth Information
Tip 60: Finding Duplicates by Using Conditional Formatting
4. For this example, enter this formula (change the range references to correspond to your own
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 60-2 shows the result. The seven highlighted cells are the duplicated values in the range.
Figure 60-2: Conditional formatting causes the duplicated 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: