Here we have a table that contains employee data.
Let’s say we want to highlight people in this table who belong to group A. I’ll use a helper column to help illustrate.
This is because structured references are not recognized inside a conditional formatting rule.
The workaround is to use regular references.
In this case, I need to use:
F5 equals A, with column F locked.
This allows the formula to highlight an entire row.
Now I’ll use this formula to create the conditional formatting rule.
As you’re free to see, the rule correctly highlights employees in group A. I can verify this by checking the range inside the rule.
Next, I’ll extend the rule a bit to make the worksheet more interactive.
That way a user can change the group being highlighted any time they want.
Notice Excel uses anabsolute referenceby default, which is what we need in this case.
