Basic IF Statement

Here is how you write an IF function in Excel:

= IF ( logical_test , [value_if_TRUE] , [value_if_FALSE] )

The first two attributes are obligatory, i.e. without them, the formula doesn’t work, and produces an error message.

  • logical _test – a placeholder for the condition we want to test
  • value_if_TRUE – an attribute where we need to specify a value (text, or another function) that the IF formula returns when the result of the logical_test is TRUE

The third parameter is Optional, i.e. we can skip it and in such a case, the IF function returns the default value FALSE.

  • value_if_FALSE – an attribute for a value, text or another formula that the IF function must return when the result of the logical test is FALSE.

In other words, IF function is used to test the specific conditions and can be described as the statement:

IF something is true, THEN return the first value, ELSE – return the second value.

The simplest IF statement may be written with the first two parameters only. Here is an example:

 =IF(C2=1, "Hello!")

If C2 equals 1, the condition is met and the IF formula returns the text “Hello!”. Otherwise, it returns the default value FALSE.

Here is another simple formula with the IF function:

=IF(C3="Hello!", 1, 0)

If the value of the cell C3 is the text “Hello!” then this formula returns the value one (1).

Else (i.e. if this cell contains any other text, number or blank value), the formula returns zero.

IF Formula with Logical Tests

When using the IF function to construct a logical test in Excel, we can use the following logical operators:

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

Let’s say you want to use the IF function to see if a student has passed or failed an exam.

If their score is greater than or equal to 50, they pass, and you’ll get a “Pass.” If their score is less than 50, they fail, and it will show “Fail.”

Suppose the student’s score is in cell B1. Here’s how you set up the IF statement with a logical test:

=IF(B1>=50, "Pass", "Fail")
  • The logical test is: B1 >=50
  • If the logical test is met (value_if_true): “Pass”
  • Else (value_if_false): “Fail”

IF Formula to Return Another Formula as a Result

The IF function can even give you a formula as an answer. For example, if C1 is under 500, it calculates C1 times 8%.

If C1 is 500 or more, the IF statement calculates C1 times 10%.

=IF(C1 < 500, C1 * 8%, C1 * 10%)

The IF function in Excel is a universal function, i.e. it accepts as an attribute different types of a value, such as numeric value, text, date/time, other functions, named ranges and others).

Nested IF statements

The IF function is versatile and can be part of more complex formulas. For example, you can use multiple IF functions together, known as nested IF statements, to handle several conditions at once.

Nested IF statements are useful when you need Excel to evaluate multiple conditions sequentially. If the first condition isn’t met, Excel moves on to the next one, and so forth. This allows you to create formulas that can return different results based on varied criteria without referring to specific values or outcomes in your explanation.

Take a look at the screenshot below. In this scenario, we use a formula to check a revenue value:

  • If it’s over 200, the formula will output “flag”.
  • If it’s under 50, it will instead output “follow up.”
Nested IF statements
= IF(Revenue > 200, "flag", IF(Revenue < 50, "follow up", ""))

This means if the value is over 200, the formula will output “flag”. If it’s under 50 and the first condition is not met, it will output “follow up”. Otherwise, it will leave the cell empty.

Using many IF statements in Excel makes formulas complex and hard to manage. Also, adding other functions can slow down Excel, especially with large datasets. These formulas are more likely to contain errors, which are tough to find and fix.

Pro Tip: To simplify nested IF statements, consider using the IFS function. It allows you to manage multiple conditions more efficiently. You can do this without nesting IF statements. For more detailed guidance, check out our article on the IFS function in Excel.

Now, let’s look at some practical examples.

IF Function Examples

Return Text if a Logical Condition is Met

Return Text if a Logical Condition is Met

This is a typical example of the IF function in Excel: Let’s flag all apps if their Revenue is above 15,000.

Step 1. Go to the first cell where we need to insert a formula (cell C5 in this example).

Start the formula with an equal sign (=), add the keyword IF and the opened parentheses to make the formula look like:

