Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm using Excel 2010 and would like to know if there's any way I can choose conditional formatting to highlight only instances of duplicates when it's found in ALL the columns I chose?

For instance, I have six columns of data, with duplicates in them, but I only want those duplicates to be highlighted if they appear in ALL SIX columns.

E.g.
Column A Dog Cat Fish Horse Platypus
Column B Cat Platypus Panda Chicken Dog
Column C Bird Zebra Giraffe Platypus Panda
Column D Platypus Bird Dog Zebra Horse
Column E Otter Lion Platypus Giraffe Zebra
Column F Lion Ostrich Platypus Dog Snake

Only "Platypus" appears in all six columns, but "Dog", "Cat", "Horse", etc all have one or more duplicates, which will usually end up being highlighted. If I can find a solution that will allow me to have the flexibility of choosing to highlight instances of duplicates in 5 out of 6 columns, 4 out of 6 columns, 3 out of 6 columns, etc, that'd be even better!

Note that the data is not arranged nicely in a row, so that I couldn't use "Countif" across rows to see how many times "dog" appeared in Column A-L on a specific row (i.e. "dog" could appear anywhere in the columns, not necessarily on the same row).

If anyone has any tips on this, I'd really appreciate it, thanks!!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Set up a conditional formatting rule for the entire table with the following formula:

=AND(COUNTIF($A$1:$A$6,A1)>0,COUNTIF($B$1:$B$6,A1)>0,COUNTIF($C$1:$C$6,A1)>0,COUNTIF($D$1:$D$6,A1)>0,COUNTIF($E$1:$E$6,A1)>0,COUNTIF($F$1:$F$6,A1)>0)

This checks that the value of the cell is in each column.

Example: enter image description here

To highlight values that are duplicated in 5 columns, you can use the following rule:

=SUM(COUNTIF($A$1:$A$6,A1)>0,COUNTIF($B$1:$B$6,A1)>0,COUNTIF($C$1:$C$6,A1)>0,COUNTIF($D$1:$D$6,A1)>0,COUNTIF($E$1:$E$6,A1)>0,COUNTIF($F$1:$F$6,A1)>0)=5

This can be customized to other numbers of duplicates by changing the last number of the formula.

Example: enter image description here

share|improve this answer
    
Hi Excellll, am I understanding your formula correctly that it means I must first manually identify the values that are duplicates (i.e. the A1 you have in your formula) and then plug it into the conditional formatting rule? –  C. Y Y Aug 9 '12 at 15:27
    
@C.YY No, not at all. A1 is just the top-left cell in the range you're applying the rule to. Since A1 is a relative reference, the rule will apply to each cell in the range, with A1 replaced by the address of each cell. I.e., the rule applied to B2 will be the formula with all instances of A1 changed to B2. This is done automatically -- one rule should find them all. –  Excellll Aug 9 '12 at 16:30
    
In case that isn't clear, the key is that formulas for conditional formatting are written to apply to the top-left cell of the target range. The formula is applied to each other cell as if it is being filled down and across the range. That is, the relative cell references are offset, and fixed references stay the same. –  Excellll Aug 9 '12 at 16:33
    
Oh I see, that makes a lot of sense. Thank you soooo much for the help, you saved me hours of staring at the screen (and the potentially errors with that). Thanks a lot also for the detailed explanation and examples, you're awesome! –  C. Y Y Aug 10 '12 at 16:59
    
Hi Excellll, actually I have another question now...I went through my data set and managed to highlight all the duplicates I wanted to, but now I'd like to essentially "count the formatted cases" i.e. determine how many values appeared in all 6columns, all 5 etc. I know using the formula you gave would be a good start, but the value always turns out "FALSE". What am I doing wrong? –  C. Y Y Aug 10 '12 at 20:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.