If you've ever stared at a spreadsheet wondering how to make Excel think for you — return one value when a condition is true and a different value when it isn't — you're looking for the IF function. It's the gateway drug to Excel logic. Once you understand it, the rest of the formula world opens up, because almost every advanced function in Excel either contains an IF statement, returns a TRUE/FALSE result, or builds on the same conditional logic you'll learn here.

The IF function asks a yes-or-no question, then hands back one of two answers. That's the whole idea. You write something like =IF(A2>=70,"Pass","Fail") and Excel reads the cell, decides, and writes the result for you. Simple. But also surprisingly deep when you start chaining conditions together, layering AND and OR inside, and trapping errors at the edges.

This guide walks you through the syntax line by line. We'll cover the three arguments, the comparison operators you'll lean on every day, how to nest IFs without losing your mind, and when to swap in IFS, AND, OR, or IFERROR instead. Real examples too — pass/fail grading, tiered discounts, sales commission, error trapping. You can copy and paste them straight into your own sheet, tweak the cell references, and have working formulas in under a minute.

By the end, you'll write IF statements the way you'd write a sentence. No hesitation. No second-guessing the parentheses. Just clean, working logic that does what you mean.

Here's the syntax, the way Excel expects it:

=IF(logical_test, value_if_true, value_if_false)

Three arguments. Separated by commas. Wrapped in parentheses. That's all there is to the shape of it.

logical_test is the question. It's an expression that evaluates to TRUE or FALSE — usually a comparison like A2>100 or B5="Yes". Excel doesn't care what the values are, only whether the test is true.

value_if_true is what gets returned when the test passes. It can be a number, text in quotes, a cell reference, another formula, or even another IF function (that's where nesting comes in).

value_if_false is the fallback. What you want returned when the test fails. Same rules — number, text, reference, formula. If you leave this argument out, Excel returns the word FALSE, which is almost never what you want, so always fill it in.

One quick gotcha: text values must sit inside double quotes. Numbers and cell references don't. So =IF(A2>10,"Yes","No") works, but =IF(A2>10,Yes,No) throws a #NAME? error because Excel thinks Yes and No are named ranges.

The logical_test isn't limited to greater-than. You've got a full set of comparison operators to work with, and picking the right one matters more than people realize.

Use = for exact matches. Useful when you're checking categories — =IF(A2="Active","Send email","Skip"). Use > and < for strict numeric comparisons. Use >= and <= when the boundary itself counts. A passing score of 70 should be >=70, not >70, or you'll fail the student who scored exactly 70.

The not-equal operator is <> — two characters, no space between them. So =IF(A2<>"Done","Pending","Complete") flags anything that isn't marked Done. Comes in handy for status columns and filtering.

Text comparisons in Excel are case-insensitive by default. =IF(A2="apple",1,0) returns 1 whether the cell contains apple, Apple, or APPLE. If you need case-sensitive matching, you'll have to combine IF with EXACT — like this: =IF(EXACT(A2,"Apple"),1,0).

Now the fun part. Nesting.

One IF gives you two outcomes — true or false. But what if you have three categories? Or five? You nest another IF inside the value_if_false slot (or value_if_true, technically), and keep going.

Grading example. Say you want A, B, C, D, F based on a score in A2:

=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))

Read it left to right. Is the score 90 or more? If yes, return A. If not, ask the next question: is it 80 or more? And so on, all the way down to F.

Order matters. You have to test from highest to lowest (or lowest to highest — consistent direction). If you reversed the logic and tested >=60 first, every passing score would return D because the first test would always succeed.

Excel allows up to 64 nested IFs, technically. But Microsoft's own docs warn that anything past 7 gets painful to debug. By that point you should be using a different function — VLOOKUP against a reference table, or the IFS function. We'll get there.

Excel 2019 introduced IFS, and it's the cleaner alternative when you've got multiple conditions to check in sequence. No more closing five parentheses at the end of a nested IF.

Syntax: =IFS(logical_test1, value1, logical_test2, value2, ...)

