If you want to use conditional formatting to highlight cells that are “greater than X and less than Y”, you can use a simple formula that returns TRUE when a value meets those conditions. For example, if you have numbers in the range B4:G11, and want to highlight cells with a numeric value over 60 and less than 90, 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, which is assumed to be B4 in this case. Also note that we are using greater than or equal to, and less than or equal to in order to include both the lower and upper values.
Using other cells as inputs
You don’t have to hard-code the numbers into the rule and, if the numbers will change, it’s better if you don’t. To make a more flexible, interactive conditional formatting rule, use other cells like variables in the formula. For example, if you want to use cell E2 for the lower limit, and cell G2 for the upper limit, you can use this formula: You can then change the values in cells E2 and G2 to anything you like and the conditional formatting rule will respond instantly. You must use an absolute address for E2 and G2 to prevent these addresses from changing.
With named ranges
A better way to lock these references is to use a named ranges, since named ranges are automatically absolute. If you name cell E2 “lower” and the cell G2 “upper”, then you can write the conditional formatting formula like so: Named ranges allow you to use a cleaner, more intuitive syntax.
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.
