We have a list of parts in Excel in a certain order. For reasons I won't get into, we need to highlight when there are duplicates that aren't next to each other. Currently, I'm using this formula in a conditional format to do the job.


This mostly works well except in cases where there are pairs of duplicates like in the example below, we would want FO-1694 to be highlighted, because they aren't all next to each other. But we would not want NIS0257 to be highlighted because they are.


Ideally, this is what we want to see...


Any ideas on this could be achieved using conditional formatting?

  • If it is possible, it needs to be done with conditional formatting and a formula. Each cell needs to be checked against the rest of the column for dupes, but ignore x-1 and x+1 where x is the current row. I don't know how to exclude x-1 and x+1. Good luck!
    – Wutnaut
    Oct 14, 2014 at 18:33
  • So if all instances of a number aren't in sequential cells, they need to be highlighted? Why not use a macro? Oct 14, 2014 at 18:37
  • @Raystafarian, there are already so many VBA macros that execute on cell change that the worksheet is slow to work with sometimes. And that's after turning off ScreenUpdating. Calculation, and EnableEvents. So, I really want to avoid adding anymore to it than I have to. Oct 14, 2014 at 18:50

2 Answers 2


The solution below was posted on StackOverflow by Scott Craner to the same question (posted there because there was no working answer here):

Use this formula:


enter image description here

If changing the range then make sure to pay attention to what is marked as absolute and relative.

Source answer Highlight Duplicates Not Next To Each Other Using Conditional Formatting by Scott Craner

  • Please read How to reference material written by others
    – DavidPostill
    Jan 30, 2016 at 22:22
  • I feel that I did as the rules said to. Am I missing something? I'm sorry, I don't mean to cause any trouble. I tried to make it very clear that I had no part in creating the answer. Jan 30, 2016 at 22:24
  • Let me fix it for you.
    – DavidPostill
    Jan 30, 2016 at 22:26
  • Thank you, I appreciate it! Sorry, I'm new at referencing others' answer. I'll note the format for in the future. Jan 30, 2016 at 22:28

This works for me:

Rule #1:

Rule: =$A1=$A2

Applies to: =$A$1:$A$15

Formatting: None (No Fill, etc)

Rule #2:


Applies to: =$A$1:$A$15

Formatting: None (No Fill, etc)

Rule #3:

Just normal "Duplicate Values"

Formatting: What ever you like (Highlight in Red)

See below:

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .