Home › Excel functions › IF
Excel IF Statement: How to Write an IF Formula
An IF statement in Excel checks whether a condition is true and returns one value if it is and another if it isn't. It's the foundation of decision-making in Excel: pass or fail, bonus or no bonus, late or on time.
Excel CSE · 4 min readIn this guide
IF syntax
=IF(logical_test, value_if_true, [value_if_false])| Argument | What it means |
|---|---|
| logical_test | A comparison that is either TRUE or FALSE, such as B2>=60. |
| value_if_true | What to return when the test is TRUE. |
| value_if_false (optional) | What to return when the test is FALSE. If you leave it out, Excel returns the word FALSE. |
Basic example: pass or fail
Students need a score of at least 60 to pass.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Anna | 72 | Pass |
| 3 | Ben | 55 | Fail |
| 4 | Chris | 90 | Pass |
| 5 | Dana | 60 | Pass |
Enter this in C2 and copy it down. Anna scored 72, which is 60 or more, so she gets Pass. Ben's 55 is lower, so he gets Fail. Dana's exactly 60 also passes, because >= means "greater than or equal to".
Text goes between double quotes. Numbers, cell references and TRUE/FALSE don't. =IF(B2>=60, Pass, Fail) without quotes returns #NAME?.
Comparison operators
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | =IF(A2="Paid", "✓", "") |
<> | Not equal to | =IF(A2<>"", "Filled", "Empty") |
> | Greater than | =IF(B2>100, "Large", "Small") |
< | Less than | =IF(C2<TODAY(), "Overdue", "") |
>= | Greater than or equal to | =IF(B2>=60, "Pass", "Fail") |
<= | Less than or equal to | =IF(B2<=0, "Out of stock", "In stock") |
Text comparisons are not case-sensitive: "paid" and "PAID" are treated as equal.
Return a calculation instead of text
The values to return can be formulas too. A 5% bonus on sales over 10,000, and nothing otherwise:
=IF(B2>10000, B2*5%, 0)To leave a cell visually empty instead of showing 0, return an empty string: =IF(B2>10000, B2*5%, "").
IF with AND and OR
To check several conditions at once, put AND or OR inside the logical test.
- AND: all conditions must be true.
=IF(AND(B2>=60, C2="Yes"), "Pass", "Fail")passes only students who scored 60+ and handed in their assignment. - OR: at least one condition must be true.
=IF(OR(B2="VIP", C2>1000), "Free shipping", "")gives free shipping to VIP customers or large orders.
Nested IFs: more than two outcomes
When there are more than two possible results, you can put an IF inside another IF. This assigns grades A to F:
=IF(B2>=80, "A", IF(B2>=70, "B", IF(B2>=60, "C", IF(B2>=50, "D", "F"))))Excel checks the conditions from left to right and stops at the first one that's true. Order matters: always start with the highest threshold. A score of 72 fails the first test, passes the second, and returns B.
Simpler: the IFS function
In Excel 2019, 2021, 2024 and Microsoft 365 you can use IFS, which lists condition/result pairs without the nesting:
=IFS(B2>=80, "A", B2>=70, "B", B2>=60, "C", B2>=50, "D", TRUE, "F")The final TRUE, "F" acts as "everything else". For long lists of thresholds, a lookup table with XLOOKUP or VLOOKUP is often easier to maintain than either.
Common IF mistakes
- #NAME?: text without quotes, or a misspelled function name.
- The cell shows FALSE: you left out
value_if_false. Add""to show nothing. - Wrong result with numbers: the numbers may be stored as text.
"100">50doesn't behave like100>50. Convert them with VALUE or Text to Columns. - Nested IF gives the wrong grade: the conditions are in the wrong order. Test the highest threshold first.
- Too many parentheses to keep track of: switch to IFS or a lookup table.
Frequently asked questions
How many IFs can you nest in Excel?
Up to 64 levels. In practice, more than three or four makes a formula hard to read; use IFS or a lookup table instead.
How do I make IF return a blank cell?
Use an empty string as the result: =IF(A2>10, "Yes", ""). The cell looks empty, although technically it contains text of zero length.
Can IF check if a cell contains certain text?
Yes, combine it with ISNUMBER and SEARCH: =IF(ISNUMBER(SEARCH("urgent", A2)), "Priority", "") returns Priority when A2 contains the word urgent anywhere.
What's the difference between IF and IFS?
IF tests one condition with two outcomes; you nest several IFs for more outcomes. IFS tests multiple conditions in one flat list and returns the result for the first one that's true.