Let’s take a look. Here we have the example we looked at previously. We have a single conditional formatting rule that uses a formula to highlight cells that have a value greater than the input cell. Let’s modify this rule to use two conditions, so that we can format cells that are between two numbers, using a lower and upper limit. To do that, I’ll first re-label the current input, and then make a new input for upper limit. So, at this point, we have two input cells, but only the lower limit is being used. To make use of the second input, we’ll need to edit the formula. Before we do that however, let’s build out a simple proof of concept to test out the approach we’re going to use. I’ll create this test formula directly on the worksheet. The easiest way to test for more than one condition is to use the AND function. The AND function can handle multiple conditions and will return true only if all conditions return true. So, I can build a formula with AND that checks if cell LB is greater than 500 and less than 800. Each condition is separated with a comma. Because both of these conditions are TRUE, the AND function returns TRUE. To apply conditonal formatting with a formula, this is exactly what we want. However, if I change the value in L4 so that it’s not between 500 and 800, the formula returns FALSE. For conditional formatting, this is exactly what we want. Now let’s change the formula we’re using for the conditional format to use AND function in the same way. Just like before, we use AND with two conditions. We want to check that B4 is greater than the lower limit and also check that B4 is less than the upper limit. Once we update the rule, the new formula takes effect. Now we can easily change the lower and upper limits to anything we like, and the conditional formatting instantly reflects the new conditions. By using AND function with multiple conditions, you can create flexible conditional formats that can handle almost any set of conditions you can think of. In addition, Excel also provides a function called OR, which you can combine with the AND function when you need even more flexibility.
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.
