Excel IF Formula Syntax
=IF(logical_test, value_if_true, value_if_false)Three arguments:
- logical_test: A question Excel can answer TRUE or FALSE. Examples:
A2>100,B2="Active",C2<>"". - value_if_true: What to return when the test is TRUE. Can be text, a number, a blank
"", or another formula. - value_if_false: What to return when the test is FALSE. Same options. If omitted, Excel returns FALSE when the test fails.
Copy-Paste IF Statement Examples
Basic pass/fail test
=IF(B2>=60,"Pass","Fail")Returns Pass when the score in B2 is 60 or higher. The value_if_true is "Pass" and value_if_false is "Fail". Text results must always be in double quotes.
Text match check
=IF(A2="Paid","Closed","Open")Checks whether a status cell equals a specific word. IF text comparisons are case-insensitive by default — "paid", "PAID", and "Paid" all return the same result.
Blank cell check
=IF(A2="","Missing","Complete")Returns "Missing" when the cell is empty. Useful for validation columns and import checklists where all required fields must be filled.
Greater than a target
=IF(C2>D2,"Over budget","OK")Compares actual values against planned values. Supports all comparison operators: =, <>, <, >, <=, >=.
Date deadline check
=IF(A2<TODAY(),"Overdue","On time")Flags dates that are earlier than today. TODAY() is a volatile function that updates every time the workbook recalculates.
IF with AND (both must be true)
=IF(AND(B2>=60,C2="Yes"),"Eligible","Not eligible")AND returns TRUE only when ALL its arguments are TRUE. Use this when a row must meet all conditions simultaneously to qualify.
IF with OR (at least one must be true)
=IF(OR(B2="High",C2>1000),"Review","Normal")OR returns TRUE when at least one argument is TRUE. Use this when any one of several conditions should trigger the result.
Nested IF for grade bands
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","D")))Tests conditions in sequence. The first TRUE result wins. For more than 3 bands, consider IFS() instead — it is easier to read and audit.
IF with IFERROR for safe division
=IFERROR(IF(A2/B2>1,"High","Low"),"Check input")Handles divide-by-zero or invalid inputs. The IFERROR wraps the outer formula so any error from the division returns a descriptive message instead.
Return a blank instead of zero
=IF(A2="","",A2*B2)Returns blank when A2 is empty, keeping reports clean until required input exists. Avoids showing 0 in rows that have not been filled in yet.
IF Statement With Text Values
Text values in IF formulas must always be in double quotes. Forgetting the quotes causes a #NAME? error:
=IF(A2="Complete","Done","Waiting") ✓ correct
=IF(A2=Complete,"Done","Waiting") ✗ wrong — Excel looks for a named range called CompleteText comparisons in IF are case-insensitive by default: "paid", "PAID", and "Paid" all return TRUE when compared to the string "paid". For case-sensitive text comparison, use EXACT inside IF:
=IF(EXACT(A2,"PAID"),"Case-sensitive match","Not matched")IF Statement With Dates
Dates in IF work most reliably when built with the DATE function:
=IF(A2<DATE(2026,7,1),"Old","Current")Using typed date strings like "07/01/2026" can behave differently depending on your regional settings — 07/01/2026 means July 1 in the US but January 7 in many European countries. DATE(year, month, day) is always unambiguous.
For date range checks (between two dates), combine with AND:
=IF(AND(A2>=DATE(2026,1,1),A2<=DATE(2026,12,31)),"This year","Other year")For checking if a date has passed:
=IF(A2<TODAY(),"Overdue",IF(A2=TODAY(),"Due today","Not yet due"))IF Statement for Blank Cell Handling
Use ="" to test for blank cells:
=IF(A2="","Required","Filled")Use <>"" to test for non-blank:
=IF(A2<>"",A2*B2,"")Note: a cell with a space character (" ") is not blank — A2="" returns FALSE for a cell containing only spaces. If cells may have been filled with spaces to look blank, use TRIM(A2)="" as the test.
IF With AND — Multiple Conditions That Must All Be True
=IF(AND(B2>=60,C2="Yes",D2<>0),"Eligible","Not eligible")AND returns TRUE only when every argument is TRUE. You can include up to 255 conditions inside AND, though formulas with more than 3-4 conditions become hard to read and maintain.
Common use cases for IF AND:
- Eligibility checks (score above threshold AND status is Active AND budget is available)
- Date range validation (start date is before end date AND both dates are filled)
- Data completeness checks (all required columns are filled for a row)
IF With OR — At Least One Condition Must Be True
=IF(OR(B2="High",B2="Critical",C2>10000),"Escalate","Normal")OR returns TRUE when any one of its arguments is TRUE. Useful for:
- Flagging rows that meet any of several criteria for review
- Handling multiple valid values for the same field
- Routing logic where different conditions lead to the same outcome
Nested IF for Multiple Bands
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","D")))Nested IFs work by testing conditions in sequence. The first TRUE result wins and no further conditions are checked. This creates a waterfall of conditions.
Important: order matters. In a greater-than nested IF, always test from highest to lowest. If you test 70 before 90, every score above 70 returns the 70+ result and the 90+ condition is never reached:
Wrong order: =IF(B2>=70,"C",IF(B2>=80,"B",IF(B2>=90,"A","D")))
Right order: =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","D")))IFS — The Modern Alternative to Nested IF
In Excel 2019 and Microsoft 365, the IFS function handles multiple conditions without nesting:
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"D")IFS takes pairs of arguments: logical_test, value_if_true, repeated for each condition. The last condition TRUE,"D" is the catch-all default (like the final else). IFS is easier to read, maintain, and extend than deeply nested IFs.
When IFS is not available (Excel 2016 or earlier), nested IF is the only built-in option.
SWITCH — For Exact Value Matching
When you need to return a different value for each specific match rather than a range test, SWITCH is cleaner than nested IF:
=SWITCH(A2,"N","North","S","South","E","East","W","West","Unknown")SWITCH compares the first argument to each value in the list and returns the corresponding result. The last argument is the default when no match is found. SWITCH is available in Excel 2019 and Microsoft 365.
Common IF Formula Mistakes
- Forgetting quotes around text results:
IF(A2>0,Yes,No)causes a #NAME? error. UseIF(A2>0,"Yes","No"). - Returning numbers as text:
IF(A2>0,"100",0)returns text "100" in the true case and number 0 in the false case. Mixing types in the true/false results causes inconsistent behavior in SUM and other downstream formulas. - Testing against the wrong data type:
IF(A2=1,"Yes","No")where A2 stores the number 1 as text returns "No" because"1"(text) does not equal1(number). - Wrong nesting order for range tests: See the grade example above — always test from highest to lowest for greater-than conditions.
- Using semicolons instead of commas: In European Excel locales, the separator is a semicolon:
=IF(A2>0;"Yes";"No"). The correct separator depends on your regional settings.
Best Practices for IF Formulas in Shared Workbooks
- Use helper columns: A complex condition split across two readable helper columns is safer than one giant nested IF that only you can decode three months later.
- Document assumptions: If an IF test uses a threshold like
>=60, put that 60 in a named input cell on an Assumptions tab. Then the IF reads>=Threshold, making it clear where the value comes from and easy to change. - Audit before sharing: The Spreadsheet Auditor can identify inconsistent formulas — rows where the IF formula pattern breaks because of accidental overwriting.
Frequently Asked Questions
Can IF return a formula result, not just a value?
Yes. Both the true and false arguments can be any formula: =IF(A2>0,SUM(B2:D2),AVERAGE(B2:D2)). Excel evaluates whichever branch applies and returns the result of that formula.
How do I use IF to color-code cells?
IF formulas control values, not colors. For color coding, use Conditional Formatting: go to Home > Conditional Formatting > New Rule, choose "Use a formula to determine which cells to format," and enter the same logical test you would use in IF. The formatting applies when the condition is TRUE.
Can IF handle more than two outcomes?
Yes, through nesting or IFS. For two outcomes, IF is perfect. For three to ten outcomes, IFS is cleaner. For many exact-value matches, SWITCH is best. For complex multi-column lookups, a dedicated lookup table combined with VLOOKUP or XLOOKUP is often more maintainable than a very large IF/IFS formula.