Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 10 Find Duplicate Data using Conditional Formatting

Excel's conditional formatting
is generally used to identify values in particular ranges, but we can
hack it to identify duplicated data within a list or
table.

People frequently have to identify
duplicated data within a list or table. Doing this manually can be
very time-consuming and error-prone. To make this job much easier,
you can hack one of 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 dragging from H100 to A1 isn't quite
the same. 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, as is 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.

If you need to identify data that appears two or more times, you can
use conditional formatting with three different conditions and
color-code each condition for visual identification. To do this,
select cell A1 (the cell in the top lefthand corner of table) and
drag it down to H100. Again, it is important that A1 is the active
cell in your selection.

Now select Format Conditional
Formatting... and, from the box containing the text
"Cell Value Is", select Formula Is.
In the box to the right of Formula Is, enter the following code:

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

Click the Format tab and then the
Patterns page tab, and select a color you want to apply to identify
data that appears more than three times. Click OK, then click Add,
and from the Condition 2 box, select Formula Is and enter the
following formula:

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

Instead of retyping the formula, highlight it from the Condition 1 box, press Ctrl/-C to copy, click the Formula box for Condition 2, press Ctrl/-V to paste, and then change >3 to =3.

Click the Format tab and then the Patterns page tab and select a
color you want to apply to identify data that appears three times.
Click OK, and then click Add. From the Condition 3 box, select
Formula Is and enter the following formula:

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

Finally, click the Format tab and then the Patterns page tab. Select
a color you want to apply to data that appears twice. Click OK. 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, as is 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.