IF Formula Excel – Nested IF Tutorial with Real Examples (Complete Guide)

The IF formula is the single most useful formula in all of Microsoft Excel. It teaches your spreadsheet how to think. Instead of you reading every row and deciding what to type, IF reads the data and decides for you – for every single row – in one second.

This guide is written in plain language with real stories, real office examples, and step-by-step explanations. Whether you have never used a formula before, or you know basic Excel but want to finally understand Nested IF properly – this guide will make it completely clear.

Part 1: What Is the IF Formula? – In Plain English

Before we look at any Excel formula, let us understand what IF actually does – using everyday life.

Real Story: IF in Everyday Life

Think about how you make decisions every day. You check the weather: IF it is raining, THEN I will carry an umbrella, ELSE I will leave it at home. You check your wallet: IF I have enough money, THEN I will order the big pizza, ELSE I will order the small one. You are already using IF logic every single day – you just never called it that.

Excel’s IF formula works exactly the same way. You give it a condition to check. If the condition is TRUE, it does one thing. If the condition is FALSE, it does something else.

That is the entire concept. Everything else – including Nested IF – is just building on this simple idea.

Part 2: The IF Formula Syntax – Every Part Explained

Here is the syntax of the Excel IF formula:

IF Formula Syntax:
=IF( logical_test, value_if_true, value_if_false )

Part of Formula	What It Means	Real Example
logical_test	The condition you want Excel to check – this must be something that is either TRUE or FALSE	B2>=50  or  C2=”Yes”  or  A2>1000
value_if_true	What Excel should show or do if the condition is TRUE	“Pass”  or  “Bonus”  or  500  or  D2*0.1
value_if_false	What Excel should show or do if the condition is FALSE	“Fail”  or  “No Bonus”  or  0  or  “”
= sign	Always starts every Excel formula – never forget this	=IF(…)
Commas	Separate the three parts – use commas, not semicolons (in most regions)	=IF(A2>10, “Yes”, “No”)
Quotes around text	Any text result must be wrapped in double quote marks	“Pass”  “Bonus”  “Approved”

Numbers Need No Quotes, Text Always Does

If your result is a number – like 500 or 0 – write it without quotes: =IF(A2>100, 500, 0). If your result is text – like Pass, Fail, Bonus – always wrap it in double quotes: =IF(A2>100, “Pass”, “Fail”). Forgetting quotes on text is the most common beginner mistake.

Part 3: Your First IF Formula – Step by Step

Priya’s Attendance Problem Let us go back to Priya. She has employee names in column A and their attendance percentage in column B. She needs to write “Present” in column C if attendance is 80% or above, and “Absent” if it is below 80%. Let us build this formula together, one step at a time.

Step 1 – Identify the Three Parts

Question	Answer for Priya’s Problem
What condition do I want to check?	Is the attendance in B2 greater than or equal to 80?
What should happen if TRUE (condition met)?	Show the word Present
What should happen if FALSE (condition not met)?	Show the word Absent

Step 2 – Write the Formula

Formula for Cell C2:
=IF(B2>=80, "Present", "Absent")

Step 3 – See What Excel Does for Each Row

Cell	Value in Sheet	Formula Used	Excel Shows
C2	82%	=IF(B2>=80,”Present”,”Absent”)	Present
C3	71%	=IF(B3>=80,”Present”,”Absent”)	Absent
C4	95%	=IF(B4>=80,”Present”,”Absent”)	Present
C5	60%	=IF(B5>=80,”Present”,”Absent”)	Absent
C6	80%	=IF(B6>=80,”Present”,”Absent”)	Present

Step 4 – Copy the Formula Down

Click on cell C2. Move your mouse to the small green square at the bottom-right corner of the cell (this is called the fill handle). Double-click it. Excel copies your formula all the way down to the last row automatically. Priya’s entire 200-row sheet is done in under 60 seconds.

Key Concept: Why >= and Not Just >?

