The IF function checks a condition and returns one value if the condition is true and a different value if it is false. It is Excel's basic decision-making tool, letting you build simple logic into formulas without programming.
=IF(logical_test, value_if_true, [value_if_false])What does IF do?
The IF function is Excel's way of making a decision inside a formula. You give it a test, such as whether a number is above a target or whether a cell contains a certain word, and IF returns one result when the test is true and another when it is false. The syntax is =IF(logical_test, value_if_true, [value_if_false]), where only the first two arguments are required and the third is optional.
Why does this matter? Most real spreadsheets are not just sums and averages; they need to react to the data. A sales report might need to label each row as Above target or Below target, a student list might need Pass or Fail, and an invoice might need a discount applied only when the order is large enough. IF lets you write those rules once and fill them down a column, so the spreadsheet updates automatically when the data changes.
A common real situation is a monthly expenses sheet. You have a budget for each category and the actual amount spent. With IF you can add a Status column that says Over budget when spending exceeds the budget and Within budget otherwise, and then filter or colour the results. That turns a plain list of numbers into a report that answers a question at a glance.
When to use IF
- Label rows as Pass or Fail based on a score or threshold.
- Flag invoices or orders as Overdue when the due date has passed.
- Apply a discount only when the order total is above a certain amount.
- Show Yes or No for whether stock is below a reorder level.
- Mark sales rows as Above target or Below target for a report.
- Return a friendly message such as On time or Late for project tasks.
IF syntax
=IF(logical_test, value_if_true, [value_if_false])| Argument | Required? | What to enter |
|---|---|---|
logical_test | Required | The condition IF checks. It must be something Excel can evaluate as true or false, such as B2>=1000 or A2="North". |
value_if_true | Required | What IF returns when the logical_test is true. It can be a number, text in quotes, a cell reference, or another formula. |
value_if_false | Optional | What IF returns when the logical_test is false. If you leave it out, IF returns FALSE when the test is not true. |
What it returns: IF returns the value_if_true result when the logical_test is true, and the value_if_false result when it is false. The result can be a number, text, a date, a logical value, or an error, depending on what you supply.
IF examples
1 Example 1: Pass or Fail from a score
This example labels each student as Pass or Fail based on a score of 60 or more. The formula in D2 checks whether B2 is greater than or equal to 60. If it is, IF returns Pass; otherwise it returns Fail. Copying the formula down the column gives the result for every student.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Subject | ||
| 2 | Ava | 72 | Maths | Pass | |
| 3 | Ben | 58 | Maths | ||
| 4 | Chloe | 91 | Maths | ||
| 5 | Daniel | 45 | Maths | ||
| 6 | Ella | 60 | Maths | ||
| 7 |
2 Example 2: Flag low stock for reordering
Here the goal is to warn when stock falls to the reorder level or below. The formula in D2 compares the quantity in B2 with the reorder level in C2. If the quantity is less than or equal to the reorder level, IF returns Reorder; otherwise it returns OK. This makes a simple stock alert column that updates as quantities change.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Quantity | ReorderLevel | ||
| 2 | Paper | 40 | 50 | Reorder | |
| 3 | Pens | 120 | 100 | ||
| 4 | Staples | 15 | 30 | ||
| 5 | Folders | 75 | 75 | ||
| 6 | Tape | 200 | 80 | ||
| 7 |
3 Example 3: Apply a discount only for large orders
This example calculates a 10 percent discount when the order total is at least 500, and no discount otherwise. The formula in D2 tests whether B2 is greater than or equal to 500. When true, it multiplies B2 by 0.1 to give the discount amount; when false, it returns 0. The result is a discount column you can subtract from the total.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | OrderID | Total | Customer | ||
| 2 | 1001 | 620 | Northwind | 62 | |
| 3 | 1002 | 480 | Contoso | ||
| 4 | 1003 | 500 | Fabrikam | ||
| 5 | 1004 | 250 | Adventure | ||
| 6 | 1005 | 900 | Litware | ||
| 7 |
4 Example 4: Mark invoices as overdue
The goal is to show which invoices are past their due date. The formula in D2 checks whether the due date in C2 is earlier than the report date in B2. If it is, IF returns Overdue; otherwise it returns On time. Because the dates are stored as real dates, the comparison works correctly and the status updates when the report date changes.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | ReportDate | DueDate | ||
| 2 | INV-001 | 2024-06-15 | 2024-06-10 | Overdue | |
| 3 | INV-002 | 2024-06-15 | 2024-06-20 | ||
| 4 | INV-003 | 2024-06-15 | 2024-06-15 | ||
| 5 | INV-004 | 2024-06-15 | 2024-05-30 | ||
| 6 | INV-005 | 2024-06-15 | 2024-07-01 | ||
| 7 |
5 Example 5: Compare sales with a target
This example produces a performance label for each salesperson. The formula in D2 compares the sales figure in B2 with the target in C2. If sales are greater than or equal to the target, IF returns Above target; otherwise it returns Below target. The label column makes it easy to sort or filter the team's results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Salesperson | Sales | Target | ||
| 2 | Mia | 1200 | 1000 | Above target | |
| 3 | Noah | 850 | 1000 | ||
| 4 | Olivia | 1000 | 1000 | ||
| 5 | Liam | 1500 | 1200 | ||
| 6 | Sophia | 700 | 900 | ||
| 7 |
Common IF errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The logical_test compares incompatible types, such as text with a number, or a date stored as text with a real date. | Make sure both sides of the comparison are the same type. Convert text numbers with VALUE or enter dates as real dates. |
| #NAME? | The function name is misspelled or the formula uses text without quotation marks, so Excel treats it as a name. | Check the spelling of IF and put text results in double quotes, for example "Pass". |
| Wrong result | The logical operator is the wrong way round, such as using > when you meant >=, or the cell references are not locked when copying. | Review the comparison and use absolute references like $B$2 when a reference should not change as you fill down. |
| FALSE shown in the cell | The value_if_false argument was left out and the logical_test was false. | Supply the third argument, for example =IF(B2>=60,"Pass","Fail"), or use an empty string "" if you want a blank. |
| #N/A or unexpected text | The formula returns a value that is later used in a calculation, or text results are compared with numbers downstream. | Keep result types consistent, or wrap the result with IFERROR when the output feeds another formula. |
Tips for using IF
- Write the logical_test as a complete comparison, such as
B2>=60, rather than justB2, so the result is clearly true or false. - Put text results in double quotes, for example
"Pass", but leave numbers unquoted so they can be used in later calculations. - Use
>=and<=when the boundary value should be included; this is a common source of off-by-one mistakes. - Lock references with
$when a threshold sits in one cell and should not move as you copy the formula down. - Keep IF formulas short and readable; if you need many conditions, consider
IFSor a lookup table instead of deeply nested IFs. - Test the formula on a row where you already know the answer before filling it down the whole column.
Frequently asked questions
What does the IF function do in Excel?
IF checks a condition and returns one value when the condition is true and another when it is false. For example, =IF(B2>=60,"Pass","Fail") returns Pass for scores of 60 or more and Fail otherwise.
Is the third argument of IF required?
No. The value_if_false argument is optional. If you leave it out and the condition is false, Excel returns the logical value FALSE. Most people supply a third argument to get a meaningful result.
Can IF return text instead of a number?
Yes. Put the text in double quotes, such as "Overdue". You can also return a cell reference or another formula as either result.
How do I use IF with dates?
Compare dates the same way as numbers, for example =IF(C2<B2,"Overdue","On time"). Make sure the dates are real Excel dates, not text, or the comparison may give the wrong result.
What is the difference between IF and IFS?
IF handles a single condition with two outcomes. IFS checks several conditions in order and returns the result for the first one that is true, which is easier to read than nested IFs.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. IF reference