How to Use the IF Function in Excel (With Easy Examples)
IF is the function that lets a spreadsheet make decisions. Has this invoice been paid? Did the student pass? Did the salesperson hit their target? IF checks something and shows one answer if it's true and another if it's not.
How IF works
=IF(test, value if true, value if false)
In plain English: if this is true, then show this, otherwise show that.
Example 1: Pass or fail
Scores are in column B. The pass mark is 50.
=IF(B2>=50, "Pass", "Fail")
Type it in C2, press Enter, then double-click the fill handle (the small square at the bottom-right of the cell) to copy it down.
Example 2: Is it overdue?
Invoice due dates are in column D.
=IF(D2<TODAY(), "Overdue", "")
The "" at the end means "show nothing" when it isn't overdue.
Example 3: Calculate a value
IF can return numbers or calculations too. A 10% bonus if sales in C2 are over £10,000:
=IF(C2>10000, C2*10%, 0)
Comparison symbols
| Symbol | Meaning |
|---|---|
| = | Equal to |
| Not equal to | |
| > | Greater than |
| Less than | |
| >= | Greater than or equal to |
| Less than or equal to |
Text needs quotation marks
Any text in a formula goes in double quotes: "Pass", "Paid". Numbers don't: 50, 10000.
Checking a cell's text works the same way:
=IF(E2="Paid", "✓", "Chase")
Text comparisons aren't case-sensitive, so "paid" and "PAID" both match.
Check two things at once: AND and OR
AND — every condition must be true:
=IF(AND(B2>=50, C2="Yes"), "Qualified", "Not yet")
OR — at least one condition must be true:
=IF(OR(D2="Urgent", E2>1000), "Priority", "Normal")
More than two answers: IFS
For grades or bands, use IFS (Microsoft 365 and Excel 2019 or later). It checks each condition in order and stops at the first true one:
=IFS(B2>=70, "A", B2>=60, "B", B2>=50, "C", TRUE, "Fail")
The final TRUE acts as "everything else".
In older versions, you can "nest" IFs inside each other:
=IF(B2>=70, "A", IF(B2>=60, "B", IF(B2>=50, "C", "Fail")))
It works — but IFS is much easier to read.
Troubleshooting
#NAME? error. Usually text without quotation marks, like =IF(E2=Paid, ...). Add the quotes.
Every row shows the same answer. Check your cell references. If you used $B$2 it won't change as you copy it down — use B2.
A number test isn't working. The numbers might be stored as text. Look for green triangles in the cells and convert them.
Wrapping up
Remember the pattern — test, if true, if false — and put text in quotes. Once that clicks, add AND, OR and IFS to handle more complicated decisions.
