IF Then Statement in Excel: The Complete Guide to IF Function Logic 2026
Learn IF then statement in Excel with syntax, nested IFs, IF+AND/OR/NOT, IFS function, common errors, and practical formulas you can copy today.

Microsoft Excel: upcoming dates and official changes
- Jun 30, 2026: Certiport retired the Microsoft Office Specialist 2016 certifications, including MOS Excel 2016, on June 30, 2026. The Microsoft 365 Apps and Office 2019 Excel exams are still available. (certiport.pearsonvue.com)
- Updated Oct 16, 2026: Delivery is now Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) (previously Proctored live-in-the-application exam via Certiport Authorized Testing Centers).
- Updated Oct 16, 2026: Eligibility is now No formal prerequisites; about 150 hours of instruction and hands-on experience recommended (previously No formal prerequisites; ~150 hours of instruction and hands-on experience recommended (Associate)).
- Updated Oct 16, 2026: Time limit is now 50 minutes (60 minutes seat time) (previously 50 minutes (60 minutes seat time)).
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
- The MOS Excel (Microsoft 365 Apps) exam has 33 to 37 questions. (certiport.pearsonvue.com)
- The MOS Excel Expert (Microsoft 365 Apps) exam has 23 to 27 questions. (certiport.pearsonvue.com)
- Microsoft Office Specialist Excel exams last 50 minutes, with 60 minutes of total seat time. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- The passing score for Microsoft Office Specialist Excel exams is always 700 on Certiport's 1 to 1000 scaled score. (certiport.pearsonvue.com)
- After failing a Microsoft Office Specialist Excel exam, candidates must wait 24 hours before the first retake and 48 hours before each later retake, and there is no annual limit on attempts. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft Office Specialist Excel certifications earned on or after September 23, 2025 are valid for five years; those earned earlier do not expire. (certiport.pearsonvue.com)
- The Microsoft Office Specialist Excel exam is proctored and delivered live in the Excel application through Certiport Authorized Testing Centers, either in person or remote-proctored. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft recommends that candidates for the MOS Excel Associate (Microsoft 365 Apps) certification have about 150 hours of instruction and hands-on experience with Excel. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Candidates get their MOS Excel score report as soon as they finish the exam, and it can also be viewed in their Certiport profile. (certiport.pearsonvue.com)
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Microsoft Excel Exam at a Glance
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel Associate (MO-210)) | 33-37 questions | certiport.pearsonvue.com |
| Number of questions (Excel Expert (MO-211)) | 23-27 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Passing score | 700 on a 1–1000 scale | certiport.pearsonvue.com |
| Retake policy | Wait 24h after 1st fail, 48h before each later retake; no annual limit | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Validity | Valid 5 years (earned on/after Sept 23, 2025) | certiport.pearsonvue.com |
| Delivery | Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | No formal prerequisites; about 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report at end of exam; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Here's the first thing you should know: Excel doesn't actually have an "IF-THEN-ELSE" function the way programming languages do. The IF function in Excel is the if-then-else statement. When you write =IF(A1>10, "Big", "Small"), you're saying: IF A1 is greater than 10, THEN return "Big", ELSE return "Small". Same logic, different syntax. The keyword THEN is implied by the comma between arguments, and ELSE is implied by the third comma. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).
If you landed here searching for "if then statement in excel," you're really looking for IF. Master it, and you've cracked open the door to logical thinking inside spreadsheets. This guide walks you through the syntax, the operators, nested IFs, combining with AND/OR/NOT, the newer IFS function, and the errors that trip people up.
Why does this matter? Because once you understand IF, every other piece of Excel logic gets easier. Conditional formatting? Same logical expressions. Data validation rules? Same operators. Even the LAMBDA function in Microsoft 365 builds on the same comparison ideas. IF is the gateway, and we'll spend the next few minutes making sure you can walk through it without tripping.
Free Excel Study Tools
Interactive Excel resources on PracticeTestGeeks, free to start:
- Free Excel Questions and Answers — 16 questions with instant scoring and answer explanations
- Excel flashcards — review key terms and concepts card by card
- Excel study guide — topic-by-topic overview of what the exam covers
- Excel cheat sheet — one-page printable summary for last-minute review