The >= operator means ‘greater than OR equal to’. So B2>=80 is TRUE when attendance is 80, 81, 90, 100 – anything 80 and above. If you wrote B2>80, then 80% itself would be marked Absent, which is unfair. Always think carefully about whether your boundary value should be included.

Part 4: Comparison Operators – The Tools Inside Your IF

Every IF formula has a condition – and that condition uses a comparison operator. Here are all six operators you need to know:

Operator	Meaning	Example	TRUE when…
>	Greater than	B2>100	B2 is 101, 200, 999 – anything above 100
<	Less than	B2<50	B2 is 49, 30, 1 – anything below 50
>=	Greater than or equal to	B2>=80	B2 is 80, 85, 100 – 80 and above
<=	Less than or equal to	B2<=30	B2 is 30, 20, 5 – 30 and below
=	Equal to	B2=”Yes”	B2 contains exactly the word Yes
<>	Not equal to	B2<>””	B2 is not empty – it has any value

Checking for Empty Cells

One of the most useful tricks: =IF(B2=””, “Missing”, “OK”) – this checks if cell B2 is blank. The two double-quote marks with nothing between them means empty. Use this to catch missing data in your sheets.

Part 5: Real-World IF Examples – Office, School & Business

Let us look at ten real, practical IF formula examples from everyday work situations. Each one builds your confidence and shows you a different way to use the same simple formula.

Example 1 – HR: Pass or Fail for a Job Test

Scenario: Your company runs a written test for new recruits. Score 50 or above means Pass. Below 50 means Fail.

Cell C2 - Test Result:
=IF(B2>=50, "Pass", "Fail")

Example 2 – Sales: Check if Target Was Achieved

Scenario: Sales team has monthly targets in column B and actual sales in column C. You want column D to say Achieved or Not Achieved.

Cell D2 - Target Status:
=IF(C2>=B2, "Achieved", "Not Achieved")

Example 3 – Finance: Apply a Discount

Scenario: Orders above Rs. 5,000 get a 10% discount. Orders at or below Rs. 5,000 get no discount. Return the discount amount in column C.

Cell C2 - Discount Amount:
=IF(B2>5000, B2*0.10, 0)

Example 4 – School: Grade Pass or Fail with Custom Message

Scenario: Students with marks above 33 pass. You want a friendly message instead of just Pass/Fail.

Cell C2 - Student Message:
=IF(B2>33, "Congratulations! You Passed.", "Please try again next term.")

Example 5 – Logistics: Flag Overdue Deliveries

Scenario: Expected delivery date is in column B. Today’s date is in column C. If delivery is more than 2 days late, flag it.

Cell D2 - Delivery Status:
=IF(C2-B2>2, "OVERDUE", "On Time")

Example 6 – HR: Check if an Employee is Active

Scenario: Column B has the status field. You want to show a green-light message for active employees.

Cell C2 - Engagement Status:
=IF(B2="Active", "Include in Payroll", "Do Not Process")

Example 7 – Finance: Tax Slab (Simple 2-Level)

Scenario: Income above Rs. 5,00,000 is taxed at 20%. Income at or below gets a 10% tax rate. Calculate the tax amount.

Cell C2 - Tax Amount:
=IF(B2>500000, B2*0.20, B2*0.10)

Example 8 – Operations: Stock Alert

Scenario: Current stock level is in column B. If stock falls below 50 units, show a Reorder alert.

Cell C2 - Stock Alert:
=IF(B2<50, "REORDER NOW", "Stock OK")

Example 9 – Admin: Check if a Cell Has Data

Scenario: You have a column where employees should have filled in their department. You want to flag rows where the department is missing.

Cell C2 - Data Check:
=IF(B2="", "Missing – Please Fill", "OK")

Example 10 – Sales: Commission Calculation

Scenario: Salespeople who exceed Rs. 1,00,000 in monthly sales earn a 5% commission. Others earn no commission.

Cell C2 - Commission Earned:
=IF(B2>100000, B2*0.05, 0)

Part 6: Nested IF – When You Have More Than Two Outcomes

