Tutorials›Excel›IF & IFS Functions
🟢 Free Demo
Excel TutorialTopic 3 of 31

IF & IFS Functions

Use IF and IFS to make Excel decisions based on conditions — a core skill for every analyst.

✅ What You Will Learn

›How to write a basic IF formula with a true and false result
›How to nest multiple IF statements for complex logic
›How IFS simplifies multiple conditions without deep nesting
›How to combine IF with AND and OR
›Real analysis use cases for conditional logic

The IF function is the most important logical function in Excel. It tests a condition and returns one value if the condition is true, and a different value if it is false. Almost every dashboard, report, or analysis uses IF in some form.

The basic structure is: =IF(condition, value_if_true, value_if_false). The condition can be any comparison — greater than, less than, equal to, not equal to. The values can be numbers, text, another formula, or even another IF (called nesting).

Excel 2019 and later introduced the IFS function, which evaluates multiple conditions in sequence without the visual complexity of deeply nested IFs. For anything beyond two or three conditions, IFS is the cleaner choice.

📋 Employee performance data

NameScoreAttendance %GradeBonus Eligible
Anjali Singh8896=IFS formula=IF formula
Rahul Mehta7288
Priya Kapoor9199
Amit Sharma5575
Neha Verma7891

Syntax

EXCEL SYNTAX
=IF(logical_test, value_if_true, value_if_false)

=IFS(condition1, result1, condition2, result2, ..., TRUE, default)

=IF(AND(A1>0, B1>0), "Both positive", "Not both positive")
=IF(OR(A1="Yes", B1="Yes"), "At least one Yes", "No")

Examples

Example 1Basic IF — pass or fail based on score
=IF(B2>=60, "Pass", "Fail")
OUTPUT
Anjali: Pass  |  Rahul: Pass  |  Amit: Fail
💡

The condition B2>=60 is tested. If true, "Pass" is returned. If false, "Fail". Text values in formulas must be in double quotes.

Example 2IFS — assign letter grades without nested IFs
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", TRUE, "F")
OUTPUT
Anjali: B  |  Priya: A  |  Rahul: C  |  Amit: F
💡

IFS tests conditions top to bottom and returns the first match. The final TRUE,"F" is the catch-all default — always include it to avoid #N/A errors.

Example 3IF with AND — bonus requires both high score AND good attendance
=IF(AND(B2>=75, C2>=90), "Eligible", "Not Eligible")
OUTPUT
Anjali: Eligible  |  Rahul: Not Eligible  |  Priya: Eligible
💡

AND returns TRUE only when every condition is true. OR returns TRUE when at least one condition is true. Both can take up to 255 conditions.

📌 Key Points to Remember

  • ✓Text values inside formulas must be in double quotes — =IF(A1="Yes", ...) not =IF(A1=Yes, ...)
  • ✓IFS is available in Excel 2019, Microsoft 365, and Excel Online — not in older versions
  • ✓Nested IFs work but become hard to read beyond 3 levels — switch to IFS or SWITCH instead
  • ✓IF can return another formula: =IF(A1>0, SUM(B1:B10), 0)
  • ✓Leave the false argument blank to return FALSE, or use "" to return an empty cell

🏢 Real-World Application

HR analysts use IFS to automatically categorise hundreds of employees into performance bands (Exceeds / Meets / Below expectations) based on score ranges. Finance teams use IF(AND(...)) to flag transactions that exceed both a value threshold and a frequency threshold for fraud review. These conditional formulas run on thousands of rows in seconds.

⚠️ Common Mistakes to Avoid

WRONGWriting =IF(A1=Yes, ...) without quotes around Yes
FIXText must be quoted: =IF(A1="Yes", ...). Without quotes Excel looks for a named range called Yes and returns a #NAME? error.
WRONGForgetting the TRUE default at the end of IFS
FIXAlways end IFS with TRUE, "default value". Without it, IFS returns #N/A when none of the conditions match.
WRONGUsing = instead of >= or <= for range checks
FIXIf you want scores of 80 and above to be "B", use B2>=80 not B2=80. The = operator only matches the exact value 80.
✏️Test Yourself

What does =IF(50>60, "High", "Low") return?

❓ Frequently Asked Questions

How many levels of nested IF can Excel handle?

Excel allows up to 64 levels of nesting, but anything beyond 3 becomes hard to read and debug. Use IFS or SWITCH for multiple conditions.

What is the difference between IF and IFS?

IF tests a single condition (with optional nesting). IFS tests multiple conditions in sequence and is much cleaner when you have more than two outcomes.

Can I use IF to return a colour?

No — IF returns values, not formats. To colour cells based on conditions, use Conditional Formatting (covered in a later topic).

✏️ Practice Exercise

In a dataset of 10 student scores (column A), write an IFS formula in column B to assign grades: A for 90+, B for 80–89, C for 70–79, D for 60–69, and F below 60. Then in column C, write an IF(AND()) formula to flag students who scored 75+ AND have attendance above 85% (column D) as "Eligible for Scholarship".

← PreviousCell ReferencesNext →VLOOKUP & XLOOKUP
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.