IF Formula Generator
Build Excel IF statements - and nested IF logic - by filling in the condition and the results for true and false.
IF
Return one value when a condition is true and another when it is false.
A comparison that is TRUE or FALSE, for example A2>=60.
Result when the condition is true. Text needs quotes.
Result when the condition is false. Text needs quotes.
=IF(A2>=60, "Pass", "Fail")Checks Before You Paste
- •Wrap text results in quotes, for example "Pass".
- •Nest another IF in the false slot to test more cases, or use IFS.
- •Use >=, <=, or <> inside the condition to compare values.
How IF works
Availability: Every version of Excel, Excel for Mac, Excel for the web, Google Sheets and LibreOffice Calc. The nesting limit is 64 levels from Excel 2007 onwards; Excel 2003 allowed 7.
IF evaluates a condition and returns one of two results. The condition is anything that reduces to TRUE or FALSE - a comparison such as A2>=60, a text test such as B2="Paid", or a function that returns a boolean such as ISBLANK or ISNUMBER. Excel also accepts a bare number in that slot, where 0 counts as FALSE and every other number as TRUE.
Text comparisons inside IF ignore case, so B2="paid" is TRUE when the cell reads "Paid" or "PAID". If case matters, EXACT is the only comparison that respects it: IF(EXACT(B2,"PAID"), ...). Comparisons are also loose about empty cells - a blank cell equals both "" and 0, so IF(A2=0,"zero","other") reports zero for a cell nobody has filled in yet. Add A2<>"" as a separate test when a blank must not count as a value.
Both result arguments are optional in the sense that Excel will accept them missing, and both omissions are traps. =IF(A2>10,"High") returns the boolean FALSE, not a blank, when the test fails. =IF(A2>10,"High",) - with the comma but nothing after it - returns 0. If you want a genuinely empty-looking cell, write "" explicitly. Be aware that "" is a zero-length string, not an empty cell: ISBLANK on it is FALSE, COUNTA counts it, and a chart plots it as a gap rather than skipping the point.
IF returns whatever you put in its result slots, including other formulas, so nesting is how multi-way decisions get built: put the next IF in the false slot and each test only runs on the rows the earlier tests rejected. Order matters, because the first TRUE wins - grade boundaries tested from lowest upwards will label everybody with the lowest grade. Past three or four levels a nested chain becomes hard to audit, and IFS (Excel 2019 and later), SWITCH, or a small lookup table with VLOOKUP's approximate match are all easier to change later.
Syntax
=IF(logical_test, [value_if_true], [value_if_false])| Argument | Required | What it does |
|---|---|---|
logical_test | Required | Anything that evaluates to TRUE or FALSE. Comparison operators are =, <>, >, <, >= and <=. A number here is read as TRUE unless it is 0. |
value_if_true | Optional | What to return when the test passes. Text must be quoted; a formula here is only evaluated when this branch is taken. Omitting it returns 0. |
value_if_false | Optional | What to return otherwise. Omit the argument entirely and a failed test returns the word FALSE; leave the comma with nothing after it and it returns 0. |
More worked examples
An invoice tracker with due dates in C2:C400. You want an Overdue flag for anything past due that has not been paid, where paid invoices carry a date in D.
=IF(AND(C2<TODAY(), D2=""), "Overdue", "")Returns: The word Overdue on unpaid rows past their due date, and an empty-looking cell everywhere else.
The "" result is a zero-length string, so COUNTA counts these cells. Count the flags with COUNTIF(E2:E400,"Overdue") rather than COUNTA.
Stock levels in B2:B200 against reorder points in C2:C200, and you want three states rather than two.
=IF(B2=0, "Out of stock", IF(B2<C2, "Reorder", "OK"))Returns: Out of stock, Reorder or OK depending on which test is met first.
The order is deliberate: zero stock is also below the reorder point, so testing for zero first is what stops every empty bin being labelled Reorder.
A commission column that should be blank rather than zero when there are no sales, with sales in D2 and a 4% rate.
=IF(D2>0, D2*0.04, "")Returns: The commission figure on rows with sales, and a blank-looking cell on the rest.
Downstream SUM ignores the text, but AVERAGE and COUNT do too - so an average taken over this column counts only the rows that earned commission.
Common mistakes
- Chained comparisons that do not mean what they read
- =IF(10<A2<20, ...) is valid syntax and always wrong. Excel evaluates 10<A2 first, gets TRUE or FALSE, and then compares that against 20 - TRUE counts as 1, so the whole thing is almost always FALSE. Write IF(AND(A2>10, A2<20), ...).
- Blank cells passing numeric tests
- An empty cell reads as 0, so =IF(A2<100,"Under","Over") labels every unfilled row Under. Guard it: =IF(A2="","",IF(A2<100,"Under","Over")).
- Quoting a cell reference
- =IF(A2="B2", ...) compares A2 against the two-character text B2, not against the contents of cell B2. Quotes belong around literal text only; references and numbers go in bare.
- Nested tests in the wrong order
- In =IF(A2>50,"Pass",IF(A2>80,"Distinction","Fail")) nobody ever gets a distinction, because 90 satisfies the first test and stops there. Nested IFs must run from the most restrictive boundary outwards.
- Treating "" as an empty cell
- IF's "" result is text. ISBLANK returns FALSE for it, COUNTA counts it, and pasting the column as values leaves cells that look empty but are not - which breaks Ctrl+End, sorting and Go To Special. Where truly empty cells matter, paste as values and use Go To Special to clear the zero-length strings.
Frequently Asked Questions
64 levels in Excel 2007 and later, and in Google Sheets; Excel 2003 stopped at 7. The practical limit is much lower - past three or four you will not be able to check the logic by reading it. IFS, SWITCH or a lookup table are easier to maintain.
No. Only the branch that is taken is evaluated, which is why =IF(B2=0,"",A2/B2) is safe - the division never runs when B2 is zero. That is not true of AND and OR, which always evaluate every argument they are given.
Use EXACT inside the test: =IF(EXACT(B2,"PAID"),"Yes","No"). The = operator in Excel never distinguishes case, in IF or anywhere else, and there is no setting that changes it.
Because value_if_false was left off entirely. =IF(A2>10,"High") returns the boolean FALSE when the test fails. Add the third argument - "" for a blank look, 0 if the result is going to be summed.
IFS is flatter and easier to read, and it needs Excel 2019, Microsoft 365 or Google Sheets. Nested IF works everywhere and can do one thing IFS cannot: branch inside a branch, where the second test only applies to rows that passed the first. IFS is a flat list of test-and-result pairs.