Excel IF Formula

IF Formula in Excel: Syntax, Examples, and Uses

The IF function in Excel checks a condition and returns one result if the condition is true and another result if it is false. It helps you make decisions automatically based on the values in your worksheet.

For example, you can use the IF formula to display Pass or Fail based on marks, calculate employee bonuses, identify overdue payments, or leave a cell blank when input data is missing.

This article explains how to use the IF formula in Excel with syntax, examples, outputs, and explanations. It also covers nested IF, IF with AND and OR, the IFS function, and common mistakes.

IF Formula Syntax

The IF function uses the following syntax:

=IF(logical_test, value_if_true, value_if_false)

Only the condition and the two possible results are required. The results can be text, numbers, formulas, or cell references.

Example:

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

If B2 contains 72, Excel returns Pass. If B2 contains 35, it returns Fail.

Important: Put text results inside double quotation marks, such as "Pass". Numbers and cell references do not need quotation marks.

Comparison Operators Used in IF Conditions

Comparison operators help Excel evaluate a condition and determine whether it is true or false.

For example, B2>=40 is true when B2 contains 40 or any number greater than 40. It is false when B2 contains a number below 40.

Sample Data

The following student table is used in the examples. It is stored in cells A1.

You can enter the formulas in a new column and copy them down to apply the same condition to every student.

Example 1: Use IF to Check Pass or Fail

Suppose a student needs at least 40 marks to pass an exam. You can use the IF function to display the result automatically.

Formula:

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

Output:

Explanation:

Excel checks whether the marks in B2 are at least 40. If the condition is true, it returns Pass; otherwise, it returns Fail. Rahul passes with exactly 40 marks because the formula uses the >= operator.

This formula is useful for exam results, eligibility checks, and other tasks that require two possible outcomes.

Example 2: Use Nested IF to Assign Grades

Sometimes, a formula needs to return more than two possible results. For example, you may want to assign grades according to a student's marks.

A nested IF places one IF function inside another to check multiple conditions.

Formula:

=IF(B2>=80,"A",IF(B2>=60,"B",IF(B2>=40,"C","Fail")))

Output:

Explanation:

Excel checks each condition from left to right and returns the result for the first condition that is true.

For Amit, the condition B2>=80 is false because he scored 72. Excel then checks B2>=60, which is true, and returns grade B.

The order of conditions matters. Start with the highest grade threshold so that students receive the correct grade.

Nested IF is useful for grading systems, performance ratings, commission levels, and salary categories.

Example 3: Use IF with AND

The AND function checks whether all specified conditions are true. When it is used inside IF, Excel returns one result if every condition is satisfied and another result if at least one condition is false.

Suppose a student qualifies for a scholarship only when their marks are at least 60 and their attendance is at least 90%.

Formula:

=IF(AND(B2>=60,C2>=90),"Eligible","Not eligible")

Output:

Explanation:

Amit and Priya meet both requirements, so Excel returns Eligible. Rahul has sufficient attendance but does not have the required marks. Because both conditions must be true, Excel returns Not eligible.

Use IF with AND when a decision depends on multiple requirements, such as employee eligibility, minimum sales and attendance, or loan application criteria.

Example 4: Use IF with OR

The OR function checks whether at least one condition is true. When OR is used inside IF, Excel returns one result if any condition is true and another result only when all the conditions are false.

Suppose a student needs a review if their marks are below 40 or their attendance is below 90%.

Formula:

=IF(OR(B2<40,C2<90),"Review","OK")

Output:

Explanation:

Neha receives Review because both her marks and attendance are below the required levels. Only one condition needs to be true for OR to return TRUE.

Rahul receives OK because his marks are not below 40 and his attendance is above 90%.

Use IF with OR when any one of several conditions is enough to trigger an action, such as flagging low marks or poor attendance.

Example 5: Use IF to Handle Blank Cells

When you copy a formula down a worksheet, empty rows may display unwanted results. You can use IF to keep the result blank until the required input is entered.

Suppose cell B2 contains a number and you want to calculate twice that value.

Formula:

=IF(B2="","",B2*2)

Output:

Explanation:

Excel first checks whether B2 is empty. If it is empty, the formula returns "", which displays as a blank cell. Otherwise, it multiplies the value in B2 by 2.

This formula is useful in reports, attendance sheets, salary sheets, and other worksheets where users enter data gradually.

Example 6: Use IF with Text Values

The IF function can also check text values, such as department names, employee types, or payment statuses.

Suppose employees in the IT department receive a bonus equal to 10% of their salary, while employees in other departments receive no bonus.

Formula:

=IF(B2="IT",C2*10%,0)

Sample Data and Output:

Explanation:

Excel checks whether B2 contains IT. If it does, Excel calculates 10% of the salary in C2. Otherwise, it returns 0.

Excel's standard text comparisons are not case-sensitive, so IT and it match. However, extra spaces can prevent an exact text match.

This formula is useful for employee bonuses, department-based calculations, discounts, and commission rules.

Example 7: Use the IFS Function as an Alternative to Nested IF

The IFS function checks multiple conditions without requiring an IF function inside another IF function. It is useful when a formula needs to return several possible results.

Using the same grading rules, you can write:

Formula:

=IFS(B2>=80,"A",B2>=60,"B",B2>=40,"C",TRUE,"Fail")

Output for Amit (72 marks):

B

Explanation:

The IFS function checks each condition in order and returns the result associated with the first true condition. Amit's marks are below 80 but at least 60, so Excel returns B.

The final pair, TRUE,"Fail", acts as the default result when none of the earlier conditions is true. Without a matching condition or a default result, IFS returns #N/A.

IFS is available in Excel 2019 and later versions, including Excel 2021 and Microsoft 365.

You can explore more Excel formulas and interview topics in Excel Interview Questions.

IF vs. IFS in Excel

Both functions can check conditions and return results, but they differ in how multiple conditions are written.

Use IF for a simple two-outcome decision. Use nested IF or IFS when you need to check several conditions.

Common Mistakes in the IF Formula

• Missing quotation marks: Write "Pass" rather than Pass when returning text. Missing quotation marks can cause a #NAME? error.
• Using the wrong comparison operator: B2>40 excludes 40, while B2>=40 includes it.
• Checking conditions in the wrong order: In nested IF formulas, start with the highest or most specific threshold to avoid incorrect results.
• Missing closing parentheses: Each IF function needs its own closing parenthesis.
• Forgetting a default result in IFS: Add a final condition such as TRUE,"Fail" when you need a result for all remaining cases.
• Unexpected blank-cell results: Check whether the formula should return 0, an empty string, or another value when the input cell is blank.

Important Points About the IF Formula

• The IF function returns one result when a condition is true and another when it is false.
• Use comparison operators such as =, <>, >, <, >=, and <= to define conditions.
• Use AND when all conditions must be true and OR when at least one condition must be true.
• Nested IF can handle multiple outcomes, while IFS offers a more readable alternative for multiple conditions.
• Put text values inside double quotation marks.
• Use IF(B2="","",...) when you want formulas to leave results blank until input data is available.
• Check the order of conditions and the closing parentheses when troubleshooting formula errors.