Using logical functions in Excel: AND, OR, XOR and NOT

Excel logical functions - overview

Microsoft Excel provides 4 logical functions to work with the logical values. The functions are AND, OR, XOR and NOT. You use these functions when you want to carry out more than one comparison in your formula or test multiple conditions instead of just one. As well as logical operators, Excel logical functions return either TRUE or FALSE when their arguments are evaluated.

The following table provides a short summary of what each logical function does to help you choose the right formula for a specific task.

In additions to the four logical functions outlined above, Microsoft Excel provides 3 "conditional" functions - IF, IFERROR and IFNA.

Using the AND function in Excel

The AND function is the most popular member of the logic functions family. It comes in handy when you have to test several conditions and make sure that all of them are met. Technically, the AND function tests the conditions you specify and returns TRUE if all of the conditions evaluate to TRUE, FALSE otherwise.

The syntax for the Excel AND function is as follows:

Where logical is the condition you want to test that can evaluate to either TRUE or FALSE. The first condition (logical1) is required, subsequent conditions are optional.

And now, let's look at some formula examples that demonstrate how to use the AND functions in Excel formulas.

Using the OR function in Excel

As well as AND, the Excel OR function is a basic logical function that is used to compare two values or statements. The difference is that the OR function returns TRUE if at least one if the arguments evaluates to TRUE, and returns FALSE if all arguments are FALSE. The OR function is available in all versions of Excel 2016 - 2000.

The syntax of the Excel OR function is very similar to AND:

Where logical is something you want to test that can be either TRUE or FALSE. The first logical is required, additional conditions (up to 255 in modern Excel versions) are optional.

And now, let's write down a few formulas for you to get a feel how the OR function in Excel works.

As well as Excel AND function, OR is widely used to expand the usefulness of other Excel functions that perform logical tests, e.g. the IF function. Here are just a couple of examples:

IF function with nested OR

=IF(OR(B2>30, C2>20), "Good", "Bad")

The formula returns "Good" if a number in cell B3 is greater than 30 or the number in C2 is greater than 20, "Bad" otherwise.

Excel AND / OR functions in one formula

Naturally, nothing prevents you from using both functions, AND & OR, in a single formula if your business logic requires this. There can be infinite variations of such formulas that boil down to the following basic patterns:

=AND(OR(Cond1, Cond2), Cond3)

=AND(OR(Cond1, Cond2), OR(Cond3, Cond4)

=OR(AND(Cond1, Cond2), Cond3)

=OR(AND(Cond1,Cond2), AND(Cond3,Cond4))

For example, if you wanted to know what consignments of bananas and oranges are sold out, i.e. "In stock" number (column B) is equal to the "Sold" number (column C), the following OR/AND formula could quickly show this to you:

=OR(AND(A2="bananas", B2=C2), AND(A2="oranges", B2=C2))

OR function in Excel conditional formatting

=OR($B2="", $C2="")

The rule with the above OR formula highlights rows that contain an empty cell either in column B or C, or in both.

For more information about conditional formatting formulas, please see the following articles:

• Excel conditional formatting formulas
• Changing the row color based on a cell's value
• Changing a cell's color based on another cell value
• How to highlight every other row in Excel

Using the XOR function in Excel

In Excel 2013, Microsoft introduced the XOR function, which is a logical Exclusive OR function. This term is definitely familiar to those of you who have some knowledge of any programming language or computer science in general. For those who don't, the concept of 'Exclusive Or' may be a bit difficult to grasp at first, but hopefully the below explanation illustrated with formula examples will help.

The syntax of the XOR function is identical to OR's :

The first logical statement (Logical 1) is required, additional logical values are optional. You can test up to 254 conditions in one formula, and these can be logical values, arrays, or references that evaluate to either TRUE or FALSE.

In the simplest version, an XOR formula contains just 2 logical statements and returns:

• TRUE if either argument evaluates to TRUE.
• FALSE if both arguments are TRUE or neither is TRUE.

This might be easier to understand from the formula examples:

When more logical statements are added, the XOR function in Excel results in:

• TRUE if an odd number of the arguments evaluate to TRUE;
• FALSE if is the total number of TRUE statements is even, or if all statements are FALSE.

The screenshot below illustrates the point:

If you are not sure how the Excel XOR function can be applied to a real-life scenario, consider the following example. Suppose you have a table of contestants and their results for the first 2 games. You want to know which of the payers shall play the 3^rd game based on the following conditions:

• Contestants who won Game 1 and Game 2 advance to the next round automatically and don't have to play Game 3.
• Contestants who lost both first games are knocked out and don't play Game 3 either.
• Contestants who won either Game 1 or Game 2 shall play Game 3 to determine who goes into the next round and who doesn't.

A simple XOR formula works exactly as we want:

=XOR(B2="Won", C2="Won")

And if you nest this XOR function into the logical test of the IF formula, you will get even more sensible results:

=IF(XOR(B2="Won", C2="Won"), "Yes", "No")

Using the NOT function in Excel

The NOT function is one of the simplest Excel functions in terms of syntax:

You use the NOT function in Excel to reverse a value of its argument. In other words, if logical evaluates to FALSE, the NOT function returns TRUE and vice versa. For example, both of the below formulas return FALSE:

=NOT(TRUE)

=NOT(2*2=4)

Why would one want to get such ridiculous results? In some cases, you might be more interested to know when a certain condition isn't met than when it is. For example, when reviewing a list of attire, you may want to exclude some color that does not suit you. I'm not particularly fond of black, so I go ahead with this formula:

=NOT(C2="black")

As usual, in Microsoft Excel there is more than one way to do something, and you can achieve the same result by using the Not equal to operator: =C2<>"black".

If you want to test several conditions in a single formula, you can use NOT in conjunctions with the AND or OR function. For example, if you wanted to exclude black and white colors, the formula would go like:

=NOT(OR(C2="black", C2="white"))

And if you'd rather not have a black coat, while a black jacket or a back fur coat may be considered, you should use NOT in combination with the Excel AND function:

=NOT(AND(C2="black", B2="coat"))

Another common use of the NOT function in Excel is to reverse the behavior of some other function. For instance, you can combine NOT and ISBLANK functions to create the ISNOTBLANK formula that Microsoft Excel lacks.

As you know, the formula =ISBLANK(A2) returns TRUE of if the cell A2 is blank. The NOT function can reverse this result to FALSE: =NOT(ISBLANK(A2))

And then, you can take a step further and create a nested IF statement with the NOT / ISBLANK functions for a real-life task:

=IF(NOT(ISBLANK(C2)), C2*0.15, "No bonus :(")

Translated into plain English, the formula tells Excel to do the following. If the cell C2 is not empty, multiply the number in C2 by 0.15, which gives the 15% bonus to each salesman who has made any extra sales. If C2 is blank, the text "No bonus :(" appears.

In essence, this is how you use the logical functions in Excel. Of course, these examples have only scratched the surface of AND, OR, XOR and NOT capabilities. Knowing the basics, you can now extend your knowledge by tackling your real tasks and writing smart elaborate formulas for your worksheets.