Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.

How to create beautiful table formatting instantly...

Why to rethink the way you do VLOOKUPs...

Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...

Excel's AND & OR functions

Once you've got a handle on the TRUE and FALSE functions combined with Excel's logical operators, it's time to learn about a couple of new functions that will allow you to combine your knowledge in totally new ways: the AND and OR functions. AND and OR do just what you would expect: check to see whether multiple conditions are true in various ways.

If you aren't yet familiar with the TRUE and FALSE functions, be sure to read our TRUE and FALSE tutorial before proceeding. And, if you need to brush up on logical operators, reading our logical operator tutorial will help.

The AND function

The AND function checks to see whether multiple conditions are true, returning TRUE only if all logical statements within it are satisfied. The formula for the AND function is as follows:

You can add as many logical_expressions to the end of the function as you'd like, allowing for complex chains of logic within the function. Let's take a look at some sample applications of AND to get a better handle on how it works:

=AND(FALSE, FALSE, FALSE) Output: FALSE

=AND(FALSE, FALSE, TRUE) Output: FALSE

=AND(TRUE, TRUE, TRUE) Output: TRUE

The first and second examples above return FALSE, because not every logical_expression within them are TRUE. The third, on the other hand, returns TRUE, because every logical expression contained within the formula is TRUE.

Let's check out AND with some actual logical statements rather than the vanilla TRUE and FALSE values:

=AND(6>3, 8<12) Output: TRUE

The above returns TRUE, because both of the logical statements it contains are TRUE: six is greater than three, and eight is less than twelve.

Here are some more examples of the AND function in action, using more complex logical statements. Can you figure out why each of the following evaluates as such?

The OR function works much the same as the AND function, with one crucial difference: OR will return TRUE if one or more of the logical statements inside of it are TRUE. The formula for OR is as follows:

Like AND, OR can take as many logical_expressions as you want to feed it. Let's take a look at some examples to cement our understanding of how it works:

=OR(FALSE, FALSE, FALSE) Output: FALSE

=OR(FALSE, FALSE, TRUE) Output: TRUE

=OR(TRUE, TRUE, TRUE) Output: TRUE

The first example above returns FALSE because all of the statements within it are FALSE. The second and third, on the other hand, return TRUE because at least one of the statements within them are TRUE. Note the difference between this and the output for AND above.

Combining AND and OR

By now, you're probably wondering whether it's possible to combine AND and OR functions into a single formula. The answer is a resounding yes. You can chain AND and OR into statements as complex as you desire. When evaluating these statements, it's helpful to go from the inside out to figure out whether they are TRUE or FALSE. Start in the innermost parentheses, which are evaluated first, and work your way outward. Here's an example:

There you have it — now you can use the AND and OR functions to evaluate complex criteria easily!

Once you've mastered these functions, check out our tutorial on Excel's NOT function, which will show you the last of Excel's important logical operators. And, when you're ready to put all your knowledge together, head over to our tutorial on using IF statements in Excel.

Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.

How to create beautiful table formatting instantly...

Why to rethink the way you do VLOOKUPs...

Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...