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.

**Table of Contents**hide

**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**

**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)`

**Step 2:**

- Now, press
**Enter**.

**Step 3:**

- Pull the
**Fill Handle**towards the last cell.

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")`

**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**

**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)`

**Step 2:**

- Then press
**Enter**to get the return.

**Step 3:**

- Pull the
**Fill Handle**icon towards the last cell.

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)`

**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))`

**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")`

**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)`

**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]**

**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**.

**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)`

**Step 2:**

- Now, press
**Enter**.

**Step 3:**

- Drag the
**Fill Handle**icon to**Cell F9**.

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)`

**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")`

**Step 2:**

- Now, press
**Enter**and get the result.

This is our nested output after applying the boolean operators.

**Read More:** **What is the Order of Operations in Excel (An Ultimate Guide)**

**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.

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")`

**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")`

**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.

**Read More:** **‘Not Equal to’ Operator in Excel (With 5 Examples)**

**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.