Excel Boolean Operators: How to Use Them?

Microsoft Excel contains lots of functions. Those functions are inbuilt in Excel. In this article, we will discuss the Excel Boolean functions with logical operators. Note that Boolean operators are expressed as functions in Excel.

To explain the Boolean operators with practical use we will use the below data set.

This data set shows the payment of the loan with duration. The payment will be made monthly.

Dataset of Excel Boolean Operator


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to Excel Boolean Functions and Operators

The boolean operator comes from the boolean expression. Boolean expression is commonly used in programming languages. Boolean operators are used to express a boolean expression. The return of the boolean expression is 1 or 0. To understand easily we can express it as True or False respectively.

In Excel, we have 4 boolean operators/functions- NOT, OR, AND, and XOR. Now, we will have an overview of the following boolean functions in Excel.

Operator Description
NOT Basic of this function is to return a reverse result. Whatever the argument is, this will return the reverse value of that argument.
AND This compares all the arguments and if all the arguments are satisfying then return TRUE, otherwise FALSE.
OR If any of the arguments satisfy the return will be TRUE. But if all the arguments are dissatisfying then result FALSE.
XOR It is known as “Exclusive OR”. Suppose we are comparing two arguments. If any of the arguments is true, then returns TRUE. But if all the arguments are true or none of the arguments are true then return FALSE.

 

Those logical operations are performed when we apply any boolean operation.

Logical Operator Meaning Example Explanation
= Equal =B4=C4 This formula will compare B4 and C4 if both are equal return TRUE otherwise FALSE.
Greater than =B4>C4 If B4 is greater than C4 the return will be TRUE otherwise FALSE.
Less than =B4<C4 If B4 is less than C4 the return will be TRUE otherwise FALSE.
<>  Not Equal =B4<>C4 This will compare B4 and C4 and if both are not equal the return TRUE, otherwise FALSE.
>= Greater than or equal =B4>=C4 If B4 is greater than or equal to C4 return will be TRUE, otherwise FALSE.
<= Less than or equal =B4<=C4 If B4 is smaller than or equal to C4 return will be TRUE, otherwise FALSE.

4 Examples of Using Boolean Operators in Excel

Now, we will show some examples using boolean functions and operators in Excel.


1. Application of NOT Boolean Operator

Excel NOT Boolean Operator

The NOT function reverses the result. Converts TRUE into FALSE and vice-versa.

Here we will show the use of the NOT function with 3 examples.

Example 1:

We will apply the NOT function in the data set below.

Step 1:

  • Go to Cell F5.
  • Write the below code:

=NOT(D5=5)

Excel NOT Boolean Operator

Step 2:

  • Now, press Enter.

Step 3:

  • Pull the Fill Handle towards the last cell.

Application of NOT Boolean Operator

Here, we applied the NOT function with a view to see which data of the cells of the Duration column is equal to 5 years. From the result, we can see that those cells that are equal to 5 are showing FALSE and the rest are showing TRUE.

Example 2:

In this example, we will insert the IF function with the NOT function.

Step 1:

  • Write the following formula in Cell F5.
=IF(NOT(D5=5),"Yes","No")

Excel NOT Boolean Operator

Step 2:

  • Then press Enter and see the return.

Step 3:

  • Drag the Fill Handle icon towards the last cell.

As the NOT function returns the reverse logical output, we also set a negative result for each cell.

One of the advantages of using the IF function is that we can set the return argument according to our desire.

Read More: How to Use Logical Operators in Excel (11 Examples)


2. Use of Boolean AND Operator in Excel

Use of Boolean AND Operator in Excel

The AND function checks the arguments with conditions. If all the arguments satisfy conditions, then results TRUE. But if any of the arguments dissatisfy condition then returns FALSE.

Now let’s see 5 examples to be more clear.

Example 1:

Here, we will show a simple example of the AND function. We will check loans that have a duration of more than 5 years. A single condition is applied in this example.

Step 1:

  • Go to Cell F5 and put the formula below:
=AND(D5>5)

Use of Boolean AND Operator in Excel

Step 2:

  • Then press Enter to get the return.

Step 3:

  • Pull the Fill Handle icon towards the last cell.

Use of Boolean AND Operator in Excel

Here, we can see how simple to apply the AND operator.

Example 2:

In this example, we will apply multiple conditions in a single formula by applying the AND function each time. We will identify which rows contain a duration more than or equal to 5 years and the total loan is less than $100,000.

Step 1:

  • Go to the Cell F5.
  • Put the below formula which contains two conditions.
=AND(D5>=5,E5<=100000)

Use of Boolean AND Operator in Excel

Step 2:

  • Now, we can see the return after applying the formula in the below image.

