Excel Boolean logic: How to sift spreadsheet data using AND, OR, NOT, and XOR

By JD Sartain of PCWorld.com

In Excel, Boolean logic (a fancy name for a simple condition that’s either true or false) is one way to sift specific data or results from a large spreadsheet. Granted, there are other ways to search a spreadsheet, including Lookup functions and pivot tables. The reason to bone up on Boolean logic is because it's a method you can use in other applications, like search engines and databases. 

Boolean operators, which Excel calls logical functions, include AND, OR, NOT, and a new function called XOR. These operators are used between search terms to narrow, expand, or exclude your results in databases, spreadsheets, search engines, or any situation where you’re seeking specific information. We'll walk you through all four. 

Boolean basics

The simplest definition for each operator is this:

AND – returns TRUE if all conditions specified are true

Example: =AND (100<200, 200>100) TRUE because both conditions are true

OR – returns TRUE if at least one of the specified conditions is true

Example: =OR(100<200, 100>300) TRUE because one of the conditions is true

NOT – returns true if condition specified is NOT met (reverse logic)

Example: =NOT(100>500) TRUE because 100 is NOT greater than 500

XOR – also called Exclusive OR, returns true if either argument (but not both) is true

Examples:
=XOR(1+1=2, 2-1=2) returns TRUE because one condition is true and one is false

=XOR(1+1=2, 2-1=1) returns FALSE because both conditions are true

=XOR(5+1=2, 5-1=2) returns FALSE because both conditions are false

A few more things to note:

When you're searching for a range of results via Boolean operators, you define the range by what it's more than or less than. 

Excel 2013 allows a maximum of 255 arguments in a single logical function, but only if the formula does not exceed 8,192 characters.

Boolean operators may start out looking simple. When combined with other functions, however, such as IF statements, you can create some complex formulas that produce very powerful results.

Boolean AND, IF-AND

When you're trying to find something that meets multiple criteria, AND is your operator. For example: One of the actors in George’s play broke his leg, so George needs another actor, immediately, with very specific skills and appearance. In order to fit the costumes, the new guy must be 68 to 69 inches tall, must weigh between 180 and 200 pounds, and must be aged between 30 and 50.

If George’s list of actors contained only 50 to 100 names, he could scan the list and locate a replacement himself. But the Guild Actors database contains 20,000 records, so he needs a faster way to narrow the search.

For this query, you can use one of the following three formulas. All three formulas work and all are similar, except the AND statement only returns True or False. The IF statements allow custom responses such as “Match” or “Qualified.” 

Copy the database and formulas shown in figure 02 and experiment with the results.

A. AND statement using AND Boolean operators (with three conditions): returns true or false.

=AND(AND(C6>67,C6<70),AND(D6>179,D6<201),AND(E6>29,E6<51)) = TRUE

B. IF/AND statement using AND Boolean operators (with three conditions): returns Yes or No because the IF statement says: If this, and this, and this is true, then answer Yes; else/otherwise, answer No.

=IF(AND(AND(C8>67,C8<70),AND(D8>179,D8<201),AND(E8>29,E8<51)),”Yes”,”No”)

C. IF statement using AND Boolean operators (with three conditions): returns Yes or No.

In this case, if any of the AND statements are not met, the response will return False and the multiplication (asterisk) result will be 0 (False). This format often appears when your syntax has an error and Excel repairs it (after asking you if you would like assistance).

=IF(AND(C10>67,C10<70)*AND(D10>179,D10<201)*AND(E10>29,E10<51),”YES”,”NO”)

Note: Notice how Excel color-codes the formulas to the matching cells, including the opening and closing parentheses, in an effort to help you understand the syntax of each condition in the formula.

 02 Formulas that use AND, IF/AND, & IF statements.

Boolean OR, AND-OR

The first database search returned 1100 actors. George wants to narrow the results further, so he queries those 1100 results for two very specific skills: This actor must speak fluent Italian or French AND have a vocal range of tenor or bass. 

Use the following formula for this query:

=OR(OR(C9="Italian",C9="French"),AND(OR(D9="tenor",D9="base"))) = TRUE

Remember, for the answer to be true, the actor must speak Italian OR French AND sing tenor OR bass.

Any incorrect information produces a FALSE response.

Copy the database and formulas shown in figure 03 and experiment with the results. Once again, note how Excel color-codes the formulas to the matching cells, including the opening and closing parentheses, in an effort to help you understand the syntax of each condition in the formula.

03 Formula that uses OR, AND-OR operators

Boolean NOT, NOT-OR

The easiest way to explain the NOT operator is to compare it to an Internet search. If you searched online for your old friend Jack Russell just by typing his name, you'd get hundreds of hits for dogs and puppies, too. With the NOT operator, you can search for “Jack Russell NOT dogs NOT puppies” to remove the canine variable.

George needs some background performers to dance and play a variety of instruments—but not the piano, because pianists can't dance around, and not ballroom dancing, because he wants them to dance with their instruments, not with human partners. George queries the database again and specifies NOT piano AND NOT ballroom dancing.

Remember, this is reverse logic, so NOT piano and NOT ballroom equals FALSE because he doesn’t want ballroom and he doesn’t want piano. Think of FALSE as “No, not this person.” Notice also that record 3 (Feyd-Rautha) says guitar and ballroom. Guitar is good, but ballroom is bad, so the response is FALSE because George doesn’t want ballroom (even though guitar is okay). Same situation for record 4 (Piter De Vries), piano, waltz. Since only one is acceptable and not both, both are rejected.

Use the following formula for this query, then copy the database shown in figure 04 and experiment with the results.

=NOT(OR(C5="piano", D5="ballroom"))

04 Formula that uses NOT, NOT-OR operators

Meet XOR, also known as Exclusive OR

Just when you thought you had Boolean logic in the bag, Excel 2013 introduced the new operator XOR, which means Exclusive OR. Think of it as a similar opposite of NOT: If one condition is true and one is false, XOR returns TRUE. If both conditions are true, or both conditions are false, XOR returns FALSE.

Use the following formula for this query and then copy the database shown in figure 05 and experiment with the results.

=XOR(C5="piano", D5="ballroom")

05 Formula that uses XOR operator

Once you get comfortable with Boolean operators, you have a new skill for finding specific records in a sea of cells. Better yet, you can branch out to use Boolean logic to to refine Internet searches, database searches, and more. 

Read more on PCWorld.com...