IF Function in Excel
- logical_test: What to check. The mathematical condition you want to test (for example, is A1 greater than 10?). Note: See Common Criteria Patterns in IF below. Required.
- value_if_true: What to show if true. The text, number, or formula Excel will display if your test is correct. Optional.
- value_if_false: What to show if false. The text, number, or formula Excel will display if your test is incorrect. Optional.
What is the IF function in Excel?
The IF function in Excel is used to run a logical test and show one result if the test is true, and a different result if the test is false.
Learn with Example: IF
- Step 1: Understand what we need We need to check if a department spent more money than their budget allows. Because there are only two possible answers ("Over Budget" or "Within Budget"), we can use a single IF formula.
- Step 2: Write the logical test We check if the actual spending in cell C3 is greater than the budget in cell B3. Type: =IF(C3>B3, logical_test The yes-or-no question Excel checks. Here, is the actual spending greater than the budget?
- Step 3: Set the answer when the test is true If they did spend too much, we want to show "Over Budget". We must use quotation marks for all text. Type: =IF(C3>B3, "Over Budget", value_if_true What to do if the test is TRUE. Here, we show they spent too much.
- Step 4: Set the answer when the test is false If they did not spend too much, we want to show "Within Budget". We close the formula with a parenthesis. Type: =IF(C3>B3, "Over Budget", "Within Budget") value_if_false What to do if the test is FALSE. Here, we show they stayed within budget.
- Step 5: Check your final result Press Enter to see the final status for the Marketing department. Over Budget
- Step 6: Copy down the formula You do not need to type the formula again. Click on the completed answer. Then, click the small square in the bottom right corner of the cell and drag it down. Excel will automatically calculate the status for the rest of the departments.
You have a list of department budgets. How do you automatically show "Over Budget" if a team spent too much money, and "Within Budget" if they did not?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department Budget | |||
| 2 | Department | Budget | Actual Spend | Status |
| 3 | Marketing | 8000 | 9800 | |
| 4 | Finance | 12000 | 11200 | |
| 5 | Operations | 6500 | 6500 | |
| 6 | IT Support | 15000 | 16100 | |
| 7 | ||||
| 8 | ||||
| 9 | ||||
| 10 | ||||
When to use IF
Here are the most common situations where you should use the IF formula:
- Checking if a target is met: When you want to see if an employee hit their sales goal and automatically show the word "Bonus" or "No Bonus".
- Marking overdue items: When you need to compare a due date against today's date to mark an invoice as "Overdue".
- Categorizing data into two groups: When you have a list of test scores and need to label them all as either "Pass" or "Fail".
When not to use IF
Because this formula is designed for simple two-choice logic, you should use a different method for these specific situations:
- When you have more than two possible outcomes: If you are building a grading scale (like A, B, C, D, and F), nesting many IF functions together is very difficult to read. Instead, use the IFS function.
- When you need to check multiple rules at the exact same time: The standard IF function only tests one single rule. To test many rules at the same time, you must place the AND or OR function inside your IF formula.
IF vs IFS Comparison
If you need to test rules, you must choose between the older IF formula and the newer IFS formula.
| Feature | IF Function | IFS Function |
|---|---|---|
| Best Used For | Testing a single rule with two possible answers. | Testing multiple rules with many possible answers. |
| Limit on outcomes | Yes (Limit: 2 outcomes per IF). | No (Limit: 127 conditions). |
| Requires nesting for complex rules? | Yes. | No. |
| Excel Version | All versions. | Excel 2019 and newer. |
| Ease of Use | Beginner. | Beginner. |
Bottom Line: Use the IF function when you only have two possible answers (like Pass or Fail). Use the IFS function when you have a long grading scale with many answers (like A, B, C, and D).
Common Criteria Patterns in IF
You must use math symbols to build the rule inside the logical_test.
| Criteria Pattern Name | Formula Example | Explanation |
|---|---|---|
| Equal to specific text | =IF(A1="Apple", "Yes", "No") | Checks if cell A1 contains exactly the word Apple. |
| Greater than a number | =IF(A1>100, "High", "Low") | Checks if the number in cell A1 is larger than 100. |
| Not equal to specific text | =IF(A1<>"Done", "Pending", "Complete") | Checks if cell A1 does not contain the word Done. |
For a complete list of patterns (including dates, blanks, and cell references), see our full Excel Criteria and Wildcards reference page.
Common Mistakes When Using IF
Frequently Asked Questions about IF
Quick answers about IF
Can I use multiple conditions at the same time?
Yes. You can place an AND or OR function inside the logical_test to check multiple rules before giving an answer.
How do I leave a cell completely blank?
You must type two quotation marks with absolutely nothing in between ("") as your value_if_true or value_if_false.
Can I put an IF function inside another IF function?
Yes. This is called a "nested IF." You place a completely new IF formula inside the value_if_false to check a second rule. However, the newer IFS function is much easier to use for this situation.
Is the text in the logical_test case-sensitive?
No. The IF function ignores capitalization. Testing if a cell equals "apple" will perfectly match "APPLE" or "Apple".
IF Practice Workbook
Download ready-to-use Excel files and practice IF hands-on
- All 5 worked example tables (finance, sales, HR, ops, data)
- Blank cells to write each IF yourself, including one nested IF
- An answer key to check your results