Same grading example, but using IFS:

=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F")

That last pair — TRUE,"F" — is the catch-all. TRUE is always true, so if no other condition matched, you land on F. Without it, IFS returns #N/A when nothing matches, which is rarely what you want.

IFS reads top-down. The first matching condition wins, and the rest are ignored. So order still matters, just like with nested IFs.

One caveat: IFS isn't available in older Excel versions (anything before 2019, including most legacy enterprise installs). If your file might be opened in Excel 2016 or earlier, stick with nested IF. Otherwise IFS is the better choice — easier to read, easier to maintain.

What if you need two conditions to be true at the same time? Or just one of several?

That's where AND and OR come in. You drop them inside the logical_test of an IF.

AND returns TRUE only when every condition is true. Example — a customer qualifies for a discount only if they spent over $500 and have been a member for at least a year:

=IF(AND(B2>500,C2>=1),"Discount","No discount")

OR returns TRUE if at least one condition is true. Say a row gets flagged if the status is Pending or the date is overdue:

=IF(OR(D2="Pending",E2<TODAY()),"Flag","OK")

You can stack them — AND inside OR, OR inside AND, both inside an IF. Just watch the parentheses. The deeper you nest, the easier it gets to drop a comma in the wrong place. Excel's formula bar will highlight matching parens in color when your cursor sits inside the formula. Use that.

Sooner or later your IF formula is going to hit an error — a divide-by-zero, a missing lookup, a text value where a number should be. By default, Excel returns the error and your beautiful spreadsheet looks broken.

IFERROR wraps any formula and gives you a fallback when something goes wrong. Pattern:

=IFERROR(your_formula, "fallback value")

Pair it with IF for clean reporting. Here's a sales-per-unit calculation that doesn't break when units are zero:

=IFERROR(IF(B2>0,A2/B2,0),"N/A")

IFERROR catches #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. All of them. The trade-off — it also hides genuine bugs, so don't slap IFERROR on every formula. Use it where errors are expected, like missing lookups, not where they indicate a real problem.

A few errors will trip you up early. Worth knowing what they mean.

#NAME? — you've misspelled IF, or you forgot to wrap text in quotes. =IF(A2>10,Yes,No) throws this. Add the quotes — =IF(A2>10,"Yes","No") — and it works.

#VALUE! — Excel can't evaluate your logical_test because the cell holds the wrong data type. Comparing a number cell to text, usually.

#N/A — typically comes from IFS when no condition matched and you forgot the TRUE catch-all. Or from a nested IF that returned a failed lookup.

#DIV/0! — your value_if_true or value_if_false includes a division and the denominator is zero. Wrap with IFERROR or add a check.

Mismatched parentheses. Excel will refuse to accept the formula and highlight where it lost track. Count your opening and closing parens. Each IF needs one of each.

Time for practical examples. The kind of IF formulas you'll actually build in real spreadsheets.

Pass/Fail grading. A2 holds a test score, B2 returns the result:

=IF(A2>=70,"Pass","Fail")

Tiered discount. Order total in A2, discount percentage returned in B2:

=IF(A2>=1000,0.15,IF(A2>=500,0.10,IF(A2>=100,0.05,0)))

Sales commission with tiers. Revenue in B2, commission rate based on bands:

=IFS(B2>=100000,0.12,B2>=50000,0.08,B2>=10000,0.05,TRUE,0.03)

Overdue invoice flag. Due date in C2, today's date as the threshold:

=IF(C2<TODAY(),"OVERDUE","On time")

Bonus eligibility — meet both targets. Sales in B2 must clear $50K and customer count in C2 must clear 100:

=IF(AND(B2>=50000,C2>=100),"Bonus","No bonus")

Each one of these can be extended. Wrap them in IFERROR, swap the static numbers for cell references so your thresholds live in a settings tab — that kind of thing. The pattern stays the same.

IF works on a single cell. But what if you want to apply the test across a whole range and sum or count the results?

