I have been looking at a lot of help articles about how to highlight duplicate values, but I can't find one that does exactly what I am trying to do.

I was hoping to highlight cells when the value in the 1st cell in column 1 equals the value in the 1st cell in column 2, and the same all the way down. So if A5=B5 I want it to be highlighted, but not highlight if A5=B6, and not highlight if A5=A6.

This is a visual example:

 1  3
 4  5
*2  2*
 3  1
*5  5*
 7  1
 4  2

So you can see, the two 4s in the first column aren't highlighted, because I don't want duplicates highlighted in the same column. And the two 3s in both columns aren't highlighted, because I don't want them highlighted unless they're in the same row.

I hope this makes sense. Is there a way to do this?


Select column A or whatever range you'd like the formatting to apply to and use the following formula under conditional formatting (select to use a formula for the criteria)


choose whatever fill you'd like

|improve this answer|||||
  • Have fun visiting the review queues, which are accessible to you now that you have > 500 reputation points (and congratulations). – G-Man Says 'Reinstate Monica' Jun 13 '15 at 16:02
  • Thank you. It's an honor and a pleasure to join the ranks. – Petr Fedorov Jun 14 '15 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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