Format duplicate values except the first instance

[Fern1879]

Hello. Hoping someone can help. I've been searching online for the answer to my question, but so far, I've not come across anything. I have a list of about 600+ account numbers. Many of these numbers are duplicated and the list is sorted by account number. I want to create a conditional formatting formula that highlights the duplicate numbers, but only the 2nd or 3rd, etc instance of that account number. Not the 1st instance. I am using:

COUNTIF($K$2:$K2,$K2)>1

I applied the formula to the entire column and the result is that it is using my format on #1 and 2 below, and I would like to format #2 and 3 instead. I also tried applying it to one cell and copying the formatting down, which didn't work at all. What am I missing?

1. Account#1 23456
2. Account#1 23456
3. Account#1 23456

Any help would be greatly appreciated.

 

[Joe4]

I am using:

COUNTIF($K$2:$K2,$K2)>1

If you highlighted the WHOLE column before applying that formula, it won't work right. When applying Conditional Formatting to a multi-cell range, you need to write the formula as it applies to the VERY first cell in your selected range. Your first cell would be K1, not K2. So your formula should be:

[COLOR=#333333]=COUNTIF($K$1:$K1,$K1)>1[/COLOR]

Don't worry if your first row has headers. That formula won't highlight them (as if you are only looking at one cell, you cannot have a count of 2!).

 

[dreid1011]

There doesn't seem to be any issue with that formula. It is working for me.

Edit: Applying the CF rule to the entire column seems to be the issue. You must apply it to only the used range. Or, as Joe pointed out, change the formula to work from K1 instead of K2.

 

[Fern1879]

Thank you both so much. I changed the formula to reference K1 and only the used range. Works exactly as I wanted it to now!