Let say i want to check column D to see if there's duplicates.

I normally use this formula (in conditional formatting) to color the cell if there's multiple same value ( exact same value) :


But now i want to check if there's the similarity in first 3 letters, For example i want this to be warned as duplicates error.


I tried to use wildcards , it works on cell but not working when i use in conditional formatting.

=countif($D$1:$D ; left($D1,3) & "*")>1

Does anyone have a solution ?

1 Answer 1


The conditional formatting custom formula rule you quote should work just fine. Check that the Apply to range setting in the formatting rule is correct — it should start from row 1.

To skip blank cells, use this:

=len($D1) * (countif($D$1:$D , left($D1,3) & "*") > 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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