So, we can apply multiple conditions with a single AND function in Excel.

Example 3:

Here, we will apply the nested AND function. Only AND function is used in the formula. Now, see what happens after applying this formula.

Step 1:

  • Write the below formula on Cell F5.
=AND(AND(D5>2,D5<10),AND(E5>50000,E5<200000))

Use of Boolean AND Operator in Excel

Step 2:

  • Press Enter and apply for the rest of the cells also.

We planned the formula in the following way. Duration is greater than 2 years and less than 10 years. And the total loan is greater than $50,000 and less than $200,000.

Example 4:

We can insert the If a function with the AND operator. In this way, we can add manipulate the result as per our likings.

Step 1:

  • Apply this formula on Cell F5.
=IF(AND(AND(D5>2,D5<10),AND(E5>50000,E5<200000)),"Success", "Failure")

Use of Boolean AND Operator in Excel

Step 2:

  • Run the formula and see what happens.

We can see that the return value is changed. “Success” and “Failure” are set instead of the default.

Example 5:

We can also apply the Cell range without individual cells along with the AND function.

We want to see if all the deposit amount is greater than $1000.

Step 1:

  • Apply the formula with range C5:C9 in Cell F5.
=AND(C5:C9>1000)

Use of Boolean AND Operator in Excel

Step 2:

  • Now, get the output after pressing Enter

Here, we simply used a cell range instead of an individual cell number. This also performs smoothly.

Read More: Reference Operator in Excel [Basics + Special Uses]


Similar Readings


3. Apply OR Operator in Excel

The OR function checks all the arguments with the condition. If any of the arguments satisfy the condition it returns TRUE. But if all the arguments dissatisfy the return will be FALSE.

Boolean OR in Excel

Example 1:

In this example, we will find rows whose duration is greater than 5 years or the total loan is greater than $90,000. We applied two conditions in a single formula.

Step 1:

  • Go to Cell F5.
  • Write the below formula on that cell-
=OR(D5>5,E5>90000)

Boolean OR in Excel

Step 2:

  • Now, press Enter.

Step 3:

  • Drag the Fill Handle icon to Cell F9.

Boolean OR in Excel

In the case of OR function, it provides TRUE as any of the conditions fulfilled.

Example 2:

We will apply cell range instead of an individual cell in this example. We want to know if the deposit money is greater than $2000.

Step 1:

  • Insert the below formula to know if any of the deposits is greater than $2000.
=OR(C5:C9>2000)

Boolean OR in Excel

Step 2:

  • Now, press Enter to get the result.

Example 3:

In this example, we will apply a nested function. AND and IF function will also be inserted in the formula. We want to find which objects have a duration greater than 5 years or a total loan greater than or equal to $90,000 and deposit money is greater than $2000.

Step 1:

  • Write the following formula on Cell F5.
=IF(OR(OR(D5>5,E5>=90000),AND(C5>2000)),"Yes","No")

Boolean OR in Excel

Step 2:

  • Now, press Enter and get the result.

Boolean OR in Excel

This is our nested output after applying the boolean operators.

Read More: What is the Order & Precedence of Operations in Excel?


4. Function of XOR Operator in Excel

The XOR operator is commonly said: “Exclusive OR”. It justifies in three ways. First, if all the arguments are true, then returns FALSE. Second, if any of the arguments is true returns TRUE. Also, if all the arguments are false returns FALSE.

To explain this operator we introduced a new data set. See the below data set.

Boolean XOR Operator in Excel

This is a coin toss game. First, each player plays 2 rounds. Head means winning of a player, and tail means loos. In the two rounds, if any player wins, i.e. gets head in both rounds, he does not need to play the 3rd round. If any player gets tails in both rounds, he will be disqualified from the game. And if the result is mixed then he will get a chance to play 3rd round. This scenario can be explained easily by the XOR operator.

 

Step 1:

  • After the 2 rounds, the result is updated in the data set.

Now, we will apply the XOR function to identify who will play the 3rd round.

Step 2:

  • Apply the formula on Cell F5.
=XOR(C5="Head",D5="Head")

Boolean XOR Operator in Excel

Step 3:

  • Then press Enter and drag won the Fill Handle

We get the result. As the result is showing in terms of TRUE and FALSE, it may be suitable all to understand easily.

We will insert the IF function to make it easier for all.

Step 4:

  • After inserting the IF function the formula will look like this.
=IF(XOR(C5="Head",D5="Head"),"Yes","No")

Boolean XOR Operator in Excel

Step 5:

  • Now, we will get a clear idea from the result below.

We can say now that, 3 players will play the 3rd round and 2 players will not play.


Conclusion

In this article, we showed different boolean operators. Added different examples of each of the boolean operators. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo