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.

Tip

With HiDrive cloud storage from IONOS, you can safely save, edit and share Office documents in one central location. Whether at home or at work, the HiDrive App and other APIs make it possible for you to work flexibly from any device. With HiDrive cloud storage, your data is securely stored in state-of-the-art data centers.

HiDrive Cloud Storage
Store and share your data on the go
  • Store, share, and edit data easily
  • Backed up and highly secure
  • Sync with all devices
Note

The instructions below work with the Microsoft 365 version of Excel as well as Excel versions 2021, 2019 and 2016.

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")

Tip

In Excel, functions in the formula bar always start with an equals sign, with the parameters of a function enclosed in parentheses. You can also make absolute cell references by using a dollar sign. This means that functions will always refer to the specified cell, even when copied to other cells.

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")
You can use the IF function to create a condition, which can be used to divide values into two groups.

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")
With the IF function, you can also check text entries.

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"))
You can use an if-then statement inside of the IF function to distinguish between different values.
Note

While the nesting of the IF function is practical, it has its limits. Nesting creates a complexity that makes it difficult to create error-free syntax. If need be, it’s better to work with other functions like IFS or VLOOKUP in Excel.

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")
You can also nest other functions inside the IF function.

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))
With the IF function, you can also carry out more complex calculations that contain other functions and cell references.

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

With the IFS function, you have more possibilities for making logical comparisons.
Was this article helpful?
We use cookies on our website to provide you with the best possible user experience. By continuing to use our website or services, you agree to their use. More Information.
Page top