=IF(

Step 2. The first argument of the IF function is a logical test.

In this example, we want to flag a value in cell B5 (Revenue) if it is above 15000.

So, let’s continue the formula =IF(B5>15000,

Do not forget to put a comma once this step is ready.

This coma is used by Excel as a delimiter to separate the arguments from each other.

Pro tip: We can either type a numeric value in the logical test or use a cell reference to a cell that contains such a value (C2 cell in our example). The second approach is preferred because it gives us more flexibility in the future. Once we copy/paste the formula to the entire range, all we need to do in this case – if want to change that value – is go to the referral cell and change it there without needing to re-write the formula for all related cells.

NOTE: Keep in mind that if we want to use this approach, we need to fix the cell reference to look like $C$2. This fixed cell address is required for copying the formula to the entire range. Without that, the formula will return the wrong result. We can simply press F4 to fix the cell reference in the formula (make sure that the cursor is located near the appropriate cell address)

The formula becomes:

=IF(B5>$C$2,

Step 3. The second argument of IF function, as we remember, is a value_if_TRUE.

Let’s type the text “Good” as a second attribute in the formula:

=IF(B5>$C$2, "Good",

Do not forget to put a comma once this step is ready.

Also, be careful – if you type a text value, you need to put it inside a double quotation mark (“your text”)

Pro tip: We can either type a numeric value in the logical test or use a cell reference to a cell that contains such a value (C3 cell in our example). If in the future we’ll decide to change the “Good” word to something else (“Not Bad”, for example), then all we need to do is go to cell C3 and change that text in the cell without the necessity to re-do the entire formula.

Our formula becomes:

=IF(B5>$C$2,$C$3,

NOTE: Don’t forget to fix the cell reference to look like $C$3 address. Press F4 to make an absolute reference. Without that, the formula will return a wrong result.

Step 4. The third argument of the IF() function is value_if_FALSE. We can skip this attribute (in such cases Excel returns the default value – FALSE). However, if we want to do that in a more aesthetic way, let the formula return a blank value. The blank value in Excel is equal to nothing between two quotation marks, like “”. So, the final version of the formula becomes:

=IF(B5>$C$2,$C$3,"")

NOTE: Don’t forget to close the brackets and press ENTER.

Step 5. Drag the formula down to the entire range.

Make a quick check manually to ensure that the formula returns the correct result according to our expectations.

Using IF Function with AND

Using IF Function with AND

Let’s flag all apps if their Revenue is above 15,000 but under 20,000.

Step 1. Go to the first cell where we need to insert a formula (cell D5 in this example).

Start the formula with an equal sign (=), add the keyword IF and the opened parentheses to make the formula look like:

=IF(

Step 2. The first argument of the IF function is a logical test.

In this example, we want to flag a value in cell B5 (Revenue) if it is above 15000 and less than 20,000.

This is a typical situation when we can use the AND logical function to unite several certain conditions into a single logical test.

AND function can accept up to 255 conditions as its arguments.

Let’s continue the formula and add the second keyword AND with an opened parenthesis.

Once we’ve done that, we can type both conditions for the logical test inside the AND block and close its second parenthesis.

The formula looks like:

=IF(AND(B5>$D$2, B5<$D$3),

NOTE: Don’t forget to fix the cell reference to look like $D$2 and $D$3 address.

Press F4 to make an absolute reference. Without that, the formula will return a wrong result after we drag it down to the entire range.

NOTE: Don’t forget to use a comma once this step is ready.

This comma is used by Excel as a delimiter to separate the arguments from each other.

Step 2. The second argument of IF function in Excel, as we remember, is a value_if_TRUE.

Let’s add another cell reference as a second attribute in the formula:

=IF(AND(B5>$D$2, B5<$D$3), $C$3,

NOTE: Don’t forget to put a comma after the attribute once this step is ready. Be careful. If you type a text value, you need to put it inside a double quotation mark (“your text”)

Step 3. The third argument of the IF function is a value_if_FALSE.

We can skip this attribute and Excel returns the default value – FALSE.

However, the more aesthetic way to do that is to return nothing (or blank value).

Nothing in Excel is denoted as a double quotation mark without any space:

=IF(AND(B5>$D$2, B5<$D$3), $C$3, "")

NOTE: Don’t forget to close the bracket and press ENTER.

Step 4. Drag the formula down to the entire range.

Make a quick check manually to ensure that the formula returns the correct result according to our expectations.

Nested IF Statement

Nested IF Function in Excel

This time we’ll flag all apps as “Good” if their Revenue is above 15,000 and under 20,000. In addition, we’ll flag them as “Exceptional” if their Revenue is greater than or equal to 20,000

Step 1. Go to the first cell where we need to insert a formula (cell E5 in this example).

Start the formula with an equal sign (=), add the keyword IF and the open parentheses to make the formula look like:

=IF(

Step 2. The first argument of the IF function in Excel is a logical test.

The first part of the formula in this example is the same as we wrote in the previous example:

=IF(AND(B5>$E$2, B5<$E$3), $C$3,

NOTE: Don’t forget to to put a comma after the attribute once this step is ready.

Step 3. Usually, the third argument of the IF function is a value_if_FALSE.

However, this time we need to add the second condition when a value in cell B5 is greater than or equal to 20,000.

We can do that if we use nested IF function. Let’s add another IF statement as a third parameter now:

=IF(AND(B5>$E$2, B5<$E$3), $C$3, IF(

Step 4. Add the second logical test, and the second value_if_TRUE.

In our example, the formula must return the “Exceptional” keyword if the value in cell B5 is greater than or equal to 20,000.

Let’s type this:

=IF(AND(B5>$E$2, B5<$E$3), $C$3,  IF(B5>=$E$3, "Exceptional",

NOTE: If you type a value as a text, you need to put it inside a double quotation mark (“your text”)

Step 5. We need to write a value that the formula will return if the result of both logical tests is FALSE.

In our example, the formula must return the raw Revenue value in this case.

Let’s place this attribute as the third argument in the second IF statement:

=IF(AND(B5>$E$2, B5<$E$3), $C$3, IF(B5>=$E$3, "Exceptional", B5))

NOTE: Don’t forget to close the brackets.

We created two IF statements, so we need to close two brackets at the end.

Step 6. Drag the formula down to the entire range.

Make a quick check manually to ensure that the formula returns the correct result according to our expectations.

Using IF Function with OR

Using IF Function with OR

Let’s flag all apps if their Revenue is <= 15,000 or >= 20,000

Step 1. Go to the first cell where we need to insert a formula (cell D5 in this example).

Start the formula with an equal sign (=), add the keyword IF and the open parentheses to make the formula look like:

=IF(

Step 2. The first argument of the IF function is a logical test.

In this example we want to flag a value in cell B5 (Revenue) if it is below or equal to 15000 OR above or equal to 20,000.

This is a situation when we can use OR logical function that allows the testing of multiple logical conditions and return a true value if ANY of them is true.

Let’s continue the formula and add the second keyword OR with an opened bracket.

Once we’ve done that, we can type both conditions for the logical test inside OR() block and close its second parentheses.

The formula looks like:

=IF(OR(B5>=$F$2, B5=<$F$3),

NOTE: Press the F4 button on the keyboard to make an absolute reference.

Step 3. The second argument of the IF function, as we remember, is a value_if_TRUE.

Let’s add this attribute in the formula:

=IF(AND(B5>$D$2, B5<$D$3), "Flag",

NOTE: If you type a text value, you need to put it inside a double quotation mark (“your text”)

Step 4. The third argument of the IF function is a value_if_FALSE.

Let’s add a double quotation mark without any space to return a blank value:

=IF(AND(B5>$D$2, B5<$D$3), "Flag", "")

NOTE: Don’t forget to close the bracket and press ENTER.

Step 5. Drag the formula down to the entire range.

Quickly check to ensure that the formula returns the correct result.

Using Other Formulas Inside the Logical Functions

Using Other Formulas Inside the Logical Functions of the IF Function in Excel

Let’s calculate the difference between Actual Revenue and Budget Revenue and flag those cases when the difference is larger than +/- 10%.

Step 1. The easiest way to start building such a complex formula is to calculate the core value (this is the +/-10% threshold value in our case).

Step 2. Go to the first cell where we need to insert a formula (cell D23 in this example).

Start the formula with an equal sign (=), add the formula to calculate the difference (Actual divided by Budget minus one:

=B23/C23 - 1

Step 3. Let’s add the IF statement and the appropriate logical test.

This is a situation when we can use the OR logical function as we need to flag a value if it is greater than +10% or lower than -10%:

=IF(OR(B23/C23 - 1>10%, B23/C23 - 1<-10%),

Step 4. The second argument of the IF function in Excel, as we remember, is a value_if_TRUE.

In this example we need to return the exact deviation, so let’s do that, by simply copying the initial formula.

=IF(OR(B23/C23 - 1>10%, B23/C23 - 1<-10%), B23/C23 - 1,

Step 5. The third argument of the IF function is a value_if_FALSE.

Let’s add a double quotation mark without any space to return a blank value:

=IF(OR(B23/C23 - 1>10%, B23/C23 - 1<-10%), B23/C23 - 1,"")

NOTE: Don’t forget to close the bracket and press ENTER.

Step 6. Drag the formula down to the entire range.

Quickly check to ensure that the formula returns the correct result.

Using Icons as the Result of the IF Function in Excel

Using Icons as the Result of the IF Function in Excel

Let’s mark the positive deviation with the up arrow and the negative deviation with the down arrow.

Step 1. We’ll continue the previous example.

Let’s say we want to mark all positive deviations with a symbol that looks like an up arrow and all negative deviations with a symbol that looks like a down arrow.

First of all, a few preparations.

Let’s go to the cells above the test range (A20 and A21 cells in this example).

Feel free to select any other cells if you prefer.

Step 2. Insert the symbols that will indicate all positive and negative deviations.

To do this, we need to go on the INSERT tab on the Excel Ribbon and follow the path: INSERT > Symbol > Arial > Geometric Shapes

Use Icons in IF Function in Excel

Click Insert once you have selected a shape.

Go to another cell below and insert the second arrow in the same way.

Step 3. Go to the first cell in the range where we want to add these visual indicators.

Replicate the IF function with the cell references containing the arrows as the value_if_TRUE and value_if_FALSE attributes.

=IF(B23/C23 - 1>10%, $A$20, IF(B23/C23 - 1<-10%, $A$21, ""))

NOTE: Don’t forget to close the bracket and press ENTER.

Step 4. Drag the formula down to the entire range.

Quickly check that the formula returns the correct result.

Workbook for Download

Feel free to Download the Workbook HERE.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.