IF function in Excel
Last updated
IF checks whether a condition is true and returns one value if it is, and another if it isn’t. For example, =IF(B2>=70,"Pass","Fail") shows Pass when the score in B2 is 70 or more, and Fail otherwise. Text results go in double quotes, numbers don’t.
IF syntax
=IF(logical_test, value_if_true, [value_if_false])
- logical_test
- The condition to check, usually a comparison such as
B2>=70orC2="Paid". Operators:=,<>,>,<,>=,<=. - value_if_true
- What to return when the test is TRUE: a number, a text in double quotes, a cell or another formula.
- value_if_false
- What to return when the test is FALSE. If you leave it out, Excel shows FALSE. Use
""to leave the cell looking empty.
Practice IF here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Pass or fail
The pass mark for this training quiz is 70. In C2, show Pass if the score in B2 is 70 or more, otherwise Fail.
| Name | Score | Result |
| Olivia Reed | 82 | |
| James Carter | 64 | |
| Sofia Patel | 91 | |
| Liam Brooks | 70 | |
| Emma Hughes | 55 |
How to write an IF formula, step by step
- Click the cell where you want the result and type
=IF(. - Write the test, for example
B2>=70, then type a comma. - Type the result for TRUE, such as
"Pass"in double quotes, then a comma. - Type the result for FALSE, such as
"Fail", close the bracket and press Enter.
IF examples
Pass or fail label
=IF(B2>=70,"Pass","Fail")
Returns Pass when B2 is 70 or more, otherwise Fail.
Discount amount
=IF(C2>=1000,C2*0.1,0)
Orders of 1,000 or more get 10% off: the formula returns the discount, or 0 for smaller orders.
Three bands with nested IF
=IF(B2>=10000,"Gold",IF(B2>=5000,"Silver","Bronze"))
The second IF sits in the value_if_false of the first. Test the highest band first, because Excel stops at the first TRUE test.
Two conditions with AND
=IF(AND(B2>=20000,C2>=4.5),"Bonus","No bonus")
AND returns TRUE only when both tests are true. Use OR instead when one of them is enough.
Common IF mistakes and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #NAME? | A text result or comparison without double quotes, e.g. =IF(B2>=70,Pass,Fail). Excel reads Pass as a name it doesn’t know. | Put every text in double quotes: "Pass". |
| FALSE in the cell | value_if_false is missing and the test is false. | Add the third argument, or "" to show nothing. |
| Wrong band, no error | In a nested IF, a lower band is tested first, so B2>=5000 catches the 12,000 rows too. | Order the tests from the highest band to the lowest (or the reverse with <). |
| Always the same result | A number typed in quotes, e.g. B2>="70". Excel compares a number with a text, so the test never works as expected. | Write numbers without quotes: B2>=70. |
Nested IF vs IFS
Nested IF works in every version of Excel and is fine for two or three bands. IFS (Excel 2019 and Microsoft 365) lists test and result pairs in one function, =IFS(B2>=10000,"Gold",B2>=5000,"Silver",TRUE,"Bronze"), which is easier to read when you have many cases. If your file may be opened in an older Excel, stick with nested IF.
Frequently asked questions
How do I use IF with text?
Put the text in double quotes, both in the test and in the results: =IF(C2="Paid","Closed","Chase"). The comparison isn’t case-sensitive.
How many IFs can I nest?
Excel accepts up to 64 nested IFs, but past three or four the formula gets hard to read. Use IFS or a lookup table for long lists of bands.
How do I check two conditions in IF?
Put them inside AND (all must be true) or OR (one is enough), and use that as the test: =IF(AND(B2>=20000,C2>=4.5),300,0).
How do I make IF leave the cell empty?
Use two double quotes as the result: =IF(B2="","",B2*1.2). The cell looks empty but still contains a formula.
Keep practising
All 10 IF exercises, with explanations and progress tracking.
Open the IF practice set