Find Duplicate Data Using Conditional Formatting

People frequently have to identify duplicated data within a list or table, and doing this manually can be very time-consuming and error-prone. To make this job much easier, use Excel's standard features, Conditional Formatting.

Take, for example, a table of data with a range of $A$1:$H$100. Select the top-left cell, A1, and drag it over and down to H100. It is important that A1 be the active cell in your selection, so don't drag from H100 to A1. Select Format → Conditional Formatting... and, in the Conditional Formatting dialog box, select Formula Is from the top-left pop-up menu. In the field to its right, enter the following code:

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

Click the Format tab (that's the Format button under Mac OS X), followed by the Patterns tab, and select a color you want applied to visually identify duplicate data. Click OK to return to the Conditional Formatting dialog box and click OK again to apply the formatting.

All those cells containing duplicate data should be lit up like a Christmas tree in the color you chose, making it much easier to eyeball duplicate data and delete, move, or alter it as appropriate.

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. By this we mean that the conditional formatting formula in cell A1 will read as follows:

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

while in cell A2, it will read:

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

in cell A3, it will read:

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

and so forth.