Priya Gets a Harder Problem: A week later, Priya’s manager comes back. This time, the task is more complex: employees with attendance above 90% get a Bonus. Employees between 75% and 90% have a Normal status. Employees below 75% get a Warning letter. Three outcomes. A basic IF formula only handles two – TRUE or FALSE. This is where Nested IF comes in.

A Nested IF is simply an IF formula inside another IF formula. Instead of putting a text answer in the value_if_false slot, you put another IF formula there. This lets you check a second condition if the first one is FALSE – and a third condition if the second is also FALSE – and so on.

Understanding Nested IF – The Simple Mental Model

Think of it like a security guard with three gates:

• Guard checks: Is attendance ABOVE 90? YES → Give Bonus. NO → Go to Gate 2.
• Gate 2 checks: Is attendance 75 or above? YES → Normal status. NO → Go to Gate 3.
• Gate 3 (the last): Everything left gets Warning.

In Excel, that logic looks like this:

Nested IF - Attendance Bonus System (Cell C2):
=IF(B2>90,
      "Bonus",
      IF(B2>=75,
            "Normal",
            "Warning"
      )
)

-- Single line version (what you type in Excel) --
=IF(B2>90, "Bonus", IF(B2>=75, "Normal", "Warning"))

Step-by-Step: How to READ a Nested IF Formula

Reading a Nested IF from left to right is the key to understanding it. Let us read the formula above out loud:

• Step 1 – Excel checks the FIRST condition: Is B2 greater than 90?
• Step 2 – If YES (TRUE): Excel shows the word Bonus. Formula stops here for this row.
• Step 3 – If NO (FALSE): Excel moves to the SECOND IF inside.
• Step 4 – SECOND condition: Is B2 greater than or equal to 75?
• Step 5 – If YES: Excel shows Normal. Formula stops here.
• Step 6 – If NO: This is the final else – Excel shows Warning.

See It in Action – Result Table

Cell	Value in Sheet	Formula Used	Excel Shows
C2	95%	=IF(B2>90,”Bonus”,IF(B2>=75,”Normal”,”Warning”))	Bonus
C3	82%	=IF(B3>90,”Bonus”,IF(B3>=75,”Normal”,”Warning”))	Normal
C4	74%	=IF(B4>90,”Bonus”,IF(B4>=75,”Normal”,”Warning”))	Warning
C5	91%	=IF(B5>90,”Bonus”,IF(B5>=75,”Normal”,”Warning”))	Bonus
C6	75%	=IF(B6>90,”Bonus”,IF(B6>=75,”Normal”,”Warning”))	Normal

Going Deeper – A 4-Level Nested IF for School Grades

The School Grade Problem: Rajiv is a teacher. He needs to assign letter grades to 150 students based on their marks out of 100: A (85 and above), B (70–84), C (55–69), D (40–54), and F (below 40). Five outcomes – four levels of nesting.

4-Level Nested IF - School Grades (Cell C2):
=IF(B2>=85, "A",
   IF(B2>=70, "B",
      IF(B2>=55, "C",
         IF(B2>=40, "D",
            "F"))))

-- Single line (type this in Excel) --
=IF(B2>=85,"A",IF(B2>=70,"B",IF(B2>=55,"C",IF(B2>=40,"D","F"))))

Cell	Value in Sheet	Formula Used	Excel Shows
C2	92	=IF(B2>=85,”A”,IF(B2>=70,”B”,IF(B2>=55,”C”,IF(B2>=40,”D”,”F”))))	A
C3	73	same formula above applied to B3	B
C4	58	same formula above applied to B4	C
C5	44	same formula above applied to B5	D
C6	28	same formula above applied to B6	F

Build Nested IF from the Highest Value Down

Always start your first IF with the highest (or most extreme) condition and work downward. For grades: check >=85 first, then >=70, then >=55. If you check in the wrong order – for example, >=40 first – students with 90 marks would incorrectly get grade D instead of A. Order matters.

Part 7: The IFS Function – A Cleaner Alternative to Nested IF

If you are using Excel 2019 or Microsoft 365, you have access to a newer function called IFS. It does exactly what Nested IF does – but without all the closing brackets. Many users find it much easier to read and write.