That's the array IF pattern, usually paired with SUMPRODUCT. It looks like this:

=SUMPRODUCT((A2:A100>50)*(B2:B100))

Reads as: for every row from 2 to 100, check if column A is greater than 50, then multiply the TRUE/FALSE result (which Excel treats as 1 or 0) by column B. Sum it all up. Result — the total of column B values where column A exceeds 50. No array formula entry required. Just press Enter like any other formula.

You can do similar things with SUMIF and COUNTIF, which are usually simpler if you only have one condition. =SUMIF(A2:A100,">50",B2:B100) does the same calculation with cleaner syntax. But SUMPRODUCT shines when you've got two or three conditions to combine without writing array formulas the old-school way, and it works in every Excel version going back decades.

Modern Excel (365 and 2021) also has FILTER, which returns a dynamic array of matching rows. Faster than nested IFs for that use case. Pair it with COUNTA or SUM and you've got powerful reporting in one line. But IF is still the right tool when you want a single value back, not a list — pass/fail, eligible/not eligible, discount/no discount. Pick the function that matches the shape of the answer you need.

Quick rapid-fire pro moves before we wrap up.

Use named ranges for thresholds. Instead of hardcoding 70 into your pass/fail formula, name a cell "PassMark" and reference it: =IF(A2>=PassMark,"Pass","Fail"). Now you change the threshold in one place and every formula updates automatically. Saves you from hunting through dozens of cells when the grading scale changes mid-semester.

Indent your nested IFs. In the formula bar, press Alt+Enter (Option+Enter on Mac) inside a formula to break it across lines. Excel ignores the whitespace but you get readable logic. Pair the line breaks with consistent indentation and a tangled five-level nested IF suddenly reads like an outline.

Test pieces individually. Building a complex IF? Put each logical_test in its own helper cell first, confirm each returns TRUE or FALSE correctly, then assemble. Saves debugging time when something goes wrong — and something always goes wrong on a Friday afternoon when the boss needs the report by close of business.

Switch from nested IF to XLOOKUP for big tables. If you're nesting more than four IFs to map values, build a two-column lookup table and use XLOOKUP or VLOOKUP instead. Faster to read, faster to update, and your colleagues will be able to maintain the sheet after you've moved on to the next project.

Don't fear the helper column. Splitting a monster IF into two columns — one for the logical test, one for the result — makes the sheet easier to audit. Storage is cheap. Confusion is expensive. Hidden helper columns also work great when you need clean output on a dashboard but messy logic behind the scenes.

Watch out for blank cells. An empty cell isn't necessarily zero — depending on the test, Excel may treat it as text, a zero, or FALSE. If your IF formula behaves oddly when cells are blank, add an explicit check: =IF(A2="","Missing",IF(A2>=70,"Pass","Fail")). The first IF catches the empty case and prevents weird downstream results.

Use the Evaluate Formula tool. Excel has a built-in debugger most people never touch. Click the cell, then go to Formulas tab → Evaluate Formula. Excel walks through the calculation one step at a time, showing you exactly which test passed, which failed, and where the final value came from. Indispensable for untangling nested logic.

The IF function is the foundation of every conditional formula you'll write in Excel. Master the three-argument syntax, learn the comparison operators, get comfortable nesting two or three levels deep, then graduate to IFS, AND, OR, and IFERROR for the more complex stuff. From there, the jump to lookup functions and array formulas feels natural rather than overwhelming.

One last thing worth saying — IF formulas reward practice more than memorization. Reading about syntax is one thing. Writing your own pass/fail formula against a real list of student scores, then catching the bug when you typed > instead of >=, is what actually makes the syntax stick. Build small examples. Break them on purpose. Read the error messages. That's the loop that turns Excel logic from a chore into a tool.

Ready to test what you've learned? Try the practice quizzes below — they cover IF syntax, nested logic, common errors, and the AND/OR combinations you'll meet on any Excel certification exam. Work through a couple and you'll spot the patterns fast.