The Excel IF formula returns one result when a condition is true and another when it’s false, and once it clicks, you’ll find yourself reaching for it constantly. This guide covers everything from a single-condition IF to modern alternatives like IFS and SWITCH that save you from nesting headaches.
IF Function Syntax
Every IF formula follows the same structure:
=IF(logical_test, value_if_true, [value_if_false])- logical_test: a condition that evaluates to TRUE or FALSE (e.g.,
A2>=50)
– value_if_true: what to return when the condition is TRUE
– value_if_false: what to return when the condition is FALSE (optional, but always include it. Omitting it returns the word FALSE, which surprises most people)
Supported comparison operators: =, <> (not equal), <, >, <=, >=.
Fix #1: Write a Basic IF Formula
Goal: show "Pass" if a score in A2 is 50 or above, otherwise show "Fail".
- Click the cell where you want the result. For example, B2.
2. Type =IF(. Excel shows a tooltip: IF(logical_test, value_if_true, [value_if_false]).
3. Type the condition: =IF(A2>=50,
4. Add the true result: =IF(A2>=50,"Pass",
5. Add the false result and close the bracket: =IF(A2>=50,"Pass","Fail")
6. Press Enter. If A2 is 50 or higher, the cell shows Pass. Otherwise it shows Fail.
7. To apply the formula to more rows, drag the fill handle (the small square at the bottom-right corner of B2) down the column.
Text must always be in double quotes. Writing =IF(A2>=50,Pass,Fail) without quotes causes a #NAME? error because Excel thinks Pass is a named range. Numbers, by contrast, go in without quotes: =IF(A2>=50,1,0).
Fix #2: Use IF with AND or OR for Multiple Conditions
When you need two or more conditions checked at once, wrap them in AND or OR inside the IF, no nesting required.
IF + AND (all conditions must be true)
"Pass" only if score ≥ 50 and attendance ≥ 80%:
=IF(AND(A2>=50,B2>=0.8),"Pass","Fail")IF + OR (any condition can be true)
"Discount" if the customer tier is Gold or the order total exceeds 500:
=IF(OR(A2="Gold",B2>500),"Discount","No discount")You can stack as many arguments inside AND or OR as you need.
Fix #3: Handle Blank Cells So You Don't See "Fail" on Empty Rows
If you drag a formula down before all your data is filled in, blank cells show "Fail" (or whatever your false value is). Fix it with an outer blank check:
=IF(A2="","",IF(A2>=50,"Pass","Fail"))- The outer IF checks whether A2 is empty.
- If it is, the cell stays blank ("").
- If it isn't, the inner IF runs the real logic.
You can swap "" for a prompt instead: =IF(A2="","Enter a score",IF(A2>=50,"Pass","Fail"))
Fix #4: Reference Other Sheets in an IF Formula
You can pull values from a different sheet into either the logical test or the return values:
=IF(A2>10,Sheet2!A1,"Below threshold")If the condition is true, the formula returns whatever is in cell A1 on Sheet 2. The same cross-sheet reference works in the logical test itself: =IF(Sheet2!A1>10,"Yes","No").
Fix #5: Check Cell Type with ISBLANK, ISTEXT, and ISNUMBER
When you need to branch based on what kind of value is in a cell rather than its specific value, use Excel's IS functions inside IF:
=IF(ISBLANK(A2),"No data","Has data")
=IF(ISNUMBER(A2),"It's a number","Not a number")
=IF(ISTEXT(A2),"It's text","Not text")These are especially useful when your data comes from imports or user input and you can't guarantee what's in each cell.
Fix #6: Replace Nested IFs with IFS (Microsoft 365 / Excel 2019 and Later)
Nested IF statements work, but they get unreadable fast. This is a classic grading formula written the old way:
=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))The IFS function, available in Microsoft 365, Excel 2021, and Excel 2019, does the same thing without the bracket avalanche:
=IFS(
A2>=90,"A",
A2>=80,"B",
A2>=70,"C",
A2>=60,"D",
A2<60,"F"
)IFS evaluates conditions in order and returns the first TRUE result. If none of your conditions can ever cover all cases, add a catch-all at the end: TRUE,"Other".
On Excel 2016 or earlier? IFS isn't available, so stick with the nested IF version above, or restructure your logic into a lookup table with XLOOKUP or VLOOKUP.
Fix #7: Use SWITCH When You're Comparing One Value Against Many Options
If your logical test always compares the same cell against a list of possible values, SWITCH (Microsoft 365 / Excel 2019+) is cleaner than either IF or IFS:
Instead of:
=IF(A2="N","North",IF(A2="S","South",IF(A2="E","East",IF(A2="W","West","Unknown"))))Write:
=SWITCH(A2,"N","North","S","South","E","East","W","West","Unknown")The last argument with no matching value is the default, equivalent to the final value_if_false in a nested IF chain.
Fix #8: Use IF in an Excel Table with Structured References
If your data is formatted as an Excel Table (Ctrl+T), column names replace cell addresses in formulas. In a table with a Score column and a Result column, the IF formula looks like this:
=IF([@Score]>=50,"Pass","Fail")[@Score] always refers to the current row's Score value. Type the formula once and Excel fills it down the entire column automatically, with no dragging needed.
Fix #9: Spill IF Results Across a Range (Microsoft 365 / Excel 2021)
In Microsoft 365 and Excel 2021, you can enter a single IF formula that returns results for an entire range at once:
=IF(A2:A10>=50,"Pass","Fail")Type this in one cell and Excel automatically spills the results into the rows below, with no fill handle required. Make sure the cells below are empty, or you'll get a #SPILL! error.
Common IF Formula Errors and How to Fix Them
| Problem | What's happening | Fix |
|---|---|---|
#NAME? error | Text in the formula is missing double quotes | Wrap all text values in "quotes" |
| Result shows FALSE instead of your text | value_if_false argument was omitted | Always include both the true and false arguments |
| Empty rows show "Fail" | No blank check in the formula | Add an outer IF(A2="","",…) check |
| Wrong results when dragging down | A reference that should be fixed is moving | Lock it with $ e.g., $D$1 instead of D1 |
#SPILL! error | A spill formula has data blocking the spill range | Clear the cells below the formula |
| Comparison returns wrong result on imported data | Numbers stored as text (e.g., "50" vs 50) | Wrap the cell reference in VALUE() to convert it |
Note for non-US locales: Some regional Excel settings use semicolons as argument separators instead of commas, so =IF(A2>=50;"Pass";"Fail") rather than commas. If your formula throws an error immediately on entry, try switching to semicolons.
When to Stop Using IF and Switch to a Lookup
IF is the right tool for simple true/false branching. When you find yourself writing four or more nested IFs to map values to labels, a lookup function is almost always cleaner and easier to maintain:
- IFS, multiple conditions, still one formula (Excel 2019+)
- SWITCH, one value compared against many options (Excel 2019+)
- XLOOKUP / VLOOKUP, map values to results using a separate reference table
- Power Query, for conditional logic across large datasets where you'd otherwise write hundreds of IF formulas
Conclusion
The basic =IF(A2>=50,"Pass","Fail") pattern handles the majority of everyday use cases. Get that one solid and everything else builds naturally on top of it. If you're on Microsoft 365 or Excel 2019+, IFS is worth learning next: it replaces the most common reason people end up with unreadable nested IF chains. The SWITCH function is a gem for direction codes, status labels, or any scenario where one cell maps to one of several fixed outputs.