Apply Multiple Conditions in Conditional Formatting in Excel

In Excel, you can use conditional formatting to apply multiple conditions on a single cell or a range of cells. And there are two ways to do that:

• Apply the rule twice.
• Or you use a formula to test two conditions at the same time.

In this tutorial, we will learn to use these methods in detail.

Multiple Conditions in Conditional Formatting (Two Conditions)

1. First, select the range of cells where you want to apply the conditions and go to Conditional Formatting > Highlight Cells Rules > Greater Than.
   
2. From here, in the dialog box, enter 1500 and select formatting to apply to the cells greater than 1500. Once you do this, click OK to apply the rule.
   
3. Again, go to the Conditional Formatting > Highlight Cells Rule > Less Than.
   
4. After that, in the dialog box, enter the 700 and select the formatting you want to apply to the range. Once you do that, click OK and apply the rule.
   

Now here you have two conditions applied to the same range of the cells. In the first condition, it highlights the cells where the value is greater than 1500; in the second condition, it highlights the cells where value is lower than 700.

Multiple Conditions in Conditional Formatting (Single Formula)

In this part of the condition, you must use the formula to test two conditions in a single rule.

1. First, select the range of cells, and go to Conditional Formatting > New Rule.
   
2. From the dialog box, click on “Use a formula to determine which cell to format”.
   
3. After that, in the formula input bar, enter the formula =IF(OR(A1>1500,A1<700),true,false), and then click on the “format…” to specify format which you want apply cells.
   
4. In the end, click OK to apply the rule.

Now you have all the highlighted cells with values below 700 or above 1500.

Using Multiple Conditions with Multiple Columns

If you want to highlight cells by applying multiple conditions to two different columns, you can do that.

=IF(AND(B1="a",OR(A1<700,A1>1500)),TRUE,FALSE)

In the above conditional formatting, we have used three conditions to test:

• In column B, it needs to be the value “a”.
• And in column A, the value needs to be below 700 or above 1500.

That means it will test one condition first from column B, then go to column A and test two more conditions there to highlight cells.