What are if-then statements in Excel and how to use them

IF is one of the oldest and most popular functions in Excel. The principle behind this function is simple: If a value is true, then an action will be carried out. If the value is false, a different action will be carried out. Here, we explain how the if-then function in Excel works and when to use it.

What are if-then statements in Excel?¶

With the IF function (to give it its proper name), you can make a logical comparison. Is a value identical to what you expect or not? If it is, an action of your choice will be carried out. If it isn’t, a different action will occur. Simply put: if A, then B, otherwise C.

This makes the if-then function in Excel one of the program’s most important tools as the formula can be used in all sorts of situations. Whether you are working on a simple membership directory, a table or corporate key figures, the IF formula is highly effective—both alone and in combination with other functions.

How do if-then statements work in Excel?¶

Like every function and formula in Excel, IF follows a specific syntax:

=IF(condition, value_if_true, value_if_false)

As shown above, the function has three parameters, the first two of which have to be specified.

• Condition: This position must contain a condition—a comparison between two values—where one or both values can be cell references. The following operators can be used to create conditions:

  • Equal to (=)
  • Not equal to (<>)
  • Less than (<)
  • Greater than (>)
  • Less than or equal to (<=)
  • Greater than or equal to (>=)

• Then_value (value_if_true): In this parameter, enter what should happen if the condition is true. Values, character strings, cell references and other functions can all serve as outcomes and should be entered in quotation marks.

• Else_value (value_if_false): This final parameter is optional. If you don’t specify anything here, the function will simply return FALSE. However, if you specify this parameter, the else_value will behave similar to the then_value, carrying out an action for values that are false.

Here’s one example of how an Excel if-then statement can look:

=IF(A1>=100,"target achieved","target not achieved")

You don’t have to enter the IF function into a cell or the formula bar manually. If you like, you can alternatively use the Insert Function feature under Formulas. This feature can help you to fill out formulas correctly.

Examples of how to use the if-then function in Excel¶

The IF function can be used for various situations, both for simple and complex calculations. Using five examples, we’ll look at the different ways you can use if-then statements in Excel.

Sorting data points¶

You can use the Excel IF function to split a series of data points into two groups based on whether or not they meet a criterion that you have specified. For example, you can use this function to see whether someone has passed an exam. In the following example, a passing grade is a score of 50% or higher.

=IF(B2>=50%,"Passed","Failed")

Checking text entries¶

In addition to checking numerical values, you can also use if-then statements in Excel to evaluate text. In the following example, we’ll use the IF function to see which books in the list were written by Stephen King:

=IF(F2 = "Stephen King"; "Yes"; "No")

Nesting an additional if-then statement in the IF function¶

Sometimes it may be necessary to check another condition after the first condition has been checked. In such cases, instead of using a then_value or else_value, you can use the IF function again. In the following example, we’ll look at whether packages have already been shipped.

=IF(J2="Arrived";"Yes";IF(J2="In transit"; "Yes";"No"))

Combining an if-then statement with other functions¶

You can also nest other functions inside of the IF function. Functions that are specified as conditions within the IF function have to return ‘True’ or ‘False’. In the following example, we’ll show how to recreate the last example using the OR function in Excel.

=IF(OR(N2="Arrived";N2="In Transit");"Yes";"No")

Complex formulas with the if-then function in Excel¶

Now, we are going to look at a more complex formula. In this example, we’re tracking the price of a stock and want to indicate whether there is a loss or gain in the value of the stock. Additionally, we also want to display the change in value. To do this, we are going to use the CONCATENATE function in Excel.

=IF(R3<R2;CONCATENATE("Loss: "; R2-R3);CONCATENATE("Gain: "; R3-R2))

What is the IFS function?¶

If you are using Microsoft 365, Office 2021, Office 2019 or Office Online, there is another Excel function you can use. The IFS function works similarly to IF in that it also allows you to assign a then_value to a condition. The difference, however, is that this can be done up to 127 times. The syntax is as follows:

=IFS(Something is True1; Value if True1; [Something is True2; Value if True2];…[Something is True127; Value if True127])

For example, if a store has products that haven’t been purchased for a while, it may be a good idea to offer a discount on them. The store manager could reduce the price of already discounted products even further if there are only a few of them left in stock. Here, we have two conditions: First, the last sale needs to have occurred over 30 days ago, and for an additional discount to be applied, there should be no more than 10 items left in stock. This means we need to use the AND function in the first parameter. We also need to define our criteria further in order to make three results possible: 50% discount, 25% discount and no discount.

=IFS(AND(DAYS(TODAY();Y2)>30;X2<10);V2*0.5;DAYS(TODAY();Y2)>30;V2*0.75;DAYS(TODAY();Y2)<=30;V2)

What stands out in this formula is that there’s no longer anelse_value**. This also means, however, that you need to define what should happen if the conditions aren’t fulfilled. In our example, this would be cases where no discount is applied.

The two checks for the discounts happen one after another, followed by cases where no discount should be applied (i.e., all products that were sold less than or equal to 30 days ago).