How to Use the Excel IF Then Formula

Key Takeaways

• The IF function in Excel tests a condition and returns one value if true and another if false, following the syntax =IF(logical_test, value_if_true, value_if_false).
• You can combine the IF function with AND or OR to evaluate multiple conditions in a single formula.
• Nested IF statements let you handle three or more possible outcomes, though the newer IFS function offers a simpler alternative.
• Thinking through your problem in plain language before writing the formula helps you translate business logic into Excel syntax.

What Is the IF Then Formula in Excel?

The excel if then formula is the common name for Excel's IF function, one of the most widely used logical functions in any spreadsheet. The IF function evaluates a condition you specify and returns one result when that condition is true and a different result when it is false. In other words, it lets you build decision-making logic directly into your cells.

The if function in Excel follows a straightforward structure. You give it a question to evaluate, tell it what to do if the answer is yes and tell it what to do if the answer is no. A simple example looks like this:

=IF(A1>50, "Pass", "Fail")

In this if then statement in Excel, the function checks whether the value in cell A1 is greater than 50. If it is, the cell displays "Pass." If it is not, the cell displays "Fail."

IF Function Syntax

The generic syntax for the if formula in Excel is:

=IF(logical_test, value_if_true, value_if_false)

Each argument serves a specific role:

• logical_test: the condition or comparison you want Excel to evaluate. You can use comparison operators such as = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to) and <> (not equal to).

• value_if_true: the value or action Excel returns when the logical test is true. This can be a number, text string, cell reference or another formula.

• value_if_false: the value or action Excel returns when the logical test is false. This argument is optional. If you omit it, Excel returns FALSE when the condition is not met.

A Real-World IF Formula Example

Sometimes you need your data to tell you more. The IF formula as a function gives your data a voice and turns raw numbers into useful information, while conditional formatting takes a visual approach by changing how cells look based on similar conditions. For example: Your wholesale company charges different rates depending on the size of bulk orders – customers who order more receive volume discounts. You want to quickly calculate the volume discount on a batch of orders so your sales team can deliver the correct quotes to their customers. In a nutshell, you need to calculate your cost and volume numbers into a price for your customers based on an additional bit of logic.

Here is how your data might look in a simple spreadsheet:

The following steps walk through building this formula from start to finish.

How to Write a Basic IF Formula Step by Step

Step 1: Define Your Problem in Natural Language

To set up an IF function, try thinking about your problem in natural language:

Problem: IF (the number of units is over 1000, the customer receives a volume discount of 3%, otherwise the customer receives no discount).

Step 2: Translate Your Logic into the IF Formula

Now, just fill in the cells and values for the statement:

=IF(D2>1000, 0.03,0)

• D2 is the cell that shows the number of units sold

• 0.03 is the discount (3%) received if D2 is greater than 1000

• 0 is the discount received if D2 is not greater than 1000

Step 3: Use the Insert Function Dialog Box (Optional Method)

If you are still confused about how to use an IF function, Excel offers a guided interface through the Insert Function dialog box:

1. Click on the Formulas tab, then click the Insert Function button.

2. Select IF from the Insert Function dialog box. Click OK.

3. Fill in the function arguments in the next dialog box. The dialog box includes prompts to help you set up your IF function. Click OK. Excel will populate the cell with the function in the correct format.

Using IF with Multiple Conditions

A single IF function handles one condition, but many real-world scenarios require you to test two or more conditions at the same time. You can do this by combining the IF function with AND or OR.

AND: all conditions must be true for the value_if_true result to be returned. Use AND when every requirement must be met.

=IF(AND(condition1, condition2), value_if_true, value_if_false)

OR: any one of the conditions can be true for the value_if_true result to be returned. Use OR when meeting at least one requirement is enough.

=IF(OR(condition1, condition2), value_if_true, value_if_false)

