If you want to use conditional formatting to highlight cells that are NOT between two values (a lower and upper limit), you can use a simple formula that returns TRUE when a value meets that condition. For example, if you have numbers in the range B4:G11, and want to highlight cells with a numeric value not between 20 and 110, 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 because we are using less than (<) and greater than (<), the lower and upper limit will not be included.
How this formula works
When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply the rule to B4:G11, with B4 as the active cell, the rule is evaluated for each of the 40 cells in B4:G11 because B4 is entered as a fully relative address. Because we are using OR with two conditions, the formula returns TRUE when either condition return TRUE, triggering the conditional formatting.
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:
Note: Excel contains many built-in "presets" for highlighting values above / below / between / equal to certain values, but if you want more flexibility you can apply conditional formatting using your own formula as explained in this article. If you...
The AND function is a logical function used to require more than one condition at the same time. AND returns either TRUE or FALSE. To test if a number in A1 is greater than zero and less than 10, use =AND(A1>0,A1...
In this video, we look at how to make conditional formatting variable, so that you can change how formatting is applied without editing a rule. This elegant technique helps you build killer spreadsheets that are fun to use.
In this Exceljet tip, we show you how to highlight entire rows in a list using conditional formatting with a formula. This is a handy way to visually expose information you are interested in without having to apply a filter. We also show you how to...
In this video, we use conditional formatting to create an interactive search box to highlight data. This is a nice alternative to filtering, because the information you're looking is highlighted in context, and the search box checks multiple...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.