Here we have an example we looked at previously. With one conditional formatting rule that uses a formula, we’re able to highlight rows based on the task owner. This works well. But what if we want to highlight rows based on both priority and owner? In that case, we’ll need to extend the formula to handle 2 conditions. First, I’ll add an input cell for priority. I’m also name these both input cells to make the formula we’re going to create a little easier to read. I’ll call the first input “priority” and the second input “owner”. Now we can use a formula that evaluates two conditions inside the AND function. For the first condition, we use $B5 = priority (with column B locked) And for the second condition, we use $D5 = owner (with column D locked) As always, I’m using the active cell to set the reference. When I update the rule, I can now highlight both a priority and an owner. But notice that I can’t highlight just priority, or just owner. That’s because the formula is checking both conditions and won’t return true unless both priority and owner match. To fix this, we can use the OR function together with the AND function. =AND(OR(priority="",$B5=priority),OR(owner="",$D5=owner)) So, if column B = priority OR if priority is blank, return true. And if column D = owner, OR, if owner is blank, return true. When I update the formula, we can highlight based on priority, or owner, or both. However, we now have one final problem. If both priority and owner are blank, all rows are highlighted. To fix this problem, we can use the NOT function to add a final condition that only returns TRUE when priority and owner are not blank. Now we can highlight based on priority, or owner, or both. And when both inputs are blank, no roes are highlighted.
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.
