The IF function in Excel
IF shows one result when a test is true and another when it is false.
What IF does
The test is a comparison such as B2>=60. Comparisons use equals, not equal (<>), greater than, less than, and greater or less than or equal to. Text results go in quotes. Copy the formula down a column and the cell reference moves to each row in turn.
Syntax
=IF(logical_test, value_if_true, [value_if_false])
- logical_test
- A comparison that is either true or false, such as B2>=60.
- value_if_true
- What to show when the test is true.
- value_if_false
- Optional. What to show when the test is false. If left out, Excel shows FALSE.
Worked example
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Ana | 72 | Pass |
| 3 | Ben | 48 | Fail |
| 4 | Cy | 64 | Pass |
| 5 | Di | 91 | Pass |
| 6 | Eli | 55 | Fail |
C2 =IF(B2>=60,"Pass","Fail") (copied down 5 rows)
The formula is written once in C2 and copied down. B2 becomes B3, B4 and so on.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Finance
Flag invoices that are over budget.
HR
Mark who has met a target.
Sales & Ops
Label orders as Late or On time.
Common mistakes
- Forgetting the quotes around text results. Pass must be "Pass".
- The wrong comparison. >= includes the boundary value and > does not.
- Typing the cell reference as a fixed address in every row instead of copying the formula down.
The IF course
- 1Flag each rowFree practice belowPractise
- 2Nested IFs for gradesComing soon
- 3IF with AND and ORComing soon
- 4Handle blanks safelyComing soon
- 5Commission tiersComing soon
- 6Replace errors with IFERRORComing soon
Common questions
Can IF return a number or a calculation?
Yes. The true and false results can be text, numbers, cell references or other formulas.
What happens if I leave out the false result?
Excel returns FALSE when the test fails.