IF Then Formula in Excel: Simple Examples to Copy

An IF then formula in Excel works like a sentence: if this is true, then show that, otherwise show something else. Excel doesn't use the word THEN. Instead, the IF function takes three parts in order: =IF(test, value_if_true, value_if_false). This page is a set of short, correct examples you can copy and adapt, from pass/fail to bonuses and blank cells.

How an IF then formula is built#

Every IF formula has the same three parts, separated by commas:

=IF(logical_test, value_if_true, value_if_false)

The test must give TRUE or FALSE. You build it with a comparison operator, such as >=. The IF function explained simply covers the theory in more depth; this page focuses on examples.

The first examples use this score list in A1:B7:

Example 1: Pass or fail#

In C2, type the formula and fill it down (Windows: Ctrl+D; Mac: ⌘D):

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

Lena, Nia, Omar and Quinn show Pass. Marco (48) and Priya (59) show Fail. The words are in double quotes because they're text. Without the quotes, Excel looks for a name called Pass and shows #NAME?.

Keep the pass mark in a cell#

Typing 60 inside the formula works, but if the pass mark changes, you have to edit every row. Put the limit in its own cell instead, say E1, and point the test at it:

=IF(B2>=$E$1,"Pass","Fail")

The $ signs lock E1, so every row still checks against it after you fill down. Change E1 to 50 and Priya flips to Pass at once, with no formula edits. This is a good habit for any threshold: targets, budgets, deadlines or discount levels. It also makes the rule visible to anyone who opens the sheet, instead of hiding it inside a formula.

The comparison operators you can use in the test#

The test compares two things. These are the six operators Excel understands:

Two traps: "not equal" is <>, not !=, and "greater than or equal" is >=, not =>. The lesson on TRUE, FALSE and comparisons lets you practise each one. For more on the difference between > and >=, see IF greater than in Excel.

Example 2: Yes or no from a text match#

To test text, put the text in double quotes inside the test as well:

=IF(C2="Paid","Yes","No")

This shows Yes when C2 holds Paid. The comparison isn't case-sensitive, so paid and PAID also match. A Yes/No column like this is handy for filters and counts; IF with Yes or No shows more versions. For partial matches, such as "contains Paid", see IF with text.

Example 3: Return a number or a calculation#

The then and else parts can be numbers or formulas. Leave the quotes off.

=IF(C2>=10000,C2*5%,0)

This pays a 5% bonus on sales of 10,000 or more and 0 below that. On sales of 12,400 the result is 620. On 9,800 it's 0.

Another common one checks a budget and shows the overspend:

=IF(C2>B2,C2-B2,0)

If actual spend in C2 is 1,150 and the budget in B2 is 1,000, the result is 150. If spend is under budget, it shows 0.

Example 4: Leave the cell blank#

Use "", two double quotes with nothing between them, to show nothing.

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

This multiplies quantity by price only when B2 has a value. Until then, the cell stays empty instead of showing 0. The same "" works as the then part, for example =IF(B2<60,"Retake","") flags only the students who need to retake.

Example 5: More than two outcomes#

When there are three or more results, put a second IF in the else part. This grades the scores:

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

Excel checks the tests in order and stops at the first TRUE. Nia (90) and Quinn (81) get A, Lena (72) and Omar (65) get B, Marco and Priya get C. Always test the biggest threshold first; otherwise 90 would stop at >=60 and get B.

In Excel 2019, 2021 and Microsoft 365, IFS reads more easily:

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

The final TRUE acts as "everything else".

To check two conditions at once, such as a score of 60 or more and full attendance, combine IF with AND; see IF with AND.

Common IF then mistakes#

Check these when your formula won't work:

1. Missing quotes around text. =IF(B2>=60,Pass,Fail) returns #NAME?.
2. Quotes around numbers. =IF(B2>=60,"1","0") returns text, which SUM ignores. Use 1 and 0.
3. Wrong operator. => and != don't exist in Excel.
4. Nested IFs in the wrong order. Test the highest threshold first.
5. Numbers stored as text. A score typed as text won't compare correctly with 60. Convert it with VALUE.

The IF function guide is a one-page reference for the syntax.

Practise it#

IF formulas show up in almost every Excel job test, usually in the first few questions. Practise the IF function in a live spreadsheet: you type the formula and it checks it instantly. Then practise TRUE, FALSE and comparisons to get the operators right first time, and try the basic Excel test when you're ready.