Highlight consecutive duplicate cells

[oalessia]

Dear all,

I woukld like to use conditional formatting in excel to highlight only consecutive duplicate cells.

for example if:

A1 4
A2 5
A3 5
A4 7
A6 5
A7 8

I would like only cells A2 and A3 to be highlighted, but if I use the 'highlight duplicate cells' command I get also A6. can anyone help me?

Thank you.

alessia

 

[yky]

You can create two rules:

rule 1: =if(a1=a2,true,false)
and set the highlight format when true
rule 2: =if(a2=a1,true,false)
and set the highlight format when true

Then, apply the rules to the range of the cells you want.

 

[yky]

Oops! I screwed it. Should be

=if(a2=a1,true,false)
=if(a2=a3,true,false)

 

[oalessia]

Oops! I screwed it. Should be

=if(a2=a1,true,false)
=if(a2=a3,true,false)

Hi YKY,

thank you for your reply. I must be doing something wrong because now it highlighted all the cells!

here is what I did: in 'conditional formatting' > new formatting rule > use a formula to determine... > format values where this formula is true: =if(a2=a1,true,false) =if(a2=a3,true,false)

then in the format tab I selected highlight 'yellow'
OK

Any help would be greatly appreciated.

thanks
Alessia

 

[yky]

Hi YKY,

thank you for your reply. I must be doing something wrong because now it highlighted all the cells!

here is what I did: in 'conditional formatting' > new formatting rule > use a formula to determine... > format values where this formula is true: =if(a2=a1,true,false) =if(a2=a3,true,false)

then in the format tab I selected highlight 'yellow'
OK

Any help would be greatly appreciated.

thanks
Alessia

Are you creating two rules?

 

[Weazel]

You could try something like....

=OR(A2=A1,A2=A3)

This takes for granted that your list of values starts in A2 so you'd need to shift your column down

Similar setup in conditional formatting but should only require one rule

Make sure you select the range prior to applying the formatting.

 

[oalessia]

You could try something like....

=OR(A2=A1,A2=A3)

This takes for granted that your list of values starts in A2 so you'd need to shift your column down

Similar setup in conditional formatting but should only require one rule

Make sure you select the range prior to applying the formatting.

It works! thank you very much.
best,
Alessia