Everything About Excel’s IF Function

​​Welcome to the COMPLETE guide on the Excel If function. We’ll go into formulas containing If, nested If statements, AND and OR. The different conditional operators that can make up the conditions of the If function. If you’re looking for some of the more advanced If functionality, you can use the ​Quick navigation below to skip to the parts you find interesting.

Video

Here’s a video we made that will walk you through the content on this page. If you prefer reading to viewing, that’s fine too. You can ​get all information​ from the text ​as well.

Also one more thing: we like to explain things ​as concise as possible here at Spreadsheet Center. Because we respect your time, we ​explain the same thing with fewer words. ​At the same time, we will still give a complete overview of everything you need to know about the If function. 

So strap in and let’s get started.

​​The Excel IF Function

​Syntax and explanation

The if function looks like this:

=IF(​Condition, ​[Value if true]​,[Value if false]​​)

Note: the square brackets [ and ] mean that the argument is optional.

​The important argument here is the first one, the ​Condition. A condition is a ​value that can be evaluated to be either TRUE or FALSE. ​

For example, ​5 = 4​ is a condition. Excel will look at ​5 = 4 and evaluate it. Meaning it will check if it is true or false. In this case, it will be evaluated to be FALSE. Why? Because 5 is not equal to 4. ​As you can imagine, the condition ​5 = ​5 would be evaluated to TRUE. There are no other options for conditions, only TRUE or FALSE.

The second and third arguments of the if function (​Value if true and ​Value if false) are just to show ​a result based on the evaluated condition.

​Different types of conditions

Comparison operators

So we can test equality using the equals sign =. But we can test more:

Inequality using <>, greater than using >, less than using <, greater than or equal to using >= and finally, less than or equal to using <=.

Example

​Take a second to look at the following formula:

 =IF(​​5​>4,​​”5 is ​greater than 4″​,​​”​5 is not greater than 4″)

​What do you think this will result in?​​​

Click here for the answer

​It will result in 5 is ​greater than 4. That’s because the condition 5 ​> 4 will evaluate to TRUE. And if the condition is TRUE, then the Value if true argument is the result of the function.

​You can try it out for yourself in the interactive spreadsheet editor below.

Spreadsheet editor

​Another type of condition: ​functions that evaluate to TRUE or FALSE

​You can ​also use other functions as conditions if they evaluate to TRUE or FALSE. For example, the ISBLANK function.

​ISBLANK(​Value) takes a value as argument and returns TRUE if the value is blank and FALSE otherwise. You would typically use it to check if a cell is empty. Like so: ISBLANK(​​A1). If you fill this in in Excel, the result will be TRUE if cell A1 is empty and FALSE otherwise.

You could combine the Isblank function with the If function into something like this:

=IF(​​ISBLANK(A1)​​”Cell A1 is ​empty”​”Cell A1 is not ​empty”)

Spreadsheet editor

Cool huh? And you can ​replace ISBLANK for any function that evaluates to TRUE or FALSE. Like ISERROR, ISTEXT, ISODD and even ISEVEN.

And, Not, Or and Xor

To create more complex conditions, there are a few helper functions. They are AND, OR and XOR. These are functions that take 2 conditions as input and return TRUE or FALSE based on the conditions.

Or

Let’s look at an example:

​=OR(5​=5,5​=4​)

​So this is the O​r function, with 2 conditions given as arguments, ​5​=5 and ​5​=4. 

If you plug this function into Excel, it will show TRUE. ​To see why, we have to look at the arguments.

One of these arguments evaluates to ​TRUE (​5​=5) and the other ​to ​FALSE (​5​=4). The Or function looks at both arguments and checks if ​argument 1 is ​TRUE ​or if argument 2 is ​TRUE (or ​both). If that is the case, Or will return TRUE. Otherwise, it will return FALSE.

​Okay, ​one-sentence recap: OR(​​a, ​b​) checks if ​aor ​bor both ​are true and then returns ​TRUE. Otherwise, it returns ​FALSE.

​The A​nd and the X​or functions are very similar.​ The only difference is for which specific values of a and b they return TRUE.

​Xor

XOR is almost the same as OR​. XOR(ab) checks if ​aor ​b​but not both ​are true and then returns ​TRUE. Otherwise, it returns ​FALSE.

And

AND(​​a,​b​) checks if both ​aand ​b are true and then returns TRUE. Otherwise, it returns ​FALSE.

Not

Not is the exception to the functions we have seen. It is also the simplest. The Not function requires 1 condition as argument and returns the opposite. So NOT(TRUE) will result in FALSE and NOT(FALSE) will result in TRUE.

Nesting IFs

What is nesting?

​​Nesting basically means putting one inside of the other. It’s kind of like those Russian ​Matryoshka dolls. You know the dolls that open up and have smaller dolls inside? And those dolls have even smaller dolls inside. It’s like that, but with functions.

We’ve done some nesting already a couple of sections back​ with the Isblank function:

=IF(​​ISBLANK(A1)​​”Cell A1 is ​empty”​”Cell A1 is not ​empty”)

The Isblank function is nested inside of the If function. That’s all. If you understand what’s going on above, you understand nesting.

Click here if you don’t understand what’s going on

If you don’t understand what’s going on, basically the ​ISBLANK(A1) argument is a function used as an argument ​of the IF function. What will happen is that ISBLANK(A1) will be evaluated by Excel. Then, it will be replaced internally by its result. SO either TRUE or FALSE, depending on if the cell A1 is empty. The result is plugged into the If function as the first argument.

So​ th​ese steps will happen:

1. =IF(​​ISBLANK(A1)​​”Cell A1 is ​empty”​”Cell A1 is not ​empty”)

​​Suppose cell A1 is empty. That means ISBLANK(A1) will evaluate to TRUE.

2. =IF(​​​TRUE​​”Cell A1 is ​empty”​”Cell A1 is not ​empty”)

​The Value if true argument is returned because the value is TRUE.

3. “Cell A1  is empty”

​Nesting Ifs inside of Ifs

Nesting If functions inside of If functions can be useful if you want to check for ​more than 2 things at once.

Suppose you own a shop and want to give a discount based on the number of items someone buys.  If cell A​2 contains the number of items bought, you could do something like:

=IF(​A​2 > 10,​”5% discount”​, ​”No discount”)

So a person gets a discount of 5 percent if they purchase more than 10 items.

Nested ifs example

But what happens if you want to add another discount ​if ​they buy even more? That’s when you would use nested Ifs:

=IF(​A​2 > 10,​ IF(​A​2 > ​20,​”​10% discount”​, ​”​5% discount”), ​”No discount”)

​Here it is in the editor:​​​

Spreadsheet editor

​Take a second to ​try and understand the formula above. Let’s ​go through it together.

The If function returns either the second or third argument based on what the first argument is. So we look at the first argument first: A​2 > 10. If ​A​2 is ​not​ ​greater than 10​, we return the third argument: No discount. If ​it ​is​ ​greater than 10, we return the second argument: another ​If function. 

When we get to the second ​If, we know that A​2 is ​greater than 10​​​​​​. Otherwise, we would not get to that function. So we can just check if A​2 is ​bigger than 20. If this is not the case, A​2 is between 10 and 20 and we return​ the small discount (5%). If it is ​bigger than 20​​​, we return a larger discount (10%)​.

See how we’ve had to jump between arguments to wrap our heads around this? That’s why nested Ifs can be a pain to understand. Don’t worry though, you will get better at this with ​practice.

​What do you think of the Excel IF function?

​Do you have any more If tips? Do you like nested ifs, or hate them? Let us know in the comments.

Leave a Comment