IF — Making a Decision in a Cell
Nisha wants every order tagged "Large" or "Small" so she can see how much of her revenue comes from big orders. Doing that by eye for hundreds of rows is slow and error-prone. A single IF formula, copied down the column, makes the decision for every row in a second.
Throughout this section the orders sheet sits in columns A to F:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | order_id | order_date | customer | item_code | qty | amount |
| 2 | 1001 | 2025-06-02 | Asha Rao | C01 | 2 | 240 |
| 3 | 1002 | 2025-06-02 | Vikram Shah | C02 | 3 | 120 |
| 4 | 1003 | 2025-06-03 | Meera Iyer | B01 | 1 | 90 |
| 5 | 1004 | 2025-06-03 | Asha Rao | B02 | 2 | 220 |
| 6 | 1005 | 2025-06-04 | Dev Menon | C01 | 1 | 120 |
The rule
IF(test, then, else): one question, two possible answers.
=IF(F2>=200, "Large", "Small")- test: a comparison that is either TRUE or FALSE. Operators:
=,<>(not equal),>,<,>=,<=. - then: what the cell shows when the test is TRUE.
- else: what it shows when the test is FALSE.
Text results go in double quotes; numbers and cell references do not. So =IF(E2>=2, F2*0.9, F2) gives a 10% discount on orders of two or more items and leaves the rest alone.
For more than two outcomes, put another IF in the else slot. This is called nesting:
=IF(F2>=200, "Large", IF(F2>=100, "Medium", "Small"))To test two conditions at once, wrap them in AND(...) (both must be true) or OR(...) (either is enough): =IF(AND(D2="C01", E2>=2), "Coffee fan", "").
The shortcut
Read an IF aloud as a sentence: "If test, show then, otherwise show else." For nested IFs, check thresholds from the biggest down, so each value is caught by the first test it passes.
First test
Is 120 >= 200? No, so skip "Large" and move to the else slot.
The trap: forgetting quotes around text. =IF(F2>=200, Large, Small) makes Excel hunt for names called Large and Small and returns #NAME?.
Drills
Answer fast — trust the rule.
What does =IF(F4>=200, "Large", "Small") show for order 1003?
Using the nested formula with "Large", "Medium" and "Small", what does order 1004 (amount 220) show?
What does =IF(E3>=2, F3*0.9, F3) return for order 1002?
Which formula labels only cold-coffee orders (code C01) of 2 or more items as "Coffee fan"?
Remember
- IF(test, then, else): one question, two possible answers.
- Shortcut: read it aloud as a sentence; in nested IFs, test the biggest threshold first.
- Trap: text results without double quotes give
#NAME?.