**How To Use The Excel Functions**

OR & AND (Exercise File + Examples)

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!

**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.

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)

**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. **logical****1**, **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)**

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

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:

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.

**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.

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”)**

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.

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.

**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, **logical1**, **logical2**, 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:

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

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

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!

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:

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

**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!