IF Function Basic Example in Excel and Google Sheets
Purpose
If I had to pick one formula that changed how I use spreadsheets, it would be IF. Before I understood…
If I had to pick one formula that changed how I use spreadsheets, it would be IF. Before I understood IF, my spreadsheets were static — they showed what was there, nothing more. After IF, they became intelligent. IF lets a cell make a decision: if this condition is true, show this; otherwise, show that. That sounds simple, and the basic form really is, but IF is the building block for almost every logical formula you will ever write. Mark pass or fail, flag overdue invoices, assign categories, show a bonus only for employees who hit their target — all of this is IF. It is available in every version of Excel and in Google Sheets with identical syntax, and once you learn it you will find yourself reaching for it constantly. The three parts of IF — the test, the result if true, and the result if false — take about five minutes to learn and a lifetime to fully exploit.
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. Common examples: A1>100, A1="Sales", ISBLANK(A1), A1<>0. Comparison operators available: > < >= <= = <> |
| value_if_true | Required | What to return when logical_test is TRUE. Can be a number, text in quotes, a cell reference, a blank (""), or another formula including a nested IF. |
| value_if_false | Optional | What to return when logical_test is FALSE. Omitting this argument returns FALSE. To return a blank cell use "" as the third argument. |
How it works
IF evaluates the logical_test argument first. This is any expression that resolves to true or false — a comparison like A1>100, a function like ISBLANK(A1), or even a formula that returns TRUE or FALSE. If the test result is true, IF returns the value_if_true argument. If the test result is false, IF returns value_if_false. The values you return can be text in quotes, numbers, blanks (written as empty double quotes ""), cell references, or even other formulas. You can nest another IF inside the value_if_true or value_if_false to chain multiple conditions, though for more than two or three conditions IFS or a lookup table is usually cleaner. One thing worth knowing: IF treats zero as false and any non-zero number as true, which sometimes catches people off guard when they check whether a cell contains a number.
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 |
| 8 | Grace | 49 | Fail |
| 9 | Hank | 91 | Pass |
| 10 | Iris | 55 | Pass |
| 11 | James | 38 | Fail |
Row 2: Pass
— Alice scored 72 which is greater than or equal to 50 so the test is true and Pass is returned.
David scored exactly 50 and also gets Pass because >= includes the boundary value. Bob scored 45 which is below 50 so the test is false and Fail is returned.
2
Calculate a tiered commission — 15% for sales above 10000, 10% for sales above 5000, and 5% for everything else.
| A | B | C | |
|---|---|---|---|
| 1 | Sales Rep | Sales | Commission |
| 2 | Alice | 12500 | =IF(B2>10000,B2*0.15,IF(B2>5000,B2*0.1,B2*0.05)) |
| 3 | Bob | 7200 | 720.00 |
| 4 | Carol | 3100 | 155.00 |
| 5 | David | 11800 | 1770.00 |
| 6 | Eve | 4900 | 245.00 |
| 7 | Frank | 8600 | 860.00 |
| 8 | Grace | 15000 | 2250.00 |
| 9 | Hank | 2200 | 110.00 |
| 10 | Iris | 6500 | 650.00 |
| 11 | James | 10000 | 1000.00 |
Row 2: 1875.00
— Alice sold 12500 which is above 10000, so the 15% rate applies giving 1875.
This nested IF checks the highest threshold first. If B2>10000 is true, it pays 15% and ignores the rest. Only if that is false does it check the 5000 threshold. James sold exactly 10000 which is not above 10000 so he falls into the second tier and earns 10%.
Common use cases
1. Mark invoices as Overdue when the due date has passed and Outstanding otherwise
=IF(C2<TODAY(),"Overdue","Outstanding")
2. Calculate a sales bonus — pay 10 percent of sales for reps who hit target, nothing for those who did not
=IF(B2>=C2,B2*0.1,0)
3. Assign a grade letter based on a percentage score
=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C","F")))
4. Show the balance only when a transaction exists, leave blank if the cell is empty
=IF(A2<>"",SUM($B$2:B2),"")
5. Flag rows where a product is below the reorder level with a warning message
=IF(C2<D2,"Reorder now","")
Common errors
Wrong result — formula returns the wrong branch
The most common IF mistake is using = when you mean >= or <=. A test like A1=50 only matches exactly 50, not anything above or below. Also check that text comparisons match case — Excel IF is case-insensitive but watch for spaces.
Fix: Double-check your comparison operator. Use >= for at least, <= for at most. Test the formula with a few known values to confirm it returns the expected results before copying it down.
Returns FALSE instead of blank
When you omit the value_if_false argument, IF returns FALSE when the condition is not met. This is rarely what you want in a report.
Fix: Always include the third argument. Use "" to return a visually blank cell when the condition is false.
#VALUE! inside the logical_test
If the logical_test references a cell containing an error like #N/A or #DIV/0!, IF often propagates the error rather than evaluating true or false.
Fix: Wrap the test in IFERROR or use ISERROR: =IF(ISERROR(A1),"Error",IF(A1>0,"Positive","Non-positive"))
Tips and variations
Use "" to return a blank instead of zero or FALSE
When you want a cell to appear empty rather than showing 0 or FALSE when the condition is not met, use two double quotes with nothing between them as the value_if_false. This keeps your spreadsheet visually clean, especially in columns that will be summed or averaged.
=IF(A2>0,A2*0.1,"")
Test the formula with edge cases first
Before copying an IF formula down an entire column, manually test it with the exact boundary values. If your condition is >=50, test with 49, 50 and 51. Many IF errors come from using > instead of >= or vice versa. Spending two minutes testing saves hours of fixing incorrect data later.
=IF(A2>=50,"Pass","Fail")
Use AND or OR inside IF for multiple conditions
Instead of nesting IFs, combine AND or OR directly inside the logical_test to test multiple conditions cleanly. AND requires all conditions to be true. OR requires at least one. This keeps the formula flat and readable rather than buried in layers of nested IF statements.
=IF(AND(A2>0,B2="Active"),"Include","Exclude")
Excel vs Google Sheets
Excel vs Google Sheets
IF works identically in Excel and Google Sheets. Same syntax, same argument structure, same behaviour. Formulas copy between the two applications without any changes. The only minor difference is that some older versions of Google Sheets displayed slightly different behaviour with empty cells in the logical_test, but this has been consistent for many years.
Related reading
Frequently asked questions
IF tests whether a condition is true or false and returns one value if the test passes and a different value if it fails. It is the foundation of conditional logic in Excel. Every time you want a cell to show different results depending on another cell's value, you are using IF logic. The basic structure is =IF(condition, value_if_true, value_if_false).
Yes — IF can return any value including numbers, text, blanks, formulas, or even other IF statements. If you write =IF(A1>100,A1*0.1,0) it returns 10 percent of the value if it is over 100 or zero otherwise. There is no requirement for the results to be text.
If you omit the third argument, Excel returns FALSE when the condition is not met. This is often not what you want. It is usually better to explicitly write FALSE, 0, or an empty string depending on your needs. An empty string returns a visually blank cell: =IF(A1>0,A1,"").
IF handles one condition and can be nested for multiple conditions, but nested IFs get hard to read quickly. IFS (available in Excel 2019+ and Google Sheets) handles multiple conditions in sequence without nesting. Use IFS when you have three or more conditions to test. Use IF for simple two-outcome decisions.
Yes, and this is one of the most useful combinations in Excel. Wrap AND or OR inside the condition argument to test multiple things at once. =IF(AND(A1>0,B1>0),"Both positive","Not both") returns the first value only when both conditions are true. =IF(OR(A1>0,B1>0),"At least one","Neither") returns the first value when either is true.