Boolean operators (also known as logical functions) in Excel return either TRUE or FALSE based on specific conditions.
It’s true that these functions play a crucial role in narrowing, expanding, or excluding results in databases and spreadsheets where specific information is sought.
For more comprehensive knowledge, let’s dive in-depth into these boolean functions and get to know how to use them.
Overview of Excel Boolean Operators
The terms Boolean operators and logical functions are used interchangeably in Excel. However, there is a subtle difference between these two.
Boolean operators are symbols or words used to perform logical operations. Examples of some operators are AND, OR, XOR, and NOT. On the contrary, logical functions are predefined formulas or functions that perform specific logical operations. Examples are IF, ISERROR, AND, OR, NOT, XOR, IFS, and so on.
Notice that there are some common operators in both the examples. This is because the boolean operators serve the same purpose as logical operators.
Below is a table that shows a short summary of the four boolean functions used in Excel.
| Function | Purpose | Syntax | Example | Result |
| AND | Returns TRUE if all conditions specified are true | =AND(condition1, condition2, …) | =AND(A1>10, B1=”Yes”) | This returns TRUE only if both conditions (A1 greater than 10 and B1 equals “Yes”) are met |
| OR | Returns TRUE if at least one of the specified conditions is true | =OR(condition1, condition2, …) | =OR(A1>10, B1=”Yes”) | This returns TRUE if either A1 is greater than 10 or B1 equals “Yes” |
| XOR | Exclusive OR; returns TRUE if exactly one condition is true | =XOR(condition1, condition2, …) | =XOR(A1>10, B1=”Yes”) | This returns TRUE if either A1 is greater than 10 or B1 equals “Yes,” but not both |
| NOT | Returns TRUE if the specified condition is false, and vice versa | =NOT(condition) | =NOT(A1>10) | This returns TRUE only if A1 is not greater than 10 |
Boolean operators, like AND, OR, and NOT, are the building blocks for constructing complex formulas in Excel.
When using Boolean operators, you have to define the range by specifying conditions like greater than (>) or less than (<). Additionally, in some cases, you need to type greater than or equal to (>=) properly.
A few things to note:
- In logical functions, empty cells within arguments are disregarded. However, if all arguments consist of empty cells, the formula yields a #VALUE! Error.
- If none of the arguments in a logical function evaluates to logical values, the function results in a #VALUE! Error.
- Misspelling the function’s name leads to a #NAME? Error.
- Excel 2013 imposes a maximum of 255 arguments in a single logical function. However, this limit applies only if the entire formula does not exceed 8,192 characters.
When numeric values are present in logical function arguments, zero is interpreted as FALSE, while all other numbers (including negatives) are treated as TRUE. For instance, in =AND(A1:A5), TRUE is returned if none of the cells in A1:A5 contains 0; otherwise, it returns FALSE.
Note: XOR function is available in Excel 2013 or higher versions.
How To Use Logical Functions in Excel – With Examples
In this section, we are going to dive into the use cases of the four operators (AND, OR, NOT, XOR). We can assure you that, after reading this post, you will have a complete knowledge about the uses of these operators.
Get Hands-On: Download the Practice Workbook and Work Along with the Steps
You can click below to download the practice workbook and follow along with the step-by-step instructions.
Excel Boolean Functions.xlsxUse of AND Function
Mainly, the AND function is used to test multiple conditions and returns TRUE if all the conditions are true, and FALSE if any one of them is false. Here, we will describe four use-cases.
| When To Use AND Logical Functions | Description |
| Multiple criteria filtering | Use AND to filter data based on multiple criteria. For example, you might want to filter a list to show only the rows where both conditions A and B are met. |
| Conditional formatting | Apply conditional formatting using AND to highlight cells that meet multiple criteria. This is useful for visually identifying specific patterns or values in a dataset. |
| Complex IF statements | Use AND within IF statements to create more complex conditions. This is particularly helpful when you need to perform different calculations or actions based on the satisfaction of multiple criteria. |
| Data validation | Apply data validation rules with AND to control and restrict the type of data that can be entered in a cell. This is useful for maintaining data integrity. |
Considering the above uses, we will now show you a few examples showcasing the use of AND with other built-in functions.
Syntax for AND Function
Here, logical1, logical2, etc., are conditions that you want to test. The function returns TRUE if all conditions are true, and FALSE if any one of them is false.
Some points to note:
- AND Function evaluates arguments based on the AND logic.
- The AND Function can take up to 255 conditions.
- It is often used in combination with other functions like IF, OR, and NOT to create complex logical tests.
Examples
1. Data Filtering
Look at the image below. Here, we have a dataset that captures information on product demand, showcasing two key columns:
- Product Category and
- Demand Quantity
Each product is categorized numerically, either as 1 or 2, representing different product categories. The Demand Quantity column specifies the quantity of demand associated with each product.
The Result column is generated through a logical condition: if a product falls into Category 1 and has a demand exceeding 100 units, it is labeled as High Demand; otherwise, it is marked as Low Demand.
So, we used the formula:
2. Conditional Formatting
Yes, Boolean operators can be used alongside conditional formatting in Excel. Let’s consider an example here. We’ll use more descriptive column names to represent a scenario where we are checking if a product has a High status and its color is either Red or Blue.
We are using the formula:
Select the Conditional Formatting option and select New Rule. Then select the option ‘use a formula to determine which cells to format’. Now, select the preferable color and press Enter.
Result: Green Filled box with dark green text highlighted for the High Demand category.
3. Data validation
Data validation is a feature in Excel that allows you to define restrictions on what data can be entered into a cell.
In this example, we are using,
Here’s the process:
- Select the cell or range of cells where you want to apply data validation.
- Go to the Data tab on the Excel ribbon.
- Click on Data Validation in the Data Tools group.
- In the Data Validation dialog box, choose Custom from the Allow dropdown list. This allows you to define a custom formula for validation.
- Apply the formula.
If a user attempts to enter a value outside this range, they will be prompted with an error message or warning, depending on your settings.
Use of OR Function
The OR logical function in Excel is used to test multiple conditions and returns TRUE if at least one condition is true, and FALSE if none of them is true. It’s particularly useful when you want to create more flexible criteria for data analysis.
| When To Use OR Logical Operator | Description |
| Multiple Criteria Testing | Use OR when you want to check if at least one of several conditions is met. |
| Complex IF Statements | Incorporate OR within IF statements for more complex decision-making. It allows you to execute a specific action if at least one condition is true. |
| Data Filtering | Use OR in combination with FILTER or SUMIFS functions to filter or summarize data based on multiple criteria. |
The syntax for the OR operator is:
The function returns TRUE if at least one condition is true, and FALSE if none are true.
Example
Let’s consider a new dataset with employees and their performance ratings. We want to use the OR function to identify employees who either have a rating of “Excellent” or have been with the company for more than 5 years.
So, the formula will be-
This formula checks if either the Rating (B) is Excellent or the Years with Company (C) is greater than 5 years. If either condition is true, it returns “High Performer”; otherwise, it returns “Regular Performer”.
Combining Both AND and OR functions
Let’s consider a sales dataset with products, quantities sold, and prices. We want to use both the AND and OR functions to identify products that meet specific criteria.
For this example, let’s say we want to find products that either have sold more than 50 units at a price higher than $50 each or have sold more than 100 units at any price.
The formula we used here is-
This formula checks if either:
- The quantity sold (B) is greater than 50 AND the price per unit (C) is greater than $50
- OR the quantity sold is greater than 100 AND the price per unit is greater than 0
Use of XOR Function
XOR function stands for exclusive or. It returns TRUE if an odd number of conditions are TRUE and FALSE if an even number are TRUE. The XOR function can be particularly useful when you want a result to be true only if an odd number of specified conditions are met.
Syntax for XOR function
Here, logical arguments are the conditions you want to met.
Note: Up to 254 conditions or logical expressions to be evaluated.
Example
Consider a dataset of products with columns for product names, prices, and availability.
In column D, you can use the XOR function to identify products that have both a price above $100 and are either in stock or out of stock. For example, starting from cell D2:
Copy this formula down for each row in the dataset. You can use the Fill Handle to automatically fill up the formula in the consecutive cells.
This formula checks if both conditions are met:
- The price (B) is above $100 AND
- The availability (C) is either “In Stock” or “Out of Stock”
Note: The XOR function ensures that the result is TRUE only if an odd number of conditions are TRUE.
Use of NOT Operator
The NOT function is a logical function that reverses the logical value of its argument. It returns TRUE if the argument is FALSE and FALSE if the argument is TRUE. The NOT function is particularly useful when you want to negate the outcome of a logical expression.
It has a simple syntax with only one argument.
Let’s consider a dataset of exam scores where we want to identify students who did not pass (scored less than 50). Here’s an example:
In column C, you can use the NOT function to identify students who did not pass. For example, starting from cell C2:
This formula checks if the score (B) is less than 50 and returns TRUE for students who did not pass. The NOT function negates the logical value, so it returns TRUE for students who did not pass.
Considering all the uses of these four Boolean operators, we can conclude that:
Excel does not have a distinct Boolean data type. Instead, it uses logical values that are represented by the constants TRUE and FALSE. These logical values are used in formulas, functions, and conditional expressions to perform logical tests and make decisions based on the results.
Final Words
Boolean operators (AND, OR, XOR, NOT) are crucial in Excel for logical operations. They help narrow, expand, or exclude results in databases and spreadsheets.
The AND operator checks if all conditions are true, OR returns true if at least one is true, XOR returns true if only one is true, and NOT negates a condition. These operators are often combined for sophisticated logic.