Excel's IF function is the if-then-else statement. Syntax: =IF(logical_test, value_if_true, value_if_false). The logical_test evaluates to TRUE or FALSE, then Excel returns one of the two values based on the result. Everything else in this guide builds on that single idea.
Let's break the syntax down piece by piece. The logical_test is any expression that evaluates to TRUE or FALSE. It could be a numeric comparison like A1>50, a text comparison like B1="Yes", or a more complex formula. The value_if_true is what gets returned when the test passes. The value_if_false is what comes back when the test fails. That third argument is technically optional, but you should always include it. Skipping it gives you a literal FALSE in the cell, which usually isn't what you want.
You'll also want to know the operators Excel understands. The basics: > greater than, < less than, = equal to, >= greater than or equal, <= less than or equal, and <> not equal to. One quick warning for anyone coming from JavaScript or Python: in Excel formulas you use a single equals sign for comparison, not double. Writing == will break your formula.
The values you return can be almost anything. Text in quotes like "Pass". Numbers like 100 or 0. Cell references like A2 or B5. Even other formulas like SUM(C1:C10) or another IF. Excel evaluates whichever branch matches the test and returns that result, treating the unused branch as if it never existed.
One thing newcomers miss: the logical_test doesn't have to be a comparison. It can be any expression that resolves to TRUE or FALSE. Functions like ISBLANK, ISNUMBER, ISTEXT, ISERROR all return boolean values and slot right into IF. =IF(ISBLANK(A1), "Please fill in", A1) works perfectly. So does =IF(ISNUMBER(A1), A1*2, "Not a number").
IF in Action: Three Patterns You'll Use Every Day
Start simple. A basic IF compares one value and returns one of two outcomes.
- Pass or fail:
=IF(A1>50, "Pass", "Fail") - Text match:
=IF(B1="Yes", "Confirmed", "Pending") - Date check:
=IF(A1>TODAY(), "Future", "Past") - Empty cell:
=IF(A1="", "Empty", "Has Value") - Discount math:
=IF(A1*B1>1000, A1*B1*0.9, A1*B1)applies a 10% discount when the order tops $1,000.
Notice the quotes around text values. Forget them and Excel throws an error.
The IF statement in Excel shines when you combine it with logical helpers. Need to flag an order as urgent only if it's marked "Active" AND has a positive balance? =IF(AND(A1="Active", B1>0), "OK", "Review") handles it. Need to mark any test score above 50 as a pass, even if just one subject made it? =IF(OR(A1>50, B1>50, C1>50), "Pass", "Fail"). The logic reads almost like English.
One thing to watch when you combine IF with cell references: lock the ones that should stay put. If you're referencing a lookup table that won't move when you drag the formula, use an absolute reference in Excel by adding dollar signs (like $E$2:$F$10). Without that, your formulas drift and start pointing at the wrong cells.
Order of evaluation also matters. Excel reads IF arguments left to right and only evaluates the branch it needs. If your logical_test is TRUE, Excel never touches the value_if_false expression. That's not just efficient, it's also useful. You can put a potentially-error-throwing formula in the false branch and protect it with a check in the test: =IF(B1=0, 0, A1/B1) avoids the divide-by-zero error because Excel skips the division entirely when B1 is zero.
This short-circuit behavior is the same idea programmers know from languages like JavaScript or Python, just packaged differently. Use it deliberately. Put your guard condition in the logical_test, and let the rest of the formula assume that condition is met. Cleaner formulas, fewer errors, easier debugging six months later when you've forgotten what the file even does.
Real-World IF Patterns You'll Reach For
- Formula: =IF(A1>=10, B1*0.15, IF(A1>=5, B1*0.10, B1*0.05))
- Logic: 15% bonus for 10+ years tenure, 10% for 5+, 5% otherwise
- Use case: HR payroll, performance reviews
- Formula: =IF(A1="Active", "OK", IF(A1="Pending", "Review", "Reject"))
- Logic: Three text states map to three actions
- Use case: Ticket queues, application pipelines
- Formula: =IF(B1>DATEVALUE("2026-04-15"), 50, 0)
- Logic: Charges $50 if payment date passes April 15
- Use case: Invoice processing, rent rolls
- Formula: =IF(A1<10, "Reorder", "OK")
- Logic: Flags any stock count below 10 for reordering
- Use case: Warehouse dashboards, retail backrooms
If you're on Excel 2019 or Microsoft 365, you have access to the IFS function. It's the modern alternative to nested IFs, and once you try it you won't go back for anything with four or more conditions. Syntax: =IFS(condition1, value1, condition2, value2, ..., TRUE, default). That final TRUE, default pair acts as the catch-all else.
Rewriting the grade calculator with IFS: =IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", TRUE, "F"). Same result as the nested version, but you can actually read it. No closing parenthesis pileup at the end either. IFS is also marginally faster than the equivalent nested IF in big workbooks. The catch: IFS doesn't exist in Excel 2016 or earlier. If you share files with people on older versions, stick with nested IF for compatibility.
SWITCH is another close cousin worth mentioning. It works like IFS but for exact-match values rather than range comparisons. =SWITCH(A1, "Mon", 1, "Tue", 2, "Wed", 3, 0) maps weekday abbreviations to numbers, returning 0 for anything that doesn't match. Use SWITCH when you have a fixed list of possible values and a result for each. Use IFS when you have ranges or compound conditions.

