📥 Master the IF Function with Hands-On Practice
Want to get the most out of this guide? Download the free practice workbook 👉 HERE and follow along step by step.
Basic IF Statement
The IF function follows a simple structure:
= IF ( logical_test , [value_if_TRUE] , [value_if_FALSE] )- logical _test – The condition you want to check (e.g., A1>10).
- value_if_TRUE – The result if the condition is TRUE (e.g., a number, text, or another formula).
- value_if_FALSE (optional) – The result if the condition is FALSE. If omitted, Excel returns FALSE by default.
How IF Works
Think of it as a simple decision-making tool:
📌 IF something is true, THEN return one value, ELSE return another.
Example 1: Simple IF Statement
=IF(C2=1, "Hello!")✅ If C2 equals 1, the formula returns “Hello!”.
❌ If not, it returns FALSE (since no value_if_FALSE is specified).
Example 2: IF Statement with Both Outcomes
If C2 equals 1, the condition is met and the IF formula returns the text “Hello!”. Otherwise, it returns the default value FALSE.
Here is another simple formula with the IF function:
=IF(C3="Hello!", 1, 0)✅ If C3 contains “Hello!”, the formula returns 1.
❌ Otherwise, it returns 0.
This makes the IF function a powerful way to categorize data, set conditions, and automate decisions in Excel.
Let’s explore more advanced uses next!
IF Formula with Logical Tests
The IF function works with logical tests using the following logical operators logical:
| Operator | Meaning |
| = | Equal to |
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
| <> | Not equal to |
Featured Course
Excel Essentials for the Real World
Example 1: Pass or Fail
Let’s say you want to determine if a student passed or failed based on their score.
📌 Condition: If the score is 50 or higher, they pass. Otherwise, they fail.
📌 Score is in cell B1.
=IF(B1>=50, "Pass", "Fail")✅ If B1 is 50 or more, the formula returns “Pass”.
❌ If B1 is below 50, it returns “Fail”.
IF Formula to Return Another Formula
The IF function can also return a calculation instead of just text.
📌 Example: If C1 is under 500, apply an 8% rate. If it’s 500 or more, apply a 10% rate.
=IF(C1 < 500, C1 * 8%, C1 * 10%)✅ If C1 is below 500, it calculates C1 × 8%.
✅ If C1 is 500 or more, it calculates C1 × 10%.
Since IF accepts different data types (numbers, text, dates, or even other formulas), it’s one of Excel’s most versatile functions.
Let’s look at multiple IF statements next!
Nested IF statements
The IF function is flexible and can be combined with multiple IF functions to evaluate multiple conditions. This is known as a nested IF statement.
📌 How It Works:
- Excel checks the first condition.
- If it’s TRUE, it returns a result.
- If it’s FALSE, Excel moves to the next condition.
- This process continues until all conditions are checked.
Nested IF statements help when you need different outcomes based on multiple conditions.
Example: Categorizing Revenue
Let’s say you want to flag revenue values based on thresholds:
- If revenue is over 200, mark it as “flag”.
- If revenue is under 50, mark it as “follow up”.
- Otherwise, leave the cell blank.
= IF(Revenue > 200, "flag", IF(Revenue < 50, "follow up", ""))✅ If Revenue is above 200, the formula returns “flag”.
✅ If Revenue is below 50, it returns “follow up”.
✅ Otherwise, it returns “” (an empty cell).
Why Nested IFs Can Be Tricky
While nested IFs are powerful, they can:
- Get complex and difficult to manage.
- Slow down Excel with large datasets.
- Increase the risk of errors, which are hard to debug.
💡 Pro Tip: Use the IFS Function Instead
The IFS function simplifies multiple conditions without the need for nested IFs. If you’re dealing with several conditions, IFS is a cleaner and more efficient alternative.
👉 Learn more about the IFS function in Excel here.
Now, let’s explore some real-world examples to see these functions in action!
IF Function Examples
Return Text if a Logical Condition is Met
A common use of the IF function in Excel is to return a specific text when a condition is met. Let’s say we want to flag all apps if their revenue exceeds 15,000.
Step 1: Start the Formula
In the first cell where you need the formula (C5 in this example), type:
=IF(Step 2: Define the Logical Test
We want to check if Revenue (B5) is greater than 15,000.
=IF(B5>15000,📌 Don’t forget the comma (,)—it separates the arguments in the function.
💡 Pro Tip: Instead of hardcoding 15,000, reference a cell (e.g., C2) that holds the value. This makes it easy to update later without changing the formula.
=IF(B5>$C$2,✅ Press F4 to make $C$2 an absolute reference, ensuring it doesn’t change when copying the formula down.
Step 3: Specify the Result If TRUE
If the condition is met, we want to display “Good”.
=IF(B5>$C$2, "Good",📌 Text values must be in double quotes ("").
💡 Pro Tip: Instead of typing "Good", use a cell reference (e.g., C3). This makes future updates easier—just change the value in C3, and the formula updates automatically.
=IF(B5>$C$2, $C$3,✅ Press F4 to make $C$3 absolute, so it doesn’t shift when copying the formula.
Step 4: Specify the Result If FALSE
If the revenue is 15,000 or less, we want the cell to stay blank instead of showing “FALSE.”
📌 In Excel, a blank value is written as "".
=IF(B5>$C$2, $C$3, "")✅ Close the parentheses and press ENTER.
Step 5: Copy the Formula Down
📌 Drag the fill handle down to apply the formula to the entire range.
Final Check
Manually verify a few results to ensure the formula works as expected.
This method makes your Excel formulas dynamic and easier to update, saving time in the long run! 🚀
Using IF Function with AND
Let’s flag all apps if their Revenue is above 15,000 but under 20,000.
Step 1. Go to the first cell where we need to insert a formula (cell D5 in this example).
Start the formula with an equal sign (=), add the keyword IF and the opened parentheses to make the formula look like:
=IF(Step 2. The first argument of the IF function is a logical test.
In this example, we want to flag a value in cell B5 (Revenue) if it is above 15000 and less than 20,000.
This is a typical situation when we can use the AND logical function to unite several certain conditions into a single logical test.
AND function can accept up to 255 conditions as its arguments.
Let’s continue the formula and add the second keyword AND with an opened parenthesis.
Once we’ve done that, we can type both conditions for the logical test inside the AND block and close its second parenthesis.
The formula looks like:
=IF(AND(B5>$D$2, B5<$D$3),NOTE: Don’t forget to fix the cell reference to look like $D$2 and $D$3 address.
Press F4 to make an absolute reference. Without that, the formula will return a wrong result after we drag it down to the entire range.
NOTE: Don’t forget to use a comma once this step is ready.
This comma is used by Excel as a delimiter to separate the arguments from each other.
Step 2. The second argument of IF function in Excel, as we remember, is a value_if_TRUE.
Let’s add another cell reference as a second attribute in the formula:
=IF(AND(B5>$D$2, B5<$D$3), $C$3,NOTE: Don’t forget to put a comma after the attribute once this step is ready. Be careful. If you type a text value, you need to put it inside a double quotation mark (“your text”)
Step 3. The third argument of the IF function is a value_if_FALSE.
We can skip this attribute and Excel returns the default value – FALSE.
However, the more aesthetic way to do that is to return nothing (or blank value).
Nothing in Excel is denoted as a double quotation mark without any space:
=IF(AND(B5>$D$2, B5<$D$3), $C$3, "")NOTE: Don’t forget to close the bracket and press ENTER.
Step 4. Drag the formula down to the entire range.
Make a quick check manually to ensure that the formula returns the correct result according to our expectations.
Nested IF Statement
This time we’ll flag all apps as “Good” if their Revenue is above 15,000 and under 20,000. In addition, we’ll flag them as “Exceptional” if their Revenue is greater than or equal to 20,000
Step 1. Go to the first cell where we need to insert a formula (cell E5 in this example).
Start the formula with an equal sign (=), add the keyword IF and the open parentheses to make the formula look like:
=IF(Step 2. The first argument of the IF function in Excel is a logical test.
The first part of the formula in this example is the same as we wrote in the previous example:
=IF(AND(B5>$E$2, B5<$E$3), $C$3,NOTE: Don’t forget to to put a comma after the attribute once this step is ready.
Step 3. Usually, the third argument of the IF function is a value_if_FALSE.
However, this time we need to add the second condition when a value in cell B5 is greater than or equal to 20,000.
We can do that if we use nested IF function. Let’s add another IF statement as a third parameter now:
=IF(AND(B5>$E$2, B5<$E$3), $C$3, IF(Step 4. Add the second logical test, and the second value_if_TRUE.
In our example, the formula must return the “Exceptional” keyword if the value in cell B5 is greater than or equal to 20,000.
Let’s type this:
=IF(AND(B5>$E$2, B5<$E$3), $C$3, IF(B5>=$E$3, "Exceptional",NOTE: If you type a value as a text, you need to put it inside a double quotation mark (“your text”)
Step 5. We need to write a value that the formula will return if the result of both logical tests is FALSE.
In our example, the formula must return the raw Revenue value in this case.
Let’s place this attribute as the third argument in the second IF statement:
=IF(AND(B5>$E$2, B5<$E$3), $C$3, IF(B5>=$E$3, "Exceptional", B5))NOTE: Don’t forget to close the brackets.
We created two IF statements, so we need to close two brackets at the end.
Step 6. Drag the formula down to the entire range.
Make a quick check manually to ensure that the formula returns the correct result according to our expectations.
Using IF Function with OR
Let’s flag all apps if their Revenue is <= 15,000 or >= 20,000
Step 1. Go to the first cell where we need to insert a formula (cell D5 in this example).
Start the formula with an equal sign (=), add the keyword IF and the open parentheses to make the formula look like:
=IF(Step 2. The first argument of the IF function is a logical test.
In this example we want to flag a value in cell B5 (Revenue) if it is below or equal to 15000 OR above or equal to 20,000.
This is a situation when we can use OR logical function that allows the testing of multiple logical conditions and return a true value if ANY of them is true.
Let’s continue the formula and add the second keyword OR with an opened bracket.
Once we’ve done that, we can type both conditions for the logical test inside OR() block and close its second parentheses.
The formula looks like:
=IF(OR(B5>=$F$2, B5=<$F$3),NOTE: Press the F4 button on the keyboard to make an absolute reference.
Step 3. The second argument of the IF function, as we remember, is a value_if_TRUE.
Let’s add this attribute in the formula:
=IF(AND(B5>$D$2, B5<$D$3), "Flag",NOTE: If you type a text value, you need to put it inside a double quotation mark (“your text”)
Step 4. The third argument of the IF function is a value_if_FALSE.
Let’s add a double quotation mark without any space to return a blank value:
=IF(AND(B5>$D$2, B5<$D$3), "Flag", "")NOTE: Don’t forget to close the bracket and press ENTER.
Step 5. Drag the formula down to the entire range.
Quickly check to ensure that the formula returns the correct result.
Using Other Formulas Inside the Logical Functions
Let’s calculate the difference between Actual Revenue and Budget Revenue and flag those cases when the difference is larger than +/- 10%.
Step 1. The easiest way to start building such a complex formula is to calculate the core value (this is the +/-10% threshold value in our case).
Step 2. Go to the first cell where we need to insert a formula (cell D23 in this example).
Start the formula with an equal sign (=), add the formula to calculate the difference (Actual divided by Budget minus one:
=B23/C23 - 1Step 3. Let’s add the IF statement and the appropriate logical test.
This is a situation when we can use the OR logical function as we need to flag a value if it is greater than +10% or lower than -10%:
=IF(OR(B23/C23 - 1>10%, B23/C23 - 1<-10%),Step 4. The second argument of the IF function in Excel, as we remember, is a value_if_TRUE.
In this example we need to return the exact deviation, so let’s do that, by simply copying the initial formula.
=IF(OR(B23/C23 - 1>10%, B23/C23 - 1<-10%), B23/C23 - 1,Step 5. The third argument of the IF function is a value_if_FALSE.
Let’s add a double quotation mark without any space to return a blank value:
=IF(OR(B23/C23 - 1>10%, B23/C23 - 1<-10%), B23/C23 - 1,"")NOTE: Don’t forget to close the bracket and press ENTER.
Step 6. Drag the formula down to the entire range.
Quickly check to ensure that the formula returns the correct result.
Using Icons as the Result of the IF Function in Excel
Let’s mark the positive deviation with the up arrow and the negative deviation with the down arrow.
Step 1. We’ll continue the previous example.
Let’s say we want to mark all positive deviations with a symbol that looks like an up arrow and all negative deviations with a symbol that looks like a down arrow.
First of all, a few preparations.
Let’s go to the cells above the test range (A20 and A21 cells in this example).
Feel free to select any other cells if you prefer.
Step 2. Insert the symbols that will indicate all positive and negative deviations.
To do this, we need to go on the INSERT tab on the Excel Ribbon and follow the path: INSERT > Symbol > Arial > Geometric Shapes
Click Insert once you have selected a shape.
Go to another cell below and insert the second arrow in the same way.
Step 3. Go to the first cell in the range where we want to add these visual indicators.
Replicate the IF function with the cell references containing the arrows as the value_if_TRUE and value_if_FALSE attributes.
=IF(B23/C23 - 1>10%, $A$20, IF(B23/C23 - 1<-10%, $A$21, ""))NOTE: Don’t forget to close the bracket and press ENTER.
Step 4. Drag the formula down to the entire range.
Quickly check that the formula returns the correct result.
Workbook for Download
Feel free to Download the Workbook HERE.
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
