How to Use the IF-THEN Function in Excel

The short version is =IF(condition, true value, false value)

What to Know

  • The syntax of IF-THEN is =IF(logic test,value if true,value if false).
  • The "true" value tells the function what to do if the result of the logic test is true.
  • The "false" value tells the function what to do if the result of the logic test is false.

This article explains how to write and use the IF function (also known as IF-THEN) in Excel. Instructions apply to Microsoft 365, Excel 2021, 2019, 2016, 2013, 2010; Excel for Mac, and Excel Online.

How to Write IF-THEN in Excel

The IF function in Excel is a way to add decision-making to your spreadsheets. It tests a condition to see if it's true or false and then carries out a specific set of instructions based on the results.

For example, you could set an IF to turn back results if a specific cell is greater than 900. If it is, you can make the formula return the text "PERFECT." If it isn't, you can make the formula return "TOO SMALL."

The IF-THEN function's syntax includes the name of the function and the function arguments inside the parenthesis.

This is the proper syntax of the IF-THEN function:

=IF(logic test,value if true,value if false)

The IF part of the function is the logic test. This is where you use comparison operators to compare two values. The THEN part of the function comes after the first comma and includes two sets of instructions with a comma between them.

  • The first item tells the function what to do if the comparison is true.
  • The second item tells the function what to do if the comparison is false.

A Simple IF-THEN Function Example

Before moving on to more complex calculations, let's look at a straightforward example of an IF-THEN statement. 

Our spreadsheet is set up with cell A1 as $100. We can input the following formula into B1 to indicate whether the value is larger than $1000. 

=IF(A1>1000,"PERFECT","TOO SMALL")

This function has the following parts:

  • A1>1000 tells Excel to check whether the value in cell A1 is larger than 1000.
  • "PERFECT" returns the word PERFECT in cell B1 if A1 is larger than 1000.
  • "TOO SMALL" returns the phrase TOO SMALL in cell B1 if A1 is not larger than 1000.
A simple example of an IF THEN formula in Excel

In plain language, this IF function says, "If the value in A1 is greater than 1,000, write PERFECT. Otherwise, write TOO SMALL."

The comparison part of the function can compare only two values. Either of those two values can be:

  • Fixed number
  • A string of characters (text value)
  • Date or time
  • Functions that return any of the values above
  • A reference to any other cell in the spreadsheet containing any of the above values

The "true" or "false" part of the function can also return any of the above. You can make the IF function very advanced by embedding additional calculations or functions inside it.

When inputting true or false conditions of an IF-THEN statement in Excel, you need to use quotation marks around any text you want to return, unless you're using TRUE and FALSE, which Excel automatically recognizes. Other values and formulas don't require quotation marks.

Inputting Calculations Into the IF-THEN Function

You can embed different calculations for the IF function to perform, depending on the comparison results. This example uses one calculation for the tax rate, depending on the total income in B2. The logic test compares total income in B2 to see if it's greater than $50,000.00.

=IF(B2>50000,B2*0.15,B2*0.10)

If the value in B2 is greater than 50,000, the IF function will multiply it by 0.15. If it's lower, the function will multiply it by 0.10.

You can also embed calculations into the comparison side of the function. In the above example, you might estimate that taxable income will only be 80% of total income. With this in mind, you can change the above IF function to the following:

=IF(B2*0.8>50000,B2*0.15,B2*0.10)

This formula first multiplies the input value (in this case, B2) by 0.8, and then it compares that result to 50,000. The rest of the function works the same.

An example of embedding calculations in an IF-THEN function in Excel.

Because Excel treats commas as breaks between parts of a formula, don't use them when entering numbers higher than 999. For example, type 1000, not 1,000.

Nesting Functions Inside of an IF Function

You can also embed (or "nest") a function inside an IF statement in Excel. This action lets you perform advanced calculations and then compare the actual results to the expected results.

In this example, let's say you have a spreadsheet with five students' grades in column B. You could average those grades using the AVERAGE function. Depending on the class average results, you could have cell C2 return either "Excellent!" or "Needs Work."

This is how you would input that IF-THEN in Excel:

=IF(AVERAGE(B2:B6)>85,"Excellent!","Needs Work")

In English: "If the average of the values from B2 to B6 is greater than 85, type Excellent! Otherwise, type Needs Work."

As you can see, inputting the IF-THEN function in Excel with embedded calculations or functions allows you to create dynamic and highly functional spreadsheets.

Embedding other functions inside an IF-THEN function in Excel.
FAQ
  • How do I create multiple IF-THEN statements in Excel?

    Use Nesting in Excel to create multiple IF-THEN statements. Alternatively, use the IFS function.

  • How many IF statements can you nest in Excel?

    You can nest up to 7 IF statements within a single IF-THEN statement.

  • How does conditional formatting work in Excel?

    With conditional formatting in Excel, you can apply more than one rule to the same data to test for different conditions. Excel first determines if the various rules conflict, and, if so, the program determines which conditional formatting rule to apply to the data.

Was this page helpful?