The Excel IF Function Explained Simply

The Excel IF function lets a cell make a simple decision: if a condition is true, show one thing; if not, show something else. It's the formula behind Pass/Fail columns, Over/Under budget flags and bonus checks. This guide is for beginners and job seekers who want to write IF formulas with confidence, including in a timed Excel test.

How does the Excel IF function work?#

IF takes a yes-or-no test and returns one of two results. The syntax is:

=IF(logical_test, value_if_true, value_if_false)

The test is usually a comparison such as B2>=70 ("is B2 at least 70?"). If comparisons are new to you, start with how TRUE, FALSE and the comparison signs work. You can also practise them in the TRUE, FALSE and comparisons lesson.

How to write an IF formula, step by step#

Write the test first, then the two results, separated by commas. Here is a small list of test scores with a pass mark of 70:

1. Click C2.
2. Type =IF(B2>=70,"Pass","Fail") and press Enter. Priya's 82 is at least 70, so C2 shows Pass.
3. Copy the formula down to C5. Select C2:C5 and press Ctrl+D (Windows) or ⌘D (Mac). The reference B2 moves to B3, B4 and B5 as you fill.

The results are Pass, Fail, Pass and Pass. Look at Elena: she scored exactly 70 and passes, because >= means "greater than or equal to". With > she would fail, since 70 is not greater than 70. Most IF mistakes in tests come from choosing > when the task says "at least".

How do you use IF with text?#

Put text in double quotes, both in the test and in the results. For example, to label customers by city:

=IF(B2="Leeds","North","Other")

Two things to know about text tests:

• Capital letters don't matter. "LEEDS", "Leeds" and "leeds" all match. If case matters, use =IF(EXACT(B2,"Leeds"),...).
• Spaces do matter. "Leeds " with a trailing space is not equal to "Leeds". Data copied from other systems often has hidden spaces, so wrap the cell in TRIM if results look wrong: =IF(TRIM(B2)="Leeds",...).

Without the quotes, Excel thinks Leeds is a name it should know, and you get a #NAME? error.

Can IF return a number or a calculation?#

Yes. The results can be numbers, cell references or whole calculations, and those don't need quotes. Take a budget sheet where B is the budget and C is the actual spend:

The second formula shows how much each department overspent, and 0 when it didn't. Don't type "0" in quotes: that is text, and SUM skips text, so your total would be wrong. Notice IT: it spent exactly its budget, and 25000 is not greater than 25000, so it counts as Under.

To leave a cell empty instead of showing 0, use two double quotes with nothing between them: =IF(C2>B2,C2-B2,"").

The same trick works in the test. A common pattern skips rows that have no data yet:

=IF(C2="","",IF(C2>B2,"Over","Under"))

If C2 is empty, the cell stays empty. Otherwise the inner IF does the real check. Without this, an empty Actual cell counts as 0, and every department with no figures yet shows Under, which looks like good news but isn't.

Why put the limit in a cell instead of the formula?#

A limit in its own cell can change without editing a single formula. Typing 70 into every IF means you must edit every row when the pass mark changes. Instead, put the pass mark in a cell such as G1 and point at it:

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

The dollar signs lock G1, so every row still points at it after you fill down. Press F4 (Windows) or ⌘T (Mac) while the cursor is on G1 to add them. Change G1 to 75 and the whole column updates. Interviewers notice this habit, because it is how real reports stay correct. Learn more about absolute references and the dollar sign.

Why is my IF formula not working?#

Most broken IF formulas have one of these problems:

The last two catch many people. When Excel compares a number with text, it treats text as larger than any number. So a score imported as text, such as '58, passes >=70. Check suspicious cells with ISNUMBER and the other IS functions.

Curly "smart" quotes copied from a web page or Word also break formulas. Retype them as straight quotes (") in Excel.

What if you need more than two results or several conditions?#

IF only chooses between two results, but you can combine it with other functions:

• More than two outcomes, like grades A, B, C and F: put another IF in the FALSE slot. See how nested IF handles grades and bands.
• A cleaner list of outcomes in Excel 2019 or Microsoft 365: use the IFS function, which needs no nesting.
• Two or more conditions at once, like "sales over 50000 and a rating of at least 4.5": put AND or OR inside the test. See AND, OR and NOT with IF.

For a quick reference to every argument, read the IF function guide.

Practise it#

You learn IF fastest by writing it, not by reading it. Try the exercise on this page, then work through the IF function lesson in a live spreadsheet. It covers Pass/Fail, a pass mark in its own cell and IF with calculations, and it checks each answer as you go.