In Excel, the IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if that condition is True or False.

But what if you need to test multiple conditions, where let’s say all conditions need to be True or False (AND), or only one condition needs to be True or False (OR), or if you want to check if a condition does NOT meet your criteria? All 3 functions can be used on their own, but it’s much more common to see them paired with IF functions.

Technical Details

Use the IF function along with AND, OR and NOT to perform multiple evaluations if conditions are True or False.

Syntax

• IF(AND()) – IF(AND(logical1, [logical2], …), value_if_true, [value_if_false]))

• IF(OR()) – IF(OR(logical1, [logical2], …), value_if_true, [value_if_false]))

• IF(NOT()) – IF(NOT(logical1), value_if_true, [value_if_false]))

Here are overviews of how to structure AND, OR and NOT functions individually. When you combine each one of them with an IF statement, they read like this:

• AND – =IF(AND(Something is True, Something else is True), Value if True, Value if False)

• OR – =IF(OR(Something is True, Something else is True), Value if True, Value if False)

• NOT – =IF(NOT(Something is True), Value if True, Value if False)

Examples

Following are examples of some common nested IF(AND()), IF(OR()) and IF(NOT()) statements in Excel. The AND and OR functions can support up to 255 individual conditions, but it’s not good practice to use more than a few because complex, nested formulas can get very difficult to build, test and maintain. The NOT function only takes one condition.

Here are the formulas spelled out according to their logic:

Note that all of the examples have a closing parenthesis after their respective conditions are entered. The remaining True/False arguments are then left as part of the outer IF statement. You can also substitute Text or Numeric values for the TRUE/FALSE values to be returned in the examples.

Here are some examples of using AND, OR and NOT to evaluate dates.

Here are the formulas spelled out according to their logic:

Using AND, OR and NOT with Conditional Formatting in Excel

In Excel, you can also use AND, OR and NOT to set Conditional Formatting criteria with the formula option. When you do this you can omit the IF function and use AND, OR and NOT on their own.

In Excel, from the Home tab, click Conditional Formatting > New Rule. Next, select the “Use a formula to determine which cells to format” option, enter your formula and apply the format of your choice.

Edit Rule dialog showing the Formula method” />

Using the earlier Dates example, here is what the formulas would be.

Note: A common error is to enter your formula into Conditional Formatting without the equals sign (=). If you do this you’ll see that the Conditional Formatting dialog will add the equals sign and quotes to the formula – =”OR(A4>B2,A4, so you’ll need to remove the quotes before the formula will respond properly.