Arguments
logical_test- Any expression that evaluates to TRUE or FALSE, e.g. A1>100.value_if_true- What to return when the test is true.value_if_false- What to return when the test is false.
IF
Returns one value when a condition is true and another when it is false.
Syntax
=IF(logical_test, value_if_true, value_if_false)=IF(A1>=50, "Pass", "Fail")Returns: "Pass" when A1 is 50 or more
The classic threshold test.
=IF(A1="", "", A1*1.2)Returns: Blank when A1 is empty, otherwise A1 × 1.2
Guarding against blank inputs keeps columns clean.
How IF evaluates
IF evaluates logical_test, coerces the result to TRUE or FALSE, and returns one of its other two arguments. The test does not have to be a comparison - any expression that can be read as a boolean works, and Excel treats 0 as FALSE and every other number as TRUE. That is why =IF(COUNTIF(A:A, B2), "seen", "new") reads correctly without an explicit >0.
Both branches are written as arguments, and in Excel both are evaluated whether or not they are used. This is the difference that catches people coming from a programming language: =IF(B2=0, "n/a", A2/B2) still computes A2/B2 internally. The formula is safe because IF discards the unused branch along with any error it produced, but the work is done, so a heavy calculation in the losing branch costs the same as one in the winner.
The third argument is optional, and omitting it is rarely what you want: =IF(A1>10, "High") returns the boolean FALSE, not a blank, when the test fails. Writing "" instead gives an empty string, which looks blank but is text - it is not counted by COUNT, is not empty to ISBLANK, and sorts with the text values. Choosing between "", 0 and a real blank is a decision about what the downstream formulas need, not a matter of taste.
- The test is coerced, not required to be boolean
- Numbers become booleans on the 0/non-zero rule, and the text "TRUE" and "FALSE" are accepted in either case. Any other text gives #VALUE!, so =IF("yes", 1, 0) fails while =IF(1, 1, 0) does not.
- Comparisons are case-insensitive
- A2="north" is TRUE when A2 holds "NORTH". IF cannot distinguish them; wrap the comparison in EXACT when case has to matter.
- Blank cells compare as zero and as empty text
- An empty A1 makes both A1=0 and A1="" return TRUE, which is why a test meant to catch blanks also catches genuine zeros. Use ISBLANK(A1) when the distinction matters.
- Errors in the test propagate
- If logical_test itself evaluates to an error, IF returns that error rather than the false branch. Guarding a lookup requires IFERROR or IFNA around the lookup, not an IF on its result.
- Nesting is capped at 64 levels
- Excel 2007 and later allow 64 nested IFs; Excel 2003 allowed 7. Long before either limit the formula becomes unreadable - IFS, SWITCH, or a lookup table are all easier to check and to change.
- Returning "" is not returning a blank
- An empty string is a text value. ISBLANK sees it as filled, COUNT ignores it but COUNTA counts it, and a chart plots it as a gap only in some chart types. If a downstream SUM must ignore it, "" is fine; if a downstream COUNTA must, it is not.
Errors IF returns
| Error | What it means and how to fix it |
|---|---|
| #VALUE! | logical_test evaluated to text that is not TRUE or FALSE, or an argument is an array where a single value is expected. Check any nested function inside the test returns one value. |
| #NAME? | Text results were left unquoted: =IF(A1>10, High, Low) reads High and Low as undefined names. Quote text; do not quote numbers or cell references. |
| #DIV/0! or #N/A showing through | Produced by the branch that was returned, or by the test itself. IF does not suppress errors - that is IFERROR's job. Identify which part fails by evaluating it in its own cell first. |
| #REF! | One of the branches refers to a range that has been deleted. It surfaces only when that branch is the one returned, which is why it can appear long after the deletion. |
| Too many arguments | Excel rejects the formula at entry rather than returning an error value. It nearly always means a nested IF is missing a closing bracket, so its arguments have run into the outer call. |
More examples
=IF(AND($C2>=$G$1, $D2<>"Void"), $E2*$F2, 0)Returns: The line total, or 0 for excluded rows
AND combines two conditions into one test. Returning 0 rather than "" keeps the column numeric so SUM and AVERAGE below it behave.
=IF(ISBLANK($B2), "Awaiting input", IF($B2>$C2, "Over", "Within"))Returns: One of three labels
ISBLANK distinguishes an untouched cell from a zero, which a plain B2="" test cannot. The nested IF handles the two remaining cases.
=IF(N($A2)=0, "", TEXT($A2, "0.0%"))Returns: A formatted percentage, or nothing for empty rows
N coerces text and blanks to 0 in one step, so a single test covers both empty cells and non-numeric input before the value is formatted.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Supported with up to 64 levels of nesting. IFS and SWITCH are not available, so nested IFs are the only branching form here. |
| Excel 2019 | Identical, and joined by IFS and SWITCH, which flatten long chains into a single call. |
| Excel 2021 / Microsoft 365 | Unchanged. LET can name intermediate results so a long IF chain is written once and referenced, and IF over an array now spills. |
| Google Sheets | Same syntax and same coercion rules; Sheets does not enforce Excel's 64-level nesting cap. IFS and SWITCH are both available. |
| LibreOffice Calc | Supported with the same three arguments; IFS and SWITCH have been available since version 5.2. |
Does IF Work in Google Sheets?
Yes. IF uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements IF with two or three arguments. Omitting value_if_false returns the boolean FALSE, matching Excel rather than returning a blank.
The test is coerced with the same rules as Excel: a blank cell is FALSE, a number is TRUE unless it is zero, and the text TRUE or FALSE is read case-insensitively. Text that reads as a number follows the same zero/non-zero rule; any other text makes the whole formula #VALUE!, and an error in the test is returned unchanged rather than falling through to the false branch.
The editor also implements IFS, which returns the value paired with the first passing test and #N/A when none passes, so a long nested chain can be flattened there as it can in Excel 2019 and later.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
IF questions
It depends on what reads the column. "" is an empty string: it displays as nothing, is ignored by SUM and AVERAGE, but is counted by COUNTA, is not blank to ISBLANK, and sorts among the text values. 0 keeps the column purely numeric, which matters for charts and for AVERAGE - though it drags the mean down, which "" would not. For a column feeding further formulas, 0 or NA() is usually safer; for a column a person reads, "" is tidier.
Excel 2007 and later allow 64 levels; Excel 2003 allowed 7. In practice anything past three or four is hard to audit, because the closing brackets stop lining up with the conditions. IFS (Excel 2019 and later) takes test/value pairs in a flat list. SWITCH is better when every branch compares the same expression to a constant. For more than a handful of outcomes, a two-column lookup table with VLOOKUP or XLOOKUP is easier to change than any formula.
No. Excel evaluates both value arguments and then discards the one it does not need - there is no short-circuiting the way there is in a programming language. The formula is still safe, because an error in the discarded branch is discarded with it, but an expensive calculation there costs full price on every recalculation. If that matters, move the expensive part into a helper cell that the IF references.
Usually because logical_test produced text that is neither TRUE nor FALSE - for example a cell holding "Yes" passed straight in as the test, or a nested function that returned an error string. IF coerces numbers and the words TRUE and FALSE, and refuses everything else. Compare explicitly instead: =IF(A1="Yes", …). If the test contains a lookup, the #VALUE! may be propagating from it, so evaluate that part on its own first.