Nested IF vs IFS Function
- +IFS reads top-to-bottom like a switch statement
- +No nested parenthesis nightmare with IFS
- +Slightly faster than nested IF on large datasets
- +Easier to maintain and debug
- +Built-in catch-all using TRUE as the final test
- +Nested IF works in every version of Excel ever made
- +Nested IF is universally compatible across platforms
- −IFS requires Excel 2019 or Microsoft 365
- −Nested IF gets unreadable past 4 levels
- −Nested IF is slower on 100k+ row sheets
- −Forgetting a closing parenthesis breaks nested IF silently
- −IFS still struggles when conditions overlap
- −Neither handles 10+ conditions gracefully (use VLOOKUP instead)
- −Both can be replaced by SWITCH for exact-match logic
Pros and Cons at a Glance
| Pros | Cons |
|---|---|
| IFS reads top-to-bottom like a switch statement | Forgetting a closing parenthesis breaks nested IF silently |
| No nested parenthesis nightmare with IFS | IFS still struggles when conditions overlap |
| Slightly faster than nested IF on large datasets | Both can be replaced by SWITCH for exact-match logic |
Now for the errors. Every Excel user hits these eventually, so it's worth knowing what they mean. #NAME? usually means you misspelled the function name. Excel doesn't know what =IFF(...) or =iF(...) with a typo means. #VALUE! appears when you compare incompatible types, like trying to test if text is greater than a date. #NUM! shows up with extreme numerical values, though it's rare with plain IF logic.
The subtle bugs are worse than the loud ones. If you order nested conditions wrong (checking A1>=60 before A1>=90), every score above 60 returns "D". Excel stops at the first match. If you forget to put quotes around text values, Excel reads them as named ranges and fails. If you write == instead of =, the formula breaks. Always test your formulas with edge cases: the lowest value, the highest, an empty cell, a typo'd entry.
Another trap: silently mixing data types. If column A contains text that looks like numbers (because the cell is formatted as text or imported from a CSV with leading apostrophes), then =IF(A1>100, ...) can behave unpredictably. Excel may compare them as strings rather than numbers, so "9" ends up "greater than" "100" alphabetically. Force a numeric comparison with =IF(VALUE(A1)>100, ...) or fix the source data with Text to Columns.
Common IF Function Errors and Fixes
- ✓#NAME? error - check spelling of IF, AND, OR, IFS (no typos, no extra periods)
- ✓#VALUE! error - you're comparing incompatible data types, like text against a number
- ✓Wrong nested order - put the most specific or highest threshold condition first
- ✓Returns FALSE in cell - you forgot the value_if_false argument; always include it
- ✓Missing quotes - text values like "Yes" must be wrapped in double quotes
- ✓Using == instead of = - Excel formulas use single equals for comparison
- ✓Hardcoded values - replace literal numbers with cell references for maintainability
- ✓Mismatched parentheses - count opening and closing parens; nested IFs are notorious for this
- ✓Date comparison fails - dates must be wrapped in DATEVALUE() or referenced from a real date cell
- ✓Case sensitivity - IF treats "YES" and "yes" as equal; use EXACT() if case matters
Let's talk about errors as a feature, not a bug. Sometimes you want to catch errors and substitute a friendlier message. That's where IFERROR and IFNA come in. =IFERROR(A1/B1, "Error") returns "Error" instead of #DIV/0! when B1 is zero. =IFNA(VLOOKUP(...), "Not Found") catches only #N/A errors and ignores the rest. Both pair nicely with IF when you're building dashboards or customer-facing reports.
One pattern I use constantly: wrapping VLOOKUP inside IFERROR inside IF. Something like =IF(A1="", "", IFERROR(VLOOKUP(A1, table, 2, FALSE), "Not found")). It checks for an empty input first, runs the lookup if there's something to look up, and shows a clean message if the lookup fails. Three layers of logic, zero error messages staring back at the user.
The difference between IFERROR and IFNA matters more than people think. IFERROR catches every error: #N/A, #DIV/0!, #VALUE!, #REF!, #NUM!, #NAME?, #NULL!. That's blunt. If you mask all errors with the same message, you'll never notice a #REF! that signals deleted data. IFNA only catches #N/A, which is the natural "not found" signal from VLOOKUP and XLOOKUP. Use IFNA for lookups specifically, and let other errors surface so you can fix them.
Decision Flow: Pick the Right Function
Step 1: How many outcomes?
Step 2: Are the conditions ranges or categorical?
Step 3: Will the rules change often?
Step 4: Need compound logic?
Step 5: How big is your dataset?

