Excel Logical Functions: IF, AND, OR, and More

Every time you open a spreadsheet and wonder “how do I make Excel decide something for me?” – the answer lies in logical functions. These are built-in Excel tools that evaluate conditions and return results based on whether those conditions are true or false. Whether you’re checking if a student passed an exam, validating employee eligibility for a bonus, or filtering orders by status, logical functions like IF, AND, and OR are what power those decisions. Understanding them is one of the most practical skills you can build in Excel.

What are logical functions in Excel? 🔗

In Excel, logical functions are functions that test conditions and return either TRUE or FALSE – or a value you specify based on that result. They introduce decision-making into your formulas, allowing spreadsheets to behave differently depending on the data they contain. The core logical functions in Excel are IF, AND, OR, NOT, and a few extended ones like IFS and XOR. Each serves a distinct role, but they’re most powerful when used together.

At their core, all logical functions work with comparison operators – symbols that compare values. The most common ones are:

• = (equal to)
• > (greater than)
• < (less than)
• >= (greater than or equal to)
• <= (less than or equal to)
• <> (not equal to)

These operators form the conditions that logical functions evaluate.

Conditional statements with IF 🔗

The IF function is the most widely used logical function in Excel. As Microsoft’s official documentation explains, it lets you make a logical comparison between a value and what you expect – returning one result if the condition is true, and another if it’s false.

Syntax of the IF function 🔗

The structure is straightforward:

=IF(logical_test, value_if_true, value_if_false)

• logical_test – the condition you want to evaluate (e.g., A2>=60)
• value_if_true – what to return if the condition is met
• value_if_false – what to return if the condition is not met

A basic IF example 🔗

Suppose you have student scores in column B and want to mark each as “Pass” or “Fail” based on whether they scored 60 or above. The formula in column C would be:

=IF(B2>=60, "Pass", "Fail")

If B2 contains 75, Excel returns Pass. If B2 contains 45, it returns Fail. That’s decision-making automated in a single formula.

Nested IF for multiple outcomes 🔗

What if you need more than two outcomes? That’s where nested IF comes in – placing one IF function inside another. For example, to assign letter grades:

=IF(B2>=90, "A", IF(B2>=75, "B", IF(B2>=60, "C", "F")))

Excel evaluates from left to right. If the first condition is true, it stops and returns “A”. If not, it moves to the next IF, and so on. While this works, deeply nested IFs can become hard to read – which is exactly why Excel introduced the IFS function in Excel 2016, allowing you to list multiple conditions more cleanly without stacking brackets.

=IFS(B2>=90, "A", B2>=75, "B", B2>=60, "C", B2<60, "F")

Using AND and OR for complex conditions 🔗

The IF function on its own can only handle one condition at a time. When you need to evaluate multiple conditions simultaneously, you bring in AND and OR. As Microsoft Support outlines, these functions can support up to 255 individual conditions, though best practice is to keep them manageable.

The AND function 🔗

The AND function returns TRUE only when all of its conditions are true. If even one condition fails, it returns FALSE.

Syntax: =AND(logical1, logical2, ...)

On its own, AND just produces TRUE or FALSE. Its real power shows up inside an IF formula:

=IF(AND(condition1, condition2), value_if_true, value_if_false)

Example: An employee qualifies for a performance bonus only if they have worked for more than 2 years and achieved a sales target above $50,000:

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

Both conditions must be satisfied. If an employee has 3 years of experience but only $40,000 in sales, the formula returns No Bonus.

The OR function 🔗

The OR function is less strict – it returns TRUE if at least one of the conditions is true, and returns FALSE only when all conditions are false. According to AbleBits, the OR function follows the same syntax as AND:

Syntax: =OR(logical1, logical2, ...)

Used inside IF:

=IF(OR(B2="delivered", B2="paid"), "Closed", "Open")

This marks an order as “Closed” if its status is either “delivered” or “paid” – either one is enough to trigger the result.

Combining AND and OR in a single formula 🔗

You can nest AND and OR together for layered logic. For instance, to flag customers eligible for a special offer if they are either a premium member or have spent over $1,000, and they have made a purchase in the last 30 days:

=IF(AND(OR(B2="Premium", C2>1000), D2<=30), "Eligible", "Not Eligible")

The OR handles the membership/spending check, while the AND wraps it with the recency condition. Both parts must be satisfied for the offer to apply.

The NOT function 🔗

The NOT function simply reverses a logical result – it turns TRUE into FALSE, and FALSE into TRUE. It’s useful when you want to act on the absence of a condition rather than its presence.

Syntax: =NOT(logical)

Example: Flag all students who did not pass:

=IF(NOT(B2>=60), "Review Required", "OK")

This is functionally equivalent to =IF(B2<60, ...), but NOT can make complex formulas more readable when the condition itself is already a function.

Practical applications of logical functions 🔗

Logical functions aren’t just academic exercises – they handle real-world decision automation inside spreadsheets every day. Here are some concrete use cases.

