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. Conditional formatting works well in a table, but you can’t use structured references directly when you create the rule. I’ll use a helper column to help illustrate. To highlight people in group A using structured references, we’d want to use a formula like this: Translated, this means: the value in the current row of the Group column equals “A”. You can see the formula returns TRUE for all rows where the group is A, and FALSE for any other group. However, if I try to use this formula to define a conditional formatting rule, Excel will complain. 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 can see, the rule correctly highlights employees in group A. Even though we can’t use structured references, we still get some benefit from using a table, because Excel will keep track of the table range. If I add some new data to the table, the table expands and Excel automatically extends the conditional formatting rule to cover the entire range. 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. I’ll first put an “A inside cell F2, and then edit the formula to refer to F2. That way a user can change the group being highlighted any time they want. Notice Excel uses an absolute reference by default, which is what we need in this case. Back on the worksheet, I can now change the value in F2 to any valid group, and Excel will automatically highlight the relevant rows.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
