Excel IF Function | Formula Examples + FREE Video

Excel IF Function – Introduction

Excel IF Function

When to use Excel IF Function

IF function in Excel is best suited for situations where you check whether a condition is TRUE or FALSE. If it’s TRUE, the function returns a specified value/reference, and if not then it returns another specified value/reference.

What it Returns

It returns whatever value you specify for the TRUE or FALSE condition.


=IF(logical_test, [value_if_true], [value_if_false])

Input Arguments

  • logical_test – this is the condition that you want to test. It could be a logical expression that can evaluate to TRUE or FALSE. This can either be a cell reference, a result of some other formula, or can be manually entered.
  • [value_if_true] – (Optional) This is the value that is returned when the logical_test evaluates to TRUE.
  • [value_if_false] – (Optional) This is the value that is returned when the logical_test evaluates to FALSE.

Important Notes About using IF Function in Excel

  • A maximum of 64 nested IF conditions can be tested in the formula.
  • If any of the argument is an array, each element of the array is evaluated.
  • If you omit the FALSE argument (value_if_false), i.e., there is only a comma after the  value_if_true argument, the function would return a 0 when the condition is FALSE.
    • For example, in the example below, the formula is =IF(A1>20,”Approve”,), where the value_if_false is not specified, however, the value_if_true argument is still followed by a comma. This would return 0 whenever the checked condition is not met.Excel If Function - When False is ommitted
  • If you omit the TRUE argument (value_if_true), and specify only the value_if_false argument, the function would return a 0 when the condition is TRUE.
    • For example, in the example below, the formula is =IF(A1>20,,”Approve”), where the value_if_true is not specified (only a comma is used to then specify the value_if_false value). This would return 0 whenever the checked condition is met.Excel If Function - When TRUE is ommitted

Excel IF Function – Examples

Here are five practical examples of using the IF function in Excel.

Example 1: Using Excel IF function to Check a Simple Numeric Condition

When using Excel IF function with numbers, you can use a variety of operators to check a condition. Here is a list of operators you can use:

Excel If Function - Operators

Below is a simple example where students marks are checked. If the marks are more than or equal to 35, the function returns Pass, else it returns Fail.

Excel If Function - pass fail

Example 2: Using Nested IF to Check a Sequence of Conditions

Excel IF Function can take up to 64 conditions at once. While it’s inadvisable to create long Nested IF functions, in the case of limited conditions, you can create a formula that checks conditions in a sequence.

In the example below, we check for two conditions.

  • The first condition checks whether the marks are less than 35. If this is TRUE it returns Fail.
  • In case the first condition is FALSE, which means that the student scored above or equal to 35, it checks for another condition. It checks if the marks are greater than or equal to 75. If this is true, it returns Distinction, else it simply returns Pass.

Excel If Function - Nested

Example 3: Calculating Commissions Using Excel IF Function

Excel If Function allows you to perform calculations in the value section. A good example of this is calculating the sales commission for sales rep using the IF function.

In the example below, a sales rep gets no commission if the sales are less than 50K, gets a 2% commission if the sales are between 50-100K and 4% commission if the sales are more than 100K.

Here is the formula used:


Excel If Function - Commission

In the formula used in the example above, the calculation is done within the IF function itself. When the sales value is between 50-100K, it returns B2*2%, which is the 2% commission based on the sales value.

Example 4: Using Logical Operators (AND/OR) in Excel IF Function

You can use logical operators (AND/OR) within the IF function to test multiple conditions at once.

For example, suppose you’ve to select students for the scholarship based on marks and attendance. In the example shown below, a student is eligible only if he scores more than 80 and has the attendance of more than 80%.


You can use the AND function within the IF function to first check whether both these conditions are met or not. If the conditions are met, the function returns Eligible, else it returns Not Eligible.

Here is the formula that will do this:



Example 5: Converting all Errors to Zero using Excel IF function

Excel IF function can also be used to get rid of cells that contain errors. You can convert the error values to blanks or zeros or any other value.

Here is the formula that will do it:


The formula returns a 0 when there is an error value, else it returns the value in the cell.

NOTE: If you’re using Excel 2007 or versions after it, you can also use the IFERROR function to do this. 

Similarly, you can also handle blank cells. In case of blank cells, use the ISBLANK function as shown below:


Excel If Function - isblank

Excel IF Function – Video Tutorial

Related Excel Functions:

Other Excel tutorials you may find useful:

Excel Ebook Subscribe


Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

6 thoughts on “Excel IF Function | Formula Examples + FREE Video”

  1. Hi, i have required different amount with find out percentage
    as like below – Required amount against percentage only one columns

    800000 0.05%
    900000 0.09%
    1000000 0.12%
    1200000 0.14%
    1400000 0.15%
    1600000 0.17%
    1800000 0.18%
    2000000 0.19%
    2200000 0.21%
    2400000 0.23%
    2600000 0.24%
    2800000 0.25%
    3000000 0.27%

    please suggest me.

  2. How can we get true or false result in if function when we use it to get the information from named range.
    Eg. Colors name is defined as named range”colour_name”
    =if (and (cellB2 =”red”, cellB2 =named range” colour_ name”),”true”,”false”.

    I couldn’t find the text (color) using “if formula” from the named range.
    How can i get it.
    Please guide me.

    Many thanks

  3. Hello I am encountering issue when entering codes in weekends and holidays in leave tracker. The codes do not compile on the list.


Leave a Comment