How To Use The Excel Functions
OR & AND (Exercise File + Examples)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Most of Excel’s functions deal with numbers and making calculations with them.

But some functions have a different purpose: they resolve logical statements and display specific values based on the results.

This might sound complicated, but it’s actually pretty simple. And it can be extremely useful.

In this tutorial, you learn all about the function AND and OR 🙂

You also learn how to make them even more powerful by combining them with IF.

First, I show you the basics of the functions – then I show you how to use them in real-world spreadsheets.

Let’s get started!

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Understanding logical functions

Before we get started, it’s a good idea to get a handle on how logical functions work in Excel.

The main thing to keep in mind is that these functions evaluate logical statements. Which means you won’t be inputting the typical mathematical formulas that you use with other functions.

Instead, you’ll use logical operators like >, =, and <.

These operators don’t return a value like most of the other functions you’re used to.

Instead, they return TRUE or FALSE.

“What good does that do me?” you might ask. It might not seem like it at first, but it can be very useful.

Kasper Langmann, Co-founder of Spreadsheeto

Logical functions take multiple arguments that resolve to TRUE or FALSE and give you a single TRUE OR FALSE value as a result.

It’s also worth noting that Excel treats TRUEs as ones and FALSEs as zeroes. So you can design mathematical formulas that work with them.

Logical operators in Excel

Not sure which logical operators are available in Excel? Here’s what you need to know:

  • < (less than)
  • > (greater than)
  • = (equal to)
  • <= (less than or equal to)
  • >= (greater than or equal to)
  • <> (not equal to)

Get your FREE exercise file

We’ve put together an example workbook that you can use to follow along.

It’s especially useful for this lesson, as the final function we’ll go over can be a bit difficult to understand.

Grab it now and open the file.

Download the FREE Exercise File

Download exercise file

The AND function

Think for a moment about what you mean when you say “and” in normal conversation. It means that two things are true.

If you say “we’re having spaghetti and meatballs for dinner,” both “we’re having spaghetti for dinner” and “we’re having meatballs for dinner” are true.

The Excel function works in the same way.

Let’s take a quick look at the syntax:

The syntax of the AND function

=AND(logical1, [logical2], [logical3]…)

The function is very simple. logical1, logical2, and logical3 are logical statements. You can include as many logical statements as you’d like.

If all those logical statements are true, AND returns TRUE.

If one or more of them is false, AND returns FALSE.

Let’s take a look at our example workbook to see how this works.

In this sheet, we have the makes and models of cars, the colors of those cars, and the number of customer requests received for that particular combination.

We can use the AND function to see which rows contain highly requested blue cars using this formula:

=AND(C2=”Blue”, D2>6)

and-function

You can think of this function as saying “return TRUE if B2 says ‘Blue’ and C2 is greater than six.”

Kasper Langmann, Co-founder of Spreadsheeto

Hit Enter, and you’ll see that the first row returns FALSE.

Use the fill handle to drag the function down to the bottom of the list, and you’ll get only two TRUE values, including this one near the top:

and-function-resolved

Note that row 29 contains a blue car with 6 requests, which gets a FALSE result. If we’d used the >= operator instead of the > operator, it would have returned TRUE.

Remember that you can use more than two arguments for the AND function, too.

Try creating a formula that contains three arguments and resolves to TRUE at least once in this spreadsheet.

Kasper Langmann, Co-founder of Spreadsheeto

Using AND and IF together

Wouldn’t it be nice if AND returned something other than TRUE or FALSE?

With a little help from the IF function, it can!

To learn more of the cool things you can do with the IF function, check out our full guide to IF, nested IFs, and IFS.

Kasper Langmann, Co-founder of Spreadsheeto

IF is a relatively simple function that can do some powerful things. In our case, we’re going to tell it to return a specific value if our AND function returns TRUE.

Click into cell E2 and type this formula:

=IF(AND(A2=”Lincoln”, C2=”Yellow”), “Yellow Lincoln”, “Not Yellow Lincoln”)

if-and-functions

It’s not exactly clear how to read this at first. Here’s how we break it down one element at a time:

  • Check to see if A2 says “Lincoln” and B2 says “Yellow.”
  • If both of those are true, display “Yellow Lincoln.”
  • If at least one of them is false, display “Not Yellow Lincoln.”

Let’s see if the formula gets us the expected results.

if-and-results

As you can see, it does. The first row returns “Yellow Lincoln,” while the other rows—including some vehicles that are Lincolns and some that are yellow—don’t.

Using IF and AND together is actually quite simple. Just think of it as changing the output from TRUE or FALSE to any other two values you’d like.

Kasper Langmann, Co-founder of Spreadsheeto

The OR function

The OR function is a bit different from how you might think of it being used in the real world. For example, if I say “I’m going mountain biking or disc golfing today,” I won’t be doing both.

This is called an “exclusive or,” and there’s another function that serves that purpose; we’ll take a look at it in a moment.

Excel’s OR function returns true when any of the arguments are true.

Let’s take a look at the syntax:

The syntax of the OR function

=OR(logical1, [logical2], [logical3]…)

As with the AND function, logical1logical2, and logical3 are logical statements.

This time, however, the function resolves to TRUE if any of the arguments are true. It also resolves to TRUE if multiple arguments are true.

Let’s use the example workbook to try out the OR function.

How would you write a formula that returns TRUE if the car is either a Ford or blue?

Take a second to think about it.

Got it? Here’s how you’d do it:

Kasper Langmann, Co-founder of Spreadsheeto

=OR(A2=”Ford,” C2=”Blue”) gets us the information we want.

or-function-example

As you can see, any car that is either a Ford or blue (or both) causes this function to return a TRUE value.

or-function-resolved

Like AND, the OR function can take any number of arguments. Try coming up with an OR formula that includes three or more for this worksheet!

Kasper Langmann, Co-founder of Spreadsheeto

As I mentioned earlier, you can also tell Excel to evaluate an exclusive OR.

When you use the XOR function, Excel will return TRUE if one and only one of the conditions is true.

(This is probably closer to how you think of the word “or.”)

Try writing an XOR function that returns TRUE for some of the rows in the example worksheet, and FALSE for others!

Using OR and IF together

Just like with AND, you can use IF to change the values you get back from the OR function.

Let’s write a function that returns “Red or Blue” for any vehicle with a listed color of red or blue. Here’s the formula for that:

=IF(OR(C2=”Red”, “C2=”Blue”), “Red or Blue”, “Some Other Color”)

As you can see, this formula does exactly what’s expected:

if-or-results

Again, just think of this as a way to change the value you get back from an OR function.

Kasper Langmann, Co-founder of Spreadsheeto

Wrapping things up…

Excel’s logical functions—AND, OR, IF, and IFS—are different from the types of functions you’re probably used to.

But once you learn how to put them to use, you’ll probably find yourself using them a lot. There’s a reason that IF is one of the most popular Excel functions!