Note: CF formulas are entered relative to the “active cell” in the selection, B5 in this case. 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: 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 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: This makes the formula easier to read and understand.
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.