IFS Syntax:
=IFS( condition1, result1, condition2, result2, condition3, result3, ... )

Let us rewrite the 4-level school grade formula using IFS:

School Grades with IFS - Cell C2:
=IFS(B2>=85, "A",
     B2>=70, "B",
     B2>=55, "C",
     B2>=40, "D",
     B2<40,  "F")

-- Single line --
=IFS(B2>=85,"A",B2>=70,"B",B2>=55,"C",B2>=40,"D",B2<40,"F")

Feature	Nested IF	IFS Function
Available in	All Excel versions	Excel 2019, Microsoft 365 only
Closing brackets	Many – easy to lose count	None – much cleaner
Readability	Harder to read with 4+ levels	Much easier to read
Maximum conditions	Up to 64 levels	Up to 127 conditions
Handles ‘else’ (catch-all)	Yes – last value_if_false	Use TRUE as last condition
Best for	Older Excel, simple 2-3 levels	Modern Excel, 4+ conditions

IFS Catch-All Trick: IFS does not have a built-in else. To handle everything that does not match your earlier conditions, use TRUE as the last condition: =IFS(B2>=85,”A”, B2>=70,”B”, B2>=40,”C”, TRUE,”F”). The TRUE condition always matches, so it acts as your default/else.

Part 8: Combining IF with AND / OR – Multiple Conditions

Sometimes a single condition is not enough. You need to check two or more things at once. Excel’s AND and OR functions let you combine multiple conditions inside a single IF.

IF with AND – Both Conditions Must Be True

Bonus for Sales and Attendance: Meera’s company gives a bonus ONLY if the employee has achieved their sales target AND has attendance above 85%. Both conditions must be true at the same time. This is where AND comes in.

IF + AND - Bonus Only When Both Conditions Are Met:
=IF(AND(C2>100000, D2>85), "Bonus Eligible", "Not Eligible")

-- C2 = Monthly Sales, D2 = Attendance %

AND returns TRUE only when ALL conditions inside it are true. If even one condition is false, AND returns FALSE, and the IF goes to value_if_false.

IF with OR – At Least One Condition Must Be True

OR is the opposite – it returns TRUE if ANY one of the conditions is met. Even if the others are false.

IF + OR - Eligible if Either Condition is Met:
=IF(OR(B2="VIP", C2>50000), "Priority Customer", "Regular Customer")

-- B2 = Customer Type, C2 = Order Value

Function	Returns TRUE when…	Use it when…
AND(cond1, cond2, …)	ALL conditions are TRUE	Employee must meet EVERY requirement
OR(cond1, cond2, …)	AT LEAST ONE condition is TRUE	Employee needs to meet ANY requirement
NOT(condition)	The condition is FALSE	You want the opposite of a condition

Part 9: Common IF Formula Mistakes and How to Fix Them

Every Excel beginner makes these mistakes. Knowing them in advance saves you hours of frustration.

Mistake	What You Typed	The Problem	Correct Formula
Text without quotes	=IF(A2>50, Pass, Fail)	Excel cannot find a cell or name called Pass	=IF(A2>50, “Pass”, “Fail”)
Wrong nesting order	=IF(B2>=40,”D”,IF(B2>=85,”A”,…))	Score of 90 returns D instead of A – checks 40 first	Start with highest: IF(B2>=85,”A”,…)
Missing closing bracket	=IF(A2>50,”Yes”,IF(A2>30,”No”)	Excel shows a red error – bracket count mismatch	Count: every IF needs one closing )
Using = for text instead of =”…”	=IF(A2=Yes, …)	Excel looks for a cell named Yes	=IF(A2=”Yes”, …)
Logical test always TRUE	=IF(A2>=0,”OK”,”Error”)	All numbers are >=0, so every row shows OK	Make sure your condition can actually be FALSE
Spaces inside formula	=IF( A2 > 50 , “Yes” , “No” )	Spaces are fine inside IF – not the issue. But avoid spaces inside text: “Yes ” vs “Yes”	=IF(A2>50,”Yes”,”No”)
Comparing text with > or <	=IF(B2>”Paid”,…)	Text cannot be compared with > or <, only with = or <>	=IF(B2=”Paid”,”Done”,”Pending”)

