How to Use IF/THEN in Excel (with Examples)

Key Takeaways

• The IF function in Excel tests a condition and returns one value if true and another if false, using the syntax =IF(logical_test, value_if_true, value_if_false).
• You can nest multiple IF functions inside each other to evaluate more than one condition in a single formula.
• Common IF formula errors include missing commas, unmatched quotation marks and incorrect cell references, all of which are easy to troubleshoot.
• For complex logic with three or more conditions, consider alternatives like the IFS function for cleaner, more readable formulas.

What Is the IF Function in Excel?

The IF function is one of the most widely used logical functions in Excel. It evaluates a condition you specify and returns one value when the condition is true and a different value when it is false. Although many people search for "if then Excel," the function in Excel is simply called IF. The IF/THEN logic is built into the formula's structure.

The generic syntax for an IF formula in Excel looks like this:

=IF(logical_test, value_if_true, value_if_false)

Here is what each argument means:

• logical_test: the condition or comparison you want Excel to check. This can use operators like >, <, =, >= or <>.
• value_if_true: the value Excel returns when the logical test evaluates to TRUE. This can be a number, text in quotation marks or another formula.
• value_if_false: the value Excel returns when the logical test evaluates to FALSE. Like value_if_true, this can be a number, text or formula.

The IF function works in all modern versions of Excel as well as Google Sheets, so the skills you learn here apply across platforms.

A Real-World IF Function Example

Sometimes you need your spreadsheet data to tell you more about itself. The versatile IF function is how you can turn raw numbers into useful information.

For example: Your wholesale company charges different rates depending on the size of bulk orders – customers who order more receive volume discounts. You want a way 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. This kind of pricing scenario often pairs well with Excel finance formulas for more advanced financial modeling.

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

The sections below walk through how to build this formula step by step.

How to Write a Basic IF Formula in Excel

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

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

How to Use the Insert Function Dialog for IF

If you are still confused about how to use an IF function, Excel offers some help:

1) Click the Formulas tab, then click Insert Function

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 setup your IF function. Click OK. Excel will populate the cell with the function in the correct format.

How to Use Nested IF Functions for Multiple Conditions

Once you are comfortable with the basic IF function, you can build more complex formulas using nested IFs. For example, nested IF functions can help you calculate tiered discounts with multiple rates for different purchasing levels.

Problem:

IF(the number of units is over 1000, check to see IF(the number is over 5000, give the customer a 6% discount, otherwise give the customer a 3% discount), if the number of units is not over 1000 the customer receives no discount).

=IF(D2>1000, IF(D2>5000, 0.06, 0.03), 0)

• D2 is the cell that shows the number of units sold.
• If D2 is greater than 1000, the formula checks to see if D2 is also greater than 5000
• 0.06 is the discount (6%) received if D2 is greater than 5000, otherwise, 0.03 is the discount (3%) received for D2 that exceeds 1000
• 0 is the discount received if D2 is not greater than 1000

Common IF Function Errors and How to Fix Them

Even experienced Excel users run into errors when writing IF formulas. Here are the most common problems and how to resolve them:

• Missing or extra commas: the IF function requires exactly two commas to separate its three arguments. A missing comma will trigger a syntax error, and an extra comma can cause unexpected results. Double-check that your formula follows the pattern =IF(test, true_result, false_result).
• Forgetting quotation marks around text values: if you want your IF function to return a word or phrase, that text must be wrapped in double quotation marks. For example, =IF(A1>10, "Yes", "No") is correct. Leaving out the quotes causes a #NAME? error.
• #VALUE! error from mismatched data types: this error typically appears when your logical test compares incompatible data types, such as a cell containing text against a number. Make sure the cells you reference contain the type of data your formula expects.
• Formula returns 0 or FALSE unexpectedly: this usually means the logical test is not evaluating the way you intended. Review your comparison operators (>, <, =, >=, <=, <>) and confirm you are referencing the correct cells.
• Too many nested IFs causing confusion: while Excel supports up to 64 levels of nesting, formulas with more than two or three nested IFs become difficult to read and troubleshoot. If you find yourself stacking multiple IFs, consider switching to the IFS function for a cleaner approach.

Beyond Nested IFs: Alternative Functions to Know

As your spreadsheet logic grows more complex, several functions can simplify your formulas and make them easier to maintain:

• IFS: the IFS function evaluates multiple conditions in sequence and returns the result for the first condition that is TRUE. Instead of nesting several IF functions, you can write =IFS(A1>5000, 0.06, A1>1000, 0.03, TRUE, 0). The IFS function is available in Excel for Microsoft 365 and modern versions of Excel.
• AND: use AND inside an IF function when you need every condition to be true. For example, =IF(AND(A1>100, B1<500), "Approved", "Denied") returns "Approved" only when both conditions are met.
• OR: use OR inside an IF function when you need at least one condition to be true. For example, =IF(OR(A1="East", A1="West"), "Domestic", "International") returns "Domestic" if the region matches either value.

Choosing the right function for the job keeps your formulas readable and reduces the chance of errors as your workbooks grow.

Start Building Smarter Spreadsheets

The IF function gives you a powerful tool for adding true/false logic to your data that will help speed decision-making (such as discounts!) and save you time on repetitive tasks.

Mastering IF/THEN logic in Excel opens the door to more advanced skills like dynamic reporting, conditional formatting and automated data validation. If you want to go deeper and build real confidence with formulas, check out Pryor Learning's Excel training courses for hands-on instruction you can apply right away.