How to use IF statements in Excel

How to use IF statements in Excel

If you need Excel to choose between two or more outcomes based on cell values, start with the IF function: =IF(condition, value_if_true, value_if_false). For multiple conditions use nested IFs, the IFS function in modern Excel, or logical operators like AND and OR to combine tests. Below are clear, practical steps to write, test, and troubleshoot IF statements in real spreadsheets.

Quick overview: what IF does and when to use it

The IF function evaluates a logical test and returns one value if the test is true and another if it is false. Use IF when a result depends on a single condition (for example, mark pass/fail from a score) or when you can express decision logic in a small number of branches. For many sequential conditions, consider alternatives to avoid complex nested formulas.

Basic IF syntax and first example

Syntax

Write an IF formula like this: =IF(logical_test, value_if_true, value_if_false). The logical_test can be a comparison (A1>10), a reference to another cell, or a function that returns TRUE or FALSE.

Worked example: pass/fail

Suppose cell B2 contains a numeric score. To return Pass when the score is 60 or above and Fail otherwise, place in C2:

=IF(B2>=60,"Pass","Fail")

Enter sample scores to test the formula: 59 should return Fail, 60 should return Pass, and a blank cell commonly evaluates to zero so will return Fail unless you handle blanks explicitly.

Step-by-step: build an IF formula and test it

  1. Identify the condition you want to check (for example, sales > quota, score >= threshold, or a status text).
  2. Decide the true and false outcomes. These can be text, numbers, formulas, or cell references.
  3. Write the formula using =IF(condition, value_if_true, value_if_false).
  4. Use absolute references ($A
    ) where you need a constant across rows, and relative references (A1) where you will copy the formula.
  5. Test with standard cases and edge cases: exactly the threshold, just below, empty cells, and non-numeric entries if relevant.
  6. Use Evaluate Formula (Formulas tab) or show formulas (Ctrl-`) to step through logic when results look wrong.

Multiple conditions: nested IF, IFS, AND, OR

When you need more than two outcomes, there are three common approaches: nested IF, the IFS function (available in newer Excel), or combining logical tests with AND and OR inside an IF.

Nested IF example

Grading example with nested IFs: A score in B2 is mapped to letter grades.

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))

Nested IF works but becomes hard to read as complexity grows. For a comparison of the two approaches see IF vs IFS.

IFS function (clearer for sequential tests)

IFS evaluates conditions in order without deeply nested parentheses. The same grading logic with IFS is:

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")

Note that IFS requires Excel versions that include it; if you do not have IFS, nested IF remains the compatible option.

Combine AND/OR with IF

Use AND to require multiple conditions and OR to allow several alternatives. For example, to check whether a date in A2 is within a range and a status cell C2 equals "Approved":

=IF(AND(A2>=start_date,A2<=end_date,C2="Approved"),"Eligible","Not eligible")

For additional examples of logical combinations, see IF with AND/OR.

Worked example: tiered commission with tests and a checklist

Problem: calculate commission rate based on quarterly sales in B2: 0-9999 -> 2%, 10000-19999 -> 4%, 20000+ -> 6%.

  1. Decide ranges and inclusive boundaries.
  2. Choose formula style. Using IFS: =IFS(B2>=20000,0.06,B2>=10000,0.04,B2>=0,0.02)
  3. Multiply by sales to get commission: =B2*IFS(...)
  4. Test cases: B2=0, B2=9999, B2=10000, B2=20000, B2 blank, and B2 nonnumeric.

Checklist before finalizing the sheet:

Troubleshooting common IF problems

When to use alternatives to nested IF

Nesting is appropriate for a small number of branches. Use other tools when logic becomes long or when conditions are nonsequential:

  1. IFS for sequential checks in modern Excel — clearer syntax and fewer parentheses.
  2. LOOKUP or VLOOKUP/HLOOKUP/XLOOKUP when decisions are based on numeric ranges or table-based mapping.
  3. SWITCH or CHOOSE for a fixed set of exact matches. For help deciding, consult a short comparison in the guide on Nested IF alternatives.

Testing strategies and best practices

Always build and test incrementally. Start with the simplest condition, verify outputs, then add the next branch. Keep formulas readable using line breaks in the formula bar (Alt+Enter) and comment cells with notes describing the logic.

Document assumptions: what constitutes an empty input, whether thresholds are inclusive, and how to treat invalid data. These notes prevent confusion when others use the worksheet.

Closing: practical checklist to finish your IF logic

Using IF statements in Excel is straightforward for single decisions and still manageable for a few branches. For many conditions or table-driven logic, prefer IFS or lookup functions to improve clarity and reduce maintenance effort.