The Most Dangerous Mistake – Wrong Nesting Order

When writing Nested IF for ranges (like grades or salary bands), ALWAYS start from the highest value and work downward. If you check the lowest threshold first, every higher value will match it first and return the wrong result. This is the most common and most damaging mistake beginners make.

Part 10: 10 Pro Tips for Writing Better IF Formulas

• Test on one cell first. Write your IF formula in a single cell and check the result carefully before copying it down to thousands of rows.
• Use the formula bar for long formulas. Click in the formula bar and use Alt+Enter to break a long Nested IF into multiple lines – makes it much easier to read and debug.
• Use named ranges for clarity. Instead of B2>85, you can name column B “AttendancePct” and write AttendancePct>85. Go to Formulas > Name Manager to set this up.
• Return a number, not just text. IF formulas can return numbers that other formulas use: =IF(C2>50000, C2*0.05, 0). This lets you SUM the bonus column directly.
• Leave the last else empty when needed. =IF(B2>50, “Flag”, “”) – the two empty quotes show nothing if condition is false. Cleaner than showing “No Flag” in every row.
• Count your brackets before pressing Enter. One opening bracket for each IF needs one closing bracket. A formula with 3 nested IFs ends with three closing brackets: …”F”)))
• Use IF with TODAY() for date logic. =IF(B2<TODAY(), “Expired”, “Valid”) – checks if a date in B2 is already in the past. Powerful for expiry tracking.
• Combine with IFERROR for clean output. =IFERROR(IF(A2/B2>1,”Over”,”Under”),”Error”) – wraps IF inside IFERROR to catch division-by-zero or other errors gracefully.
• Use IFS when nesting more than 3 levels. If you find yourself writing IF inside IF inside IF inside IF, switch to IFS. It is cleaner, less error-prone, and easier to update.
• Read your formula out loud. Seriously. Read it as a sentence: ‘IF B2 is greater than 90, show Bonus; otherwise IF B2 is greater than or equal to 75, show Normal; otherwise show Warning.’ If it sounds right spoken aloud, it is correct.

Frequently Asked Questions

Summary – Everything You Now Know About IF Formula in Excel

Priya’s Monday – Six Months Later: Six months after that panic-filled Monday morning, Priya is the go-to person in her office for Excel. When her manager sends the attendance list now, she types one IF formula, copies it down, and her entire report is done before her second cup of chai. She is not a programmer. She just learned one formula – and it changed her entire work week.

What You Learned	Formula Structure	Use it When
Basic IF	=IF(condition, true, false)	2 outcomes – Pass/Fail, Yes/No, Bonus/No Bonus
Nested IF	=IF(c1, r1, IF(c2, r2, r3))	3 or more outcomes – Grades, Tiers, Bands
IF + AND	=IF(AND(c1,c2), true, false)	Multiple conditions ALL must be true
IF + OR	=IF(OR(c1,c2), true, false)	Multiple conditions – ANY one must be true
IFS (modern)	=IFS(c1,r1, c2,r2, c3,r3)	4+ outcomes – cleaner than deep nesting
IF + TODAY	=IF(B2<TODAY(),r1,r2)	Date-based decisions – expiry, overdue, valid
IF with calculation	=IF(cond, B2*0.1, 0)	Return a computed number, not just text

The IF formula is your first step into making Excel work for you – instead of you working for Excel. Once this concept is clear, every other advanced Excel formula becomes easier to understand. AND, OR, XLOOKUP, COUNTIF, SUMIF – they all rely on the same logic you just mastered.

Free Excel Tools at ibusinessmotivation.com If manual data tasks are still eating your time – merging files, cleaning duplicates, splitting sheets by region – visit ibusinessmotivation.com for free browser-based tools. No VBA, no installation, no technical knowledge required.