Conditional Formatting: can't highlight all duplicate instances

[Limone]

Hi everyone.

I've got a formula that's supposed to highlight every duplicate in a column, except for the "F" character. My problem is that only the first instance of a duplicate value gets highlighted unless I type the COUNTIFS range as an absolute reference and I don't get why.

The reason I'd like to get Excel to highlight every duplicate while using a relative reference is that the conditional formatting rule will have to be dragged both down and to the right so that I can cover every part of the table without using a costumized formula for every column (which with the table being a calendar are as many as the number of days in a month).

Here are my formulas.

=COUNTIFS($D$8:$D$16;D8;$D$8:$D$16;"<>F")>1

or

=COUNTIFS(D8:D16;D8;D8:D16;"<>F")>1

 

[Joe4]

Th

e reason I'd like to get Excel to highlight every duplicate while using a relative reference is that the conditional formatting rule will have to be dragged both down and to the right so that I can cover every part of the table without using a costumized formula for every column

Try locking JUST the row part of the range reference in your formulas, and not the column part, i.e. D$8 (instead if $D$8).

It is important to understand "mixed" range references.
See: https://www.excel-easy.com/functions/cell-references.html

 

[Limone]

That worked. Thank you very much for the help!

 

[Joe4]

You are welcome.