How to Use IF Statements in Excel

Introduction

Excel is packed with powerful tools, but few are as useful as the IF function. With IF statements, you can make your spreadsheets think for themselves by applying logic. Instead of just showing raw data, Excel can test conditions and return different results based on what’s true or false.

Think of IF statements as Excel’s way of saying:

“If something is true, do this. If it’s not true, do something else.”

This article will give you a clear, step-by-step guide to using IF statements in Excel. We’ll start with the basics, then move into advanced variations like nested IFs, combining IF with AND/OR, and even the newer IFS function.

By the end, you’ll understand how to use IF statements to analyse data, solve problems, and automate everyday tasks in Excel.

What is an IF Statement in Excel?

The IF function checks whether a condition is true or false, and then returns one value if it’s true and another if it’s false.

The basic formula looks like this:

=IF(logical_test, value_if_true, value_if_false)


• logical_test: The condition you want to check (e.g., A1>50).
• value_if_true: What Excel should return if the condition is true.
• value_if_false: What Excel should return if the condition is false.

Example: Pass or Fail

If you want Excel to show “Pass” if a student scores 50 or more, and “Fail” if not:

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


If the score in cell A2 is 72, the formula returns “Pass.” If the score is 43, it returns “Fail.”

Step 1: Creating Your First IF Statement

Let’s walk through a simple example.

Imagine you have sales data, and you want to mark whether each salesperson hit their target of £1,000.

1. In column A, list the sales values.
2. In column B, enter the formula:

=IF(A2>=1000,"Target Met","Below Target")


3. Copy the formula down.

Now, each salesperson is automatically labelled depending on their sales figure.

Step 2: Nesting IF Statements

What if you have more than two possible outcomes? That’s where nested IFs come in. Nesting means putting one IF inside another.

Example: Grading System

Suppose you want to assign grades:

• 70 or above = Distinction
• 50 to 69 = Pass
• Below 50 = Fail

Formula:

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


Here’s what happens:

• If A2 is 85, Excel returns “Distinction.”
• If A2 is 60, Excel returns “Pass.”
• If A2 is 40, Excel returns “Fail.”

Step 3: Combining IF with AND/OR

Sometimes you need to test multiple conditions at once. You can use IF with AND or OR functions.

Example: Bonus Eligibility

• Sales must be over £1,000 AND customer satisfaction must be above 80%.

=IF(AND(A2>1000,B2>80),"Bonus","No Bonus")


• Sales must be over £1,000 OR customer satisfaction above 80%.

=IF(OR(A2>1000,B2>80),"Bonus","No Bonus")


Step 4: Using IF with Text

IF statements aren’t just for numbers. You can also test text values.

Example: Department Check

If cell A2 contains “HR”, then return “Human Resources”, otherwise return “Other”:

=IF(A2="HR","Human Resources","Other")


Remember: IF is not case-sensitive. “HR”, “hr”, or “Hr” will all be treated the same.

Step 5: Handling Errors with IFERROR

Sometimes formulas generate errors like #DIV/0!. To make your spreadsheets cleaner, you can combine IF with IFERROR.

Example: Safe Division

=IFERROR(A2/B2,"Check denominator")


If B2 is zero, instead of showing an error, Excel displays “Check denominator.”

Step 6: The IFS Function

In newer versions of Excel (Office 2019 and Microsoft 365), there’s a function called IFS, which is like an improved IF. It checks multiple conditions without needing nested IFs.

Example: Grading with IFS

=IFS(A2>=70,"Distinction",A2>=50,"Pass",A2<50,"Fail")


This formula is easier to read and maintain than nested IFs.

Step 7: Real-World Examples of IF Statements

1. Overtime Calculation

If hours worked > 40, return “Overtime,” else “Standard.”

=IF(A2>40,"Overtime","Standard")


2. Discount Calculation

If purchase amount > £500, apply 10% discount, else no discount.

=IF(A2>500,A2*0.9,A2)


3. Check Expiry Date

If expiry date < today, return “Expired.”

=IF(A2<TODAY(),"Expired","Valid")


Step 8: Best Practices for IF Statements

• Keep formulas simple – If you’re nesting more than three IFs, consider using VLOOKUP or SWITCH instead.
• Use named ranges – They make formulas easier to understand.
• Test your formulas – Start with simple data to confirm the logic.
• Combine with other functions – IF works well with SUM, AVERAGE, and text functions like LEFT or RIGHT.

Step 9: Common Mistakes to Avoid

• Mismatched brackets – Always check you’ve closed each IF with a parenthesis.
• Hardcoding values – Instead of typing numbers into formulas, reference cells.
• Over-nesting – Too many IFs make formulas hard to read. Switch to IFS or lookup functions for complex logic.

Conclusion

The IF function is one of Excel’s most powerful tools because it allows you to apply logic directly inside your spreadsheets. From simple pass/fail checks to advanced formulas with multiple conditions, IF statements can save time, reduce errors, and make your data analysis smarter.

This guide showed you:

• The basic IF structure
• How to nest IFs
• Combining IF with AND/OR
• Using IF with text and errors
• The IFS function for simpler logic

If you’d like to take your Excel skills further, ExperTrain offers expert-led training to match your level:

• Excel Introduction – Build confidence with formulas and essential features.
• Excel Intermediate – Learn Data Validation, advanced formatting, and logical functions like IF.
• Excel Advanced – Master advanced formulas, complex nested IFs, and error handling.
• Excel Power User – Tackle sophisticated data models, advanced logic, and automation.

With the right training, you’ll be able to solve problems faster, design smarter spreadsheets, and work more efficiently.

‍