If a spreadsheet needs to make a decision based on a value, the IF function is usually one of the first Excel functions to try. It lets you test a condition and return one result when the condition is true and another when it is false.
That sounds simple, but most IF problems come from choosing the wrong comparison, mixing text and numbers, or nesting too many conditions. This guide focuses on the situations people actually run into when building a working spreadsheet.
What the IF function does
In plain English, IF asks: Is this condition true? If yes, return one value. If not, return another.
For example, if A2 contains a student's score and you want a pass/fail result:
A score of 50 or more returns Pass; anything below 50 returns Fail.
Use IF to return Yes or No
This is one of the most useful beginner patterns. Suppose B2 contains an invoice amount and you want to flag invoices above $1,000.
You can replace the text with a cell reference, another calculation, or a number if the next step in your workbook needs a numeric result.
Use IF for pass or fail
For marks, quality checks or other threshold-based decisions, compare the cell with the required minimum.
The important part is the comparison operator. >= includes the threshold itself. Using > would make a score of exactly 40 fail.
Use IF for conditional calculations
IF can return a calculation rather than a word. For example, suppose B2 is a sales amount and the commission rate is 5% only when sales reach $10,000.
This returns the commission amount when the threshold is met and zero otherwise.
When a rule becomes more complicated, keep the business rule visible in the formula rather than hiding important assumptions in unexplained constants. If the threshold or rate may change, storing it in a separate cell makes the workbook easier to maintain.
Use IF with text
IF can compare text as well as numbers. Suppose A2 contains an order status:
For text comparisons, put literal text in quotation marks. A missing pair of quotation marks is a common reason for a formula error.
Use IF with dates
Excel stores dates as values, so you can compare them in IF formulas. Suppose A2 contains a due date and B2 contains today's date.
If you want the formula to always use the current date, you can use TODAY():
Be careful with blank date cells. A blank may be treated like zero in a comparison, which can produce a misleading result. If blanks are possible, test for them explicitly.
Use IF with AND for multiple conditions
Sometimes all conditions must be true. For example, an employee receives a bonus only when sales are at least $10,000 and the quality score is at least 90.
AND returns TRUE only when every condition supplied to it is true.
Use IF with OR when any condition is enough
If either of two conditions should trigger the result, use OR.
This is useful when different events lead to the same action.
Use IFERROR when the underlying calculation can fail
IF and IFERROR solve different problems. IF tests a condition you define. IFERROR catches an error returned by a calculation.
This is useful when B2 may be zero or blank. Do not use IFERROR simply to hide every problem in a workbook; first make sure the underlying formula is correct.
Multiple conditions with nested IF
You can put another IF inside an IF when several outcomes are required. For example:
This works, but long nested formulas become difficult to read and maintain. When there are many categories, consider whether a lookup table, IFS, or another design would make the rule clearer.
A practical example: classify order values
| Order value | Rule | Result |
|---|---|---|
| $250 | Under $500 | Standard |
| $750 | $500 or more | Priority |
| $2,000 | $500 or more | Priority |
If the order value is in B2, use:
Copy the formula down the column. The cell reference changes with each row, so each order is evaluated independently.
Common IF mistakes
- Wrong comparison operator: Check whether the rule needs >, >=, <, <=, = or <>.
- Text without quotation marks: Literal text such as Paid or Pass needs quotes.
- Numbers stored as text: A value that looks like 100 may not behave like the number 100 if it is stored as text.
- Blank cells: Decide what a blank should mean before writing the rule.
- Hidden business rules: Hard-coded thresholds and rates are harder to maintain when they change frequently.
- Overly long nesting: A complicated decision tree may be easier to manage with a lookup table or a different function.
IF formula checklist
- Write the condition in plain English before writing the formula.
- Confirm the comparison operator includes or excludes the threshold as intended.
- Put literal text in quotation marks.
- Test the formula with a value below, at and above the boundary.
- Test blank and unexpected inputs if your sheet can contain them.
- Keep changing thresholds and rates in cells when practical.
When to use something other than IF
IF is excellent for a small number of decisions. If you are repeatedly matching categories, returning values from a table, or handling a large decision matrix, a lookup or another purpose-built function may produce a cleaner workbook. For modern Excel, functions such as XLOOKUP, IFS and related functions can reduce complicated nested logic depending on the task.
For more lookup work, see Tervilo's XLOOKUP guide and VLOOKUP guide. You can also browse the Excel Guides collection.
Quick answer
Use =IF(condition, result_if_true, result_if_false). Start by defining the rule in plain language, then test the boundary values. For multiple conditions, combine IF with AND or OR, and use IFERROR when a separate calculation may return an error.
Related Excel guide: How to Use Conditional Formatting in Excel.