Voter eligibility checker 🔗

One of the clearest illustrations of AND logic is modeling voter eligibility. In many countries, voting requires meeting multiple criteria simultaneously – typically a minimum age and citizenship status.

=IF(AND(B2>=18, C2="Citizen"), "Eligible to Vote", "Not Eligible")

Here, column B holds the person’s age and column C holds their citizenship status. Only individuals who are 18 or older and are citizens will be marked “Eligible to Vote.” If either condition fails, they are “Not Eligible.” This is a textbook example of AND logic – every criterion must be satisfied.

Loan application screening 🔗

A bank wants to pre-approve loan applicants who have a credit score above 700 or an existing account with the bank for over 5 years:

=IF(OR(B2>700, C2>5), "Pre-Approved", "Review Needed")

Either condition alone is enough for pre-approval – this is OR logic in action.

Exam pass/fail with attendance 🔗

A student passes only if their exam score is 50 or above and their attendance is at least 75%:

=IF(AND(B2>=50, C2>=75), "Pass", "Fail")

A student scoring 80 on the exam but with 60% attendance would still fail – both conditions are mandatory.

Product discount automation 🔗

Retailers often apply discounts based on multiple criteria. If a customer has spent over $200 and is a loyalty member, give them a 15% discount; otherwise, no discount:

=IF(AND(B2>200, C2="Yes"), B2*0.85, B2)

This formula directly calculates the discounted price in the true branch and returns the original price in the false branch – no manual calculations needed.

Beyond the basics: IFS, IFERROR, and XOR 🔗

As you grow more comfortable with IF, AND, and OR, a few additional logical functions become useful tools in your arsenal.

IFS function 🔗

Available in Excel 2016 and later, the IFS function tests multiple conditions sequentially and returns the result for the first one that evaluates to TRUE. It eliminates the need for deeply nested IF statements:

=IFS(B2>=90, "A", B2>=75, "B", B2>=60, "C", B2<60, "F")

IFERROR function 🔗

The IFERROR function catches formula errors and replaces them with a value you define. It’s particularly useful when working with division or lookups that might produce errors:

=IFERROR(A2/B2, "Division Error")

If B2 is zero, instead of displaying the ugly #DIV/0! error, the cell shows “Division Error.” This keeps your spreadsheet clean and professional.

XOR function 🔗

The XOR (exclusive OR) function, introduced in Excel 2013, returns TRUE when an odd number of its conditions are true – meaning it returns TRUE if exactly one condition is true, and FALSE if both or neither are true. As How-To Geek explains, it’s less common than OR but useful in toggle-style logic where exactly one option should be active at a time.

Tips for writing effective logical formulas 🔗

Writing clean logical formulas takes a little practice. Here are a few guidelines that help:

• Test conditions individually first. Before nesting AND or OR inside IF, test each condition on its own in a blank cell to confirm it returns TRUE or FALSE as expected.
• Keep nesting shallow. According to AbleBits, complex nested formulas become difficult to maintain. Use IFS when you have more than three or four branches.
• Use text values in quotes. When comparing text, always wrap the text in double quotes – "Yes", "Citizen", "Paid". Numbers do not need quotes.
• Remember OR is case-insensitive. Excel’s OR (and AND) functions don’t distinguish between uppercase and lowercase. “delivered” and “DELIVERED” are treated as identical.
• Close all parentheses. Every open parenthesis needs a matching close. Excel will often alert you to mismatches, but building the habit saves debugging time.

How logical functions connect to real business decisions 🔗

Beyond individual formulas, logical functions are foundational to how businesses use Excel as a decision-support tool. HR teams use them to automate eligibility checks for leave or benefits. Finance departments use them to flag transactions that exceed thresholds. Sales teams use them to calculate tiered commissions automatically. Once you understand how conditions chain together, you can model almost any rule-based process inside a spreadsheet – without writing a single line of code.

The combination of IF, AND, and OR essentially lets you encode real-world policies directly into your data. A policy that says “employees qualify for remote work if they are full-time and have completed onboarding” translates directly into =IF(AND(B2="Full-Time", C2="Complete"), "Eligible", "Not Eligible"). The formula is the policy.

What do you think? Consider a decision-making process you handle manually – like approving requests or categorizing data. How would you translate its rules into an IF formula using AND or OR? And if you needed to handle five or more different outcomes, would you use nested IFs or the IFS function – and what would drive that choice?

References

1. https://www.howtogeek.com/401998/how-to-use-logical-functions-in-excel-if-and-or-xor-not/
2. https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
3. https://support.microsoft.com/en-us/office/using-if-with-and-or-and-not-functions-in-excel-d895f58c-b36c-419e-b1f2-5c193a236d97
4. https://www.ablebits.com/office-addins-blog/excel-and-or-xor-not-functions/
5. https://www.contextures.com/excellogicalfunctions.html
6. https://www.ablebits.com/office-addins-blog/excel-if-or-statement/