If you want to highlight cells that are “greater than X” with conditional formmatting, you can use a simple formula that returns TRUE when a cell value is greater than X. For example, if you have numbers in the cells B4:G11, and want to highlight cells with a numeric value over 100, you select B4:G11 and create a conditional formatting rule that uses this formula: It’s important that the formula be entered relative to the “active cell” in the selection. To highlight cells less than 100 with a conditional formatting formula, use:
Using a another cell as an input
Note that there is no need to hard-code the number 100 into the rule. To make a more flexible, interactive conditional formatting rule, you can use another cell like a variable in the formula. For example, if you want to use cell G2 as an input cell, you can use this formula: You can then change the value in cell G2 to anything 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. Another way to lock the reference is to use a named range, since named ranges are automatically absolute. Just name cell G2 “input” then write the conditional formatting formula like so:
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.