Text comparisons inside IF deserve a special note. By default, Excel's = operator is case-insensitive when comparing strings. So =IF(A1="cat", "Animal", "Other") returns "Animal" whether A1 contains "Cat", "CAT", or "cat". If case matters, swap in EXACT: =IF(EXACT(A1, "cat"), "Animal", "Other"). Now only lowercase "cat" returns the first value. EXACT compares two strings character by character, including capitalization, and returns TRUE only when they match perfectly.
Whitespace is another gotcha for text matching. A trailing space in a cell (common in copy-pasted data) will cause an apparent match to fail silently. "Cat " with a trailing space is not equal to "Cat" without one. Wrap your reference in TRIM to handle this: =IF(TRIM(A1)="Cat", "Match", "No"). TRIM removes leading, trailing, and double-spaces, leaving only single spaces between words.
Need partial text matching? IF alone can't do wildcards, but you can pair it with SEARCH or COUNTIF. =IF(ISNUMBER(SEARCH("cat", A1)), "Yes", "No") returns "Yes" if "cat" appears anywhere in A1 (case-insensitive). Swap SEARCH for FIND if you want case sensitivity. This pattern is gold for filtering messy data like email addresses, product codes, or notes fields.
IF Function by the Numbers
Math inside IF unlocks conditional arithmetic. =IF(A1>10, A1*B1, 0) multiplies two cells only when the first exceeds 10, otherwise returns zero. =IF(A1>0, SUM(B1:B10), 0) sums a range conditionally. That said, if you're doing pure conditional totals, SUMIF and SUMIFS are usually a better fit. They're built specifically for "sum these cells where conditions match," and they handle multi-criteria scenarios with cleaner syntax than nested IF chains.
Speaking of math, Excel quietly treats TRUE as 1 and FALSE as 0. That means you can multiply by a boolean to get conditional behavior without writing IF at all. =(A1>0)*B1 returns B1 if A1 is positive, otherwise zero. It's a power-user trick. Cleaner for some use cases, harder to read for others. Pick the approach that the next person reading your file will understand.
Dates and IF can be tricky because dates are stored as serial numbers under the hood. January 1, 1900 is 1, and every day after is one more. That's why you can do math on dates and why comparisons work. =IF(A1>TODAY(), "Future", "Past or Today") works because TODAY returns a serial number and A1 (if formatted as a date) is also a serial number. The comparison happens on the underlying numbers, not the visible text.
Want to check if a date falls within a range? Use AND: =IF(AND(A1>=DATE(2026,1,1), A1<=DATE(2026,12,31)), "2026", "Other"). Want to know if a date is a weekday? =IF(WEEKDAY(A1, 2)<=5, "Weekday", "Weekend"). The second argument 2 in WEEKDAY tells Excel to count Monday as 1 and Sunday as 7, which makes the comparison clean.
Original (5 levels deep):
=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", IF(A1>=60, "D", "F"))))
Same logic with IFS (Excel 2019+):
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", TRUE, "F")
Same logic with VLOOKUP (most maintainable):
Build a two-column table with thresholds (0, 60, 70, 80, 90) and grades (F, D, C, B, A), then use =VLOOKUP(A1, $E$2:$F$6, 2, TRUE). All three return the same letter grades. VLOOKUP wins when the grading scale might change, because you edit the table instead of every formula.
The Excel IF statement doesn't change much between platforms, but a few small things are worth flagging. Excel for Mac supports IF, IFS, AND, OR, NOT, IFERROR, and IFNA identically to the Windows version. Keyboard shortcuts for editing formulas differ slightly (Cmd instead of Ctrl, F2 still works for cell edit). Excel for the Web has full IF support but limits some advanced array operations like dynamic arrays in older subscriptions. Anything you write on desktop with IF, AND, OR, NOT, and IFS will work on the web version too.
Performance is where IF can quietly bite you. A workbook with 100,000 rows and a nested-5-deep IF in every row recalculates noticeably slower than the same workbook using XLOOKUP against a reference table. If your file feels sluggish, audit your formulas. Replace deep nested IFs with VLOOKUP/XLOOKUP for many conditions, use IFS for medium nesting, and reserve simple IF for two-outcome scenarios. Power Query handles ultra-large datasets with its own M language IF and runs outside the spreadsheet calculation engine entirely.
Here's the bottom line. Excel's IF function is the if-then-else statement, regardless of how programming languages elsewhere structure their conditional logic. Start with the basic three-argument form. Layer in AND or OR when you need compound conditions. Use NOT sparingly (and usually flip the operator instead).
Reach for IFS when nesting hits three or four levels and you're on Excel 2019 or newer. Switch to VLOOKUP or XLOOKUP when you have many conditions or a reference table that might change. Always include the value_if_false argument, always quote text values, always use single equals for comparison, and always test your formula with edge cases before trusting it on real data.
Once IF clicks for you, the rest of Excel opens up. Conditional formatting uses the same logic. Data validation uses the same logic. Array formulas and lambdas extend the same logic to entire ranges. It's the foundational pattern, and twenty minutes spent really nailing it pays off for years.
One last piece of advice: keep your formulas small. If a single IF expression spans three lines on screen, you probably need to break it apart. Use helper columns to compute intermediate values, then reference those helpers in your final IF. Helper columns are not a sign of weakness. They're how serious Excel users build maintainable spreadsheets. Hidden columns or grouped columns keep the worksheet looking clean while preserving the logic underneath. Your future self will thank you.
Sample Excel Practice Questions
Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.
A user wants to create a formula that first calculates the total sales (Price * Quantity) and then applies a discount if the total is over $1,000. To avoid calculating the total sales twice within an IF statement, which function is most efficient for defining the total sales as a named variable within the formula?
- A. IFS
- B. LET
- C. VAR
- D. DEFINE
Answer: B. LET
The LET function is designed to declare and assign values to variables within a formula's scope. This improves readability and performance by preventing redundant calculations. The user can define 'TotalSales' as Price * Quantity once and then reference 'TotalSales' in the subsequent calculation.
What does the SUMPRODUCT function do?
- A. Sums numbers then multiplies by a constant
- B. Multiplies corresponding elements of arrays and returns the sum of those products
- C. Finds the product of a range of numbers
- D. Sums only positive numbers in a range
Answer: B. Multiplies corresponding elements of arrays and returns the sum of those products
SUMPRODUCT multiplies corresponding values in two or more arrays and then sums all those products in a single step.
What does the YEARFRAC function calculate?
- A. The number of years between two dates
- B. The fraction of the year represented by the number of days between two dates
- C. The year portion of a date value
- D. The number of workdays in a year
Answer: B. The fraction of the year represented by the number of days between two dates
YEARFRAC returns the fraction of the year represented by the number of whole days between two dates, useful for financial calculations.
Which function replaces all occurrences of a substring within a text string?
- A. FIND
- B. SEARCH
- C. REPLACE
- D. SUBSTITUTE
Answer: D. SUBSTITUTE
SUBSTITUTE replaces all (or a specific) occurrences of a given text with new text within a string.
Take the full Excel practice test
Many readers pair this guide with the excel test practice to check their readiness before exam day.
Many readers pair this guide with the excel co-authoring in real time to check their readiness before exam day.
Many readers pair this guide with the excel linear regression to check their readiness before exam day.
IF Then Statement in Excel Questions and Answers
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- learn.microsoft.com — Retake policy, test delivery, time limit, eligibility · verified Oct 2026
- certiport.pearsonvue.com — Exam format, score validity, passing score, score release, exam information · verified Oct 2026