IF Function in Excel and Google Sheets
Purpose
Test a condition and return one value when true and another when false
Return value
value_if_true when the logical test passes, value_if_false when it fails
IF is the function that turns a spreadsheet from a calculator into a decision maker. Before I understood IF, every formula I wrote just computed — it never chose between outcomes. After IF, spreadsheets can think. Mark invoices overdue the moment their due date passes. Assign a letter grade based on a score. Calculate a bonus only for employees who hit their target. Flag rows that need a manager's review. Display a status message that updates every day without any manual input. All of this and an enormous amount more comes down to a single IF. The pattern is always the same: test a condition, return one thing when it is true, return something different when it is false. That is the entire function. Yet because it can return any value including another IF, another formula, or a blank, IF is the foundation for almost every conditional calculation you will ever write. It has been in Excel since the very beginning and works identically in Google Sheets.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=IF(logical_test, value_if_true, [value_if_false])
Arguments
| Argument | Required | Description |
|---|---|---|
| logical_test | Required | Any expression that evaluates to TRUE or FALSE. Use comparison operators: > < >= <= = <>. Examples: A1>100, B1="Sales", ISBLANK(C1), AND(A1>0,B1>0). |
| value_if_true | Required | What to return when logical_test is TRUE. Can be a number, text in quotes, a cell reference, "" for blank, or another formula including a nested IF. |
| value_if_false | Optional | What to return when logical_test is FALSE. If omitted IF returns FALSE. Use "" for blank or 0 for zero to avoid the word FALSE appearing in cells. |
How it works
IF evaluates the logical_test first. If the result is TRUE it immediately returns value_if_true and ignores value_if_false completely — the false branch is never even calculated. If the result is FALSE it skips value_if_true entirely and returns value_if_false. One behaviour that catches people out: IF treats zero as FALSE and any non-zero number as TRUE. This means =IF(A1,"Yes","No") returns Yes whenever A1 contains any non-zero number, and No when A1 is zero, blank, or FALSE. You can nest one IF inside another by placing the second IF in the value_if_false position of the first — this gives you three possible outcomes. Add a third nesting for four outcomes. Beyond two or three levels of nesting, the formula becomes very hard to read and maintain. That is when IFS becomes the better choice — it accepts up to 127 condition-result pairs in a flat list with no nesting at all. For multi-condition single tests, combine AND or OR inside the logical_test argument rather than nesting additional IFs.
Examples
1
Mark each student as Pass or Fail based on whether their score is 50 or above.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Alice | 72 | =IF(B2>=50,"Pass","Fail") |
| 3 | Bob | 45 | Fail |
| 4 | Carol | 88 | Pass |
| 5 | David | 50 | Pass |
| 6 | Eve | 31 | Fail |
| 7 | Frank | 67 | Pass |
Row 2: Pass
— Alice scored 72 which is >= 50 so the test is TRUE and Pass is returned.
David scored exactly 50 and also gets Pass because >= includes the boundary. Eve at 31 fails the test so Fail is returned.
2
Pay 10% commission for reps who hit target, 5% for those who did not.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Target | Commission |
| 2 | Alice | 12500 | 10000 | =IF(B2>=C2,B2*0.1,B2*0.05) |
| 3 | Bob | 7200 | 10000 | 360.00 |
| 4 | Carol | 11800 | 10000 | 1180.00 |
| 5 | David | 9500 | 10000 | 475.00 |
| 6 | Eve | 15000 | 10000 | 1500.00 |
Row 2: 1250.00
— Alice sold 12500 which meets the 10000 target so the 10% rate applies: 12500 x 0.1 = 1250.
Bob and David missed their targets so they earn 5%. Adjust column C to individual targets and the formula handles each rep automatically.
3
Flag invoices as Overdue or Outstanding based on whether the due date has passed today.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice | Amount | Due Date | Status |
| 2 | INV-001 | 1200 | 2024-01-15 | =IF(C2<TODAY(),"Overdue","Outstanding") |
| 3 | INV-002 | 850 | 2026-06-30 | Outstanding |
| 4 | INV-003 | 2100 | 2024-02-28 | Overdue |
| 5 | INV-004 | 450 | 2026-12-15 | Outstanding |
| 6 | INV-005 | 3200 | 2024-03-10 | Overdue |
Row 2: Overdue
— INV-001 due date is in the past so C2<TODAY() is TRUE and Overdue is returned.
TODAY() recalculates every time the file opens so the status updates automatically each day without any manual changes.
Common use cases
1. Show a performance bonus only for employees who reached their quarterly sales target, returning zero otherwise
=IF(C2>=D2,C2*0.1,0)
2. Assign a letter grade from a percentage score using nested IF statements for each grade boundary
=IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C","F")))
3. Display a running balance in a transaction log only when the row contains a transaction amount
=IF(A2<>"",SUM($B$2:B2),"")
4. Flag stock items as Reorder when quantity falls below the reorder level, showing OK otherwise
=IF(C2<D2,"Reorder","OK")
5. Assign a risk label based on a credit score, returning Low, Medium, or High using nested IF
=IF(A2>=750,"Low",IF(A2>=650,"Medium","High"))
Common errors
Wrong branch returned unexpectedly
The comparison operator is wrong for the boundary you need. =IF(A1=50,...) only matches the exact value 50. Scores of 49 or 51 both fall to the false branch even though they may logically belong to the same category. Also check whether you intended >= or > — the difference matters at boundary values like pass marks and thresholds.
Fix: Test the formula with boundary values (exactly at the threshold, one above, one below) before copying it down the column. Switch between = and >= or > and >= until the boundary behaviour is correct.
Returns FALSE instead of a blank or zero
The value_if_false argument was omitted. When IF has only two arguments it returns the word FALSE when the condition is not met rather than a blank or zero. This is a common source of confusion because the cell looks like it contains an error but technically it does not.
Fix: Always include the third argument. Use "" to return a blank cell that is invisible but does not disrupt SUM, AVERAGE, or COUNTIF formulas. Use 0 when a numeric result is required in the false case.
#VALUE! error in the logical_test
The logical_test references a cell containing a formula error such as #N/A, #REF!, or #DIV/0!. IF cannot evaluate an error as true or false and so returns #VALUE! itself.
Fix: Wrap the test in IFERROR before passing it to IF: =IF(IFERROR(A1,0)>100,"High","Low"). This converts any error in A1 to zero before the comparison, allowing IF to evaluate normally.
Tips and variations
Use "" for a blank false result instead of FALSE or zero
When the false branch should produce an empty cell rather than zero or the word FALSE, use two double-quotes with nothing between them as value_if_false. Blank cells are invisible, do not appear in charts as data points, and do not skew AVERAGE or COUNT calculations the way zero values do.
=IF(A2>0,A2*0.1,"")
Combine with AND or OR for multi-condition tests in a single formula
Rather than nesting multiple IF functions to test several conditions simultaneously, wrap AND or OR inside the logical_test. AND requires every condition to be true before the true branch runs. OR requires just one condition to pass. Both approaches keep the formula flat and readable compared to nested IFs.
=IF(AND(A2>0,B2="Active"),"Include","Exclude")
Switch to IFS for three or more distinct outcomes
Every additional nesting level in a multi-outcome IF makes the formula exponentially harder to read, edit, and debug. IFS takes any number of condition-result pairs in sequence and returns the result of the first condition that is true. The last pair can use TRUE as its condition to act as a catch-all else clause.
=IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C",TRUE,"F")
Excel vs Google Sheets
Excel vs Google Sheets
IF works identically in Excel and Google Sheets with the same syntax and behaviour. Formulas copy between the two applications without changes.
Related reading
Frequently asked questions
IF tests a condition and returns one value when the condition is TRUE and a different value when it is FALSE. You supply three arguments: the logical test to evaluate, the value to return when true, and the value to return when false. It is the most fundamental decision-making function in Excel and Google Sheets and the building block for almost all conditional logic in spreadsheets. Everything from flagging overdue invoices to assigning letter grades to calculating tiered commissions starts with an IF.
Yes — IF can return numbers, text, blank strings, cell references, formulas, or even arrays in either the true or false position. =IF(A1>100,A1*0.1,0) returns ten percent of the value when it exceeds 100, or zero otherwise. The return values can be any valid Excel expression including nested functions, date formulas, or lookups.
If you omit the third argument, IF returns the word FALSE when the condition is not met — not a blank cell, not zero, but the text FALSE. In most cases this is not what you want. Always include the third argument explicitly. Use 0 to return zero, or "" to return a visually blank cell that does not interfere with SUM or AVERAGE calculations.
Wrap AND or OR inside the logical_test argument. =IF(AND(A1>0,B1>0),"Both positive","Not both") requires both conditions to be true. =IF(OR(A1>100,B1>100),"Either high","Both low") requires just one. For three or more distinct outcomes, use IFS which lists condition-result pairs in a flat structure without the deep nesting that makes multiple IFs hard to maintain and debug.
Yes — IF is completely identical in both applications. The syntax is the same, the argument order is the same, the default behaviour when omitting the third argument is the same, and the case insensitivity of text comparisons is the same. Formulas using IF copy between Excel and Google Sheets without any changes needed.