Explanation
COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule.
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the range we are using in COUNTIF is locked with an absolute address, but B4 is fully relative. So, the rule is evaluated for each cell in the range, with B4 changing and $B$4:$G$11 remaining unchanged.
A variable number of duplicates + named ranges
Instead of hard-coding the number 1 into the formula you can reference a cell to make the number of duplicates variable. You can extend this idea and make the formula easier to read by using named ranges . For example, if you name F2 “dups”, and the range B4:G11 “data”, you can rewrite the formula like so:
=COUNTIF(data,B4)>=dups
You can then change the value in F2 to anything you like and the conditional formatting rule will respond instantly, highlighting cell that contain values greater than or equal to the number you put in the named range “dups”.
Explanation
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the address of the active cell (B5) is used for the row (5) and entered as a mixed address , with column D locked and the row left relative. When the rule is evaluated for each of the 40 cells in B5:E12, the row will change, but the column will not.
Effectively, this causes the rule to ignore values in columns B, C, and E and only test values in column D. When the value in column D for in a given row is “Bob”, the rule will return TRUE for all cells in that row and formatting will be applied to the entire row.
Using other cells as inputs
Note that you don’t have to hard-code any values that might change into the rule. Instead you can use another cell as an “input” cell to hold the value so that you can easily change it later. For example, in this case, you could put “Bob” into cell D2 and then rewrite the formula like so:
=$D5=$D$2
You can then change D2 to any priority you like, and the conditional formatting rule will respond instantly. Just make sure you use an absolute address to keep the input cell address from changing.
Named ranges for a cleaner syntax
Another way to lock references is to use named ranges , since named ranges are automatically absolute. For example, if you name D2 “owner”, you can rewrite the formula with a cleaner syntax as follows:
=$D5=owner
This makes the formula easier to read and understand.