Building on the wholesale discount scenario, suppose your company also wants to reward preferred customers with a higher discount. You need to check two things: the order is over 1,000 units and the customer status is "Preferred." The formula would look like this:

=IF(AND(D2>1000, C2="Preferred"), 0.05, 0.03)

This formula returns a 5% discount when both conditions are true and a 3% discount when they are not. You could also use OR if you wanted to offer the higher discount when either condition is met:

=IF(OR(D2>1000, C2="Preferred"), 0.05, 0.03)

The key difference between the two:

• AND: returns the value_if_true result only when every condition evaluates to true
• OR: returns the value_if_true result when at least one condition evaluates to true

How to Use Nested IF Statements

When you need to handle three or more possible outcomes, you can nest one IF function inside another. Nesting means placing a second IF function in the value_if_false position of the first IF function so that Excel evaluates additional conditions when the first one is not met.

The generic syntax for a nested IF looks like this:

=IF(condition1, result1, IF(condition2, result2, result3))

Returning to the volume discount example, imagine your company now offers three discount tiers based on order size:

• Orders over 2,000 units receive a 5% discount
• Orders over 1,000 units receive a 3% discount
• All other orders receive no discount

The nested IF formula would be:

=IF(D2>2000, 0.05, IF(D2>1000, 0.03, 0))

Excel evaluates the first condition (D2>2000). If true, it returns 0.05. If false, it moves to the nested IF and checks whether D2>1000. If that is true, it returns 0.03. If neither condition is met, it returns 0.

You can nest up to 64 IF functions in a single formula, but formulas with more than three or four levels of nesting become difficult to read and troubleshoot. When you find yourself building deeply nested formulas, consider using the IFS function instead.

The IFS Function: A Simpler Alternative

The IFS function is a modern alternative to nested IF statements. It is available in current versions of Excel and Microsoft 365 and allows you to evaluate multiple conditions in sequence without nesting.

The syntax for IFS is:

=IFS(condition1, value1, condition2, value2, ..., TRUE, default_value)

Each pair of arguments represents a condition and the value to return if that condition is true. Excel evaluates the conditions from left to right and returns the value for the first condition that is met. The final pair (TRUE, default_value) acts as a catch-all for cases where none of the previous conditions are true.

Using the three-tier discount example, the IFS version of the formula would be:

=IFS(D2>2000, 0.05, D2>1000, 0.03, TRUE, 0)

The IFS function is easier to read and maintain than deeply nested IF statements. Keep in mind that IFS is not available in older versions of Excel, so if you share workbooks with users on legacy software, nested IF statements remain the safer choice.

Common IF Formula Errors and How to Fix Them

Even experienced users run into issues with IF formulas. Here are some of the most common errors and how to resolve them:

• Mismatched parentheses: every opening parenthesis needs a closing one. If your formula returns an error or Excel prompts you to accept a correction, count your parentheses to make sure they match.
• Missing quotation marks around text values: text strings in an IF formula must be enclosed in double quotation marks. Writing =IF(A1>50, Pass, Fail) produces an error, while =IF(A1>50, "Pass", "Fail") works correctly.
• Wrong comparison operator: using = when you mean > or >= is a common oversight. Double-check that your logical test reflects the exact comparison you intend.
• Unexpected results from blank cells: Excel treats blank cells as zero in numeric comparisons. If your data contains blanks, add a check such as =IF(A1="", "No Data", IF(A1>50, "Pass", "Fail")) to handle them.
• #VALUE! errors from mismatched data types: this error often appears when your formula compares text to a number or references a cell that contains an error. Verify that the cells in your logical test contain the data type you expect.

Strengthen Your Excel Skills with Pryor Learning

The IF function is just one of many powerful tools in Excel, and mastering it opens the door to more advanced formulas and data analysis techniques. Pryor Learning offers live virtual and in-person Excel seminars as well as on-demand courses through PryorPlus that cover everything from foundational functions to advanced reporting.

Explore Pryor's Excel Training Options