If you’ve encountered the word “Boolean” but aren’t quite sure what it means, this is the guide for you! From definitions of key terms to easy-to-follow examples, here’s everything you need to know about Boolean logic in Microsoft Excel.
Boolean Logic: Key Definitions
Here are four key terms that are used in this article and are essential to understanding how Boolean logic works:
- Boolean logic: The method used to evaluate conditions, returning either TRUE or FALSE.
- Boolean values: The two Boolean values are TRUE and FALSE. When used in formulas, TRUE is equal to 1, and FALSE is equal to 0.
- Logical functions: The primary logical functions in Excel that enable you to apply Boolean logic are AND, OR, XOR, NOT, IF, and variations of these.
- Logical operators: Mathematical symbols used to compare values to return a Boolean value. The comparison operators in Excel are = (equal to), > (greater than), < (less than), >= (greater than or equal to), =< (less than or equal to), and <> (not equal to).
Boolean Values: TRUE and FALSE
The results of Boolean logic are always TRUE if the stated conditions are met, or FALSE if they’re not.
For example, typing:
=15=15
into a cell in Excel and pressing Enter returns TRUE, because the value before the equal logical operator is the same as the value after the equal logical operator. In other words, 15 is equal to 15, so the result is TRUE.
Every formula in Excel begins with the equal (=) symbol. This isn’t to be confused with the equal logical operator, which is used between conditions in Boolean logic to compare equality.
Similarly, in this example, typing:
=B2=B3
returns FALSE, because the value in cell B2 is not equal to the value in cell B3.
Although the results in the above examples appear to be textual, Boolean values actually carry numerical values. Specifically, TRUE = 1, and FALSE = 0. You can test this by using the SUM function to add cells containing these Boolean values. Here, typing:
=A1+B1
returns 1, because you’re adding a TRUE value (1) to a FALSE value (0).
Let’s look at Boolean values in action in a real-world scenario.
Imagine you’re tracking the number of deliveries each employee has made over a four-day period. Each worker has 100 items to deliver, and you want to use Boolean logic to find out whether they’ve completed them all.
To do this, in cell F2, type:
=B2+C2+D2+E2=100
and press Enter.
Because the values in cells B2, C2, D2, and E2 equal 100 when added together, the result is TRUE.
However, when you select cell F2 and double-click the fill handle to apply the formula to the remaining cells, you can see that the Boolean values in cells F3 and F4 are FALSE, because the number of items offloaded for each of those employees doesn’t equal 100.
To visualize the Boolean values more clearly, you can turn them into checkboxes, where TRUE generates a checked checkbox, and FALSE generates an unchecked checkbox. Select all the cells containing Boolean values, and in the Insert tab on the ribbon, click “Checkbox.”
Now, if you update the figures so that employee B’s totals equal 100, the Boolean value turns to TRUE, and, thus, the checkbox is checked.
On the other hand, armed with the knowledge that checked checkboxes are equal to 1, you can use them to track task progress. Here, typing:
=B2*C2*D2=1
into cell E2, returns TRUE if all checkboxes are checked (1*1*1=1), or FALSE if any of the checkboxes are unchecked (for example, 1*0*1=0).
You can then turn these Boolean values into checkboxes themselves if you wish.
Logical Functions: AND, OR, XOR, NOT, and IF
So far, I’ve shown you how Boolean logic works to test whether one thing equals another. However, you can use Excel’s logical functions to test more than one condition, check whether one thing doesn’t equal another, or return alternative values to TRUE and FALSE.
AND, OR, and XOR: Testing Multiple Conditions
As their names suggest, the AND, OR, and XOR logical functions evaluate more than one condition using Boolean logic to return either TRUE or FALSE:
|
Function |
What It Does |
Example Formula |
Result |
|---|---|---|---|
|
=AND |
Returns TRUE if all conditions are met, or FALSE if any of the conditions aren’t met |
=AND(A1=A2,B1=B2) |
Returns TRUE if the value in cell A1 equals the value in cell A2 and if the value in cell B1 equals the value in cell B2 |
|
=OR |
Returns TRUE if any or all of the conditions are met, or FALSE if none of the conditions are met |
=OR(A1=A2,B1=B2) |
Returns TRUE if the value in cell A1 equals the value in cell A2 or if the value in cell B1 equals the value in cell B2, or both |
|
=XOR |
Returns TRUE if an odd number of conditions are met, or FALSE if an even number of conditions are met |
=XOR(A1=A2,B1=B2) |
Returns TRUE if only one of the conditions is met, or FALSE if both conditions are met |
In this example, the AND function returns TRUE for employees A and D because both conditions (the Induction and Probation columns containing “Y”) are met.
=AND(B2="Y",C2="Y")
Any text values used in logical formulas, whether in the logical test or the return values, must be placed inside double quotation marks. The only exceptions to this are TRUE and FALSE, as these are considered Boolean values as opposed to textual values. Numerical values do not need double quotation marks.
Here, the OR function returns TRUE for employee B in cell D2, because even though the value in cell B3 isn’t equal to “Gold,” the value in cell C3 is equal to “Senior,” so one of the conditions is met. On the other hand, employees A, C, and D meet neither of these two criteria, so their outcomes are FALSE.
=OR(B3="Gold",C3="Senior")
The XOR function is best used when there’s a choice of A or B, as it allows you to track whether neither, both, or only one option is selected.
Here, employees can select either healthcare plan A or healthcare plan B. If they check only one of these options, the XOR function returns TRUE. However, if they check neither or both options, the function returns FALSE.
=XOR(B2=TRUE,C2=TRUE)
You can also nest these logical functions to create even more specific conditions.
In this example, the Boolean logic returns TRUE if (1) the value in column B is either “Sun” or “Overcast,” and (2) the value in column C is “Y.”
=AND(OR(B3="Sun",B3="Overcast"),C3="Y")
The AND, OR, and XOR functions allow up to 255 conditions.
NOT: Reversing Boolean Logic
The NOT function in Excel essentially flips the Boolean logic by returning TRUE if the conditions are not met, or FALSE if the conditions are met.
In this example, the NOT function returns TRUE if the values in column B do not match the values in column C:
=NOT(B2=C2)
As with the examples above, you can nest other logical functions inside a NOT formula to evaluate multiple conditions. Here, typing:
=NOT(AND(B2="Good",C2="Good"))
returns TRUE if the U.S. and U.K. ratings for a restaurant are not both “Good.”
On the other hand, using the same logic with NOT and OR, this formula returns TRUE if neither the U.S. nor the U.K. ratings are “Good.”
=NOT(OR(B2="Good",C2="Good"))
IF: Returning Custom Values
While Boolean logic primarily returns Boolean values (TRUE or FALSE), you can force Excel to return alternative values using the IF function.
The syntax for the IF function is as follows:
=IF(a,b,c)
where
- a is the Boolean logical test, which can be evaluated as either TRUE or FALSE,
- b is the value to return instead of TRUE, and
- c is the value to return instead of FALSE.
Argument a is mandatory, and both or either arguments b and/or c must also be provided. If you omit argument b or argument c, the formula will return TRUE or FALSE in its place.
To return a blank cell instead of a value for TRUE or FALSE, type “” (two double quotation marks) for argument b or c.
Suppose you’re recruiting staff for your business. Specifically, you want to interview candidates who meet the following criteria:
- They have the silver or gold certification,
- They have provided referees on their resume, and
- They can travel.
If they meet the above criteria, you want to return “Interview” in the status column. However, if they don’t meet them, you want to return “Send rejection.”
To do this, in cell E2, type:
=IF(AND(OR(B2="Silver",B2="Gold"),C2="Y",D2,"Y"),"Interview","Send rejection")
Here’s what’s happening with this formula:
- The IF function uses Boolean logic and allows you to return “Interview” instead of TRUE, and “Send rejection” instead of FALSE,
- The OR function tests whether the value in column B is either “Silver” or “Gold,” and
- The AND function checks whether, as well as having silver or gold certification (column B), the candidate has provided referees (column C) and can travel (column D).
To evaluate multiple conditions and return a result for the first one that returns TRUE, use the IFS function.
Logical Operators: Comparing Values in Boolean Logic
If you are using Boolean logic to evaluate numerical values, you don’t just have to use the equal sign. Indeed, you can test whether a value is greater than, less than, greater than or equal to, less than or equal to, or not equal to another value.
In this example, if a student has scored 75 or more, you want to return “Pass” in column C. If they haven’t, you want to return “Fail.”
The formula to achieve this is as follows:
=IF(B2>=75,"Pass","Fail")
You can use Boolean logic and logical operators with dates, where the “greater than” symbol (>) means “later than,” the “less than” symbol (<) means “earlier than.”
Here, typing:
=IF(B2>TODAY(),"Coming soon","Available now")
returns “Coming soon” if the date in column B is later than today’s date, or “Available now” if the date in column B is earlier than today’s date.
For dates to work as expected in Excel formulas, make sure the value is set to a recognized date format in the Number group of the Home tab on the ribbon.
If you’re including the date within the formula (rather than referencing a cell containing the date), use the DATEVALUE function, with the date wrapped inside double quotation marks, then inside parentheses:
=IF(B2<DATEVALUE("2025/12/25"),"Yes","No")
Now that you know how to apply Boolean logic to your figures in Microsoft Excel, take this knowledge one step further by making the most of the power of the COUNTIF, SUMIF, and AVERAGEIF functions to count, sum, and average cells that match certain criteria.
