How to Use the IF Function in Excel: Syntax, AND, OR, NOT and the Fix for a 0 in the Cell

Microsoft describes IF as a logical comparison between a value and what you expect. Every IF statement has exactly two possible outcomes. Once that clicks, the rest is just deciding what goes in each slot, and how to test more than one thing at a time with AND, OR and NOT.

The three parts of an IF formula

The syntax is IF(logical_test, value_if_true, value_if_false), and only the last part is optional. Here's what each slot does:

Argument	Required?	What it holds
logical_test	Yes	The condition you want to test, such as C2>B2
value_if_true	Yes	What Excel returns when the test is TRUE
value_if_false	No	What Excel returns when the test is FALSE

Text inside a formula has to be wrapped in straight double quotes, so it's "Yes", not Yes. Microsoft makes one exception: TRUE and FALSE, which Excel understands without quotes.

Five IF examples straight from Microsoft

These are the examples on Microsoft's IF page. Between them they cover text results, number results and results that are calculations in their own right.

Formula	What it does
=IF(C2="Yes",1,2)	Returns 1 if C2 says Yes, otherwise 2
=IF(C2=1,"Yes","No")	Shows IF works the other way round too: test a number, return text
=IF(C2>B2,"Over Budget","Within Budget")	Compares actual spend with budget and labels the row
=IF(C2>B2,C2-B2,0)	Returns the overspend (actual minus budget) instead of a label, or 0
=IF(E7="Yes",F5*0.0825,0)	Adds 8.25% sales tax only when E7 says Yes, and 0 otherwise

The fourth one's the most useful in real spreadsheets. The result doesn't have to be a word: it can be another calculation, and Excel only runs it when the test passes.

Why your IF formula shows 0, and two other errors

This is the catch most people hit first. Microsoft's common-problems table says a 0 (zero) in the cell means there was no argument for value_if_true or value_if_false. The fix is to put text, or TRUE or FALSE, in both slots so Excel always has something to return.

What you see	What it means
0 (zero) in the cell	No argument for value_if_true or value_if_false; add text or TRUE/FALSE to both
#NAME? in the cell	Usually a misspelled formula
#VALUE! from AND or OR	The range you referenced holds no logical values

Google Sheets behaves differently here, which trips people up when they move between the two. In Sheets, value_if_false is optional and blank by default, so a missing FALSE result leaves an empty cell rather than a 0. Google also says that putting value_if_true and value_if_false in the wrong order is the single most common source of problems with IF.

IF with AND: every condition has to pass

AND returns TRUE only if every argument is TRUE, and it takes up to 255 conditions. Put it in the logical_test slot and one IF can check several things at once. Text or empty cells inside a referenced range are ignored, but if the range holds no logical values at all, AND returns #VALUE!.

Microsoft's simplest example is =AND(A2>1,A2<100), which is TRUE when A2 sits between 1 and 100 (not including either). Wrap it in IF and you get =IF(AND(A2<A3,A2<100),A2,"The value is out of range"), which shows A2 only when both tests pass.

The bonus scenario on the AND page is the one to copy for real work:

=IF(AND(B14>=$B$7,C14>=$B$5),B14*$B$8,0)

It pays the bonus percentage only if Total Sales meet the Sales Goal and Accounts meet the Account Goal. If either falls short, the result's 0. The dollar signs lock the goal cells, so you can copy the formula down a column of salespeople and every row still checks against the same targets.

IF with OR: one condition is enough

OR flips the logic. It returns TRUE if any argument is TRUE, and FALSE only when all of them are FALSE. It has the same 255-condition limit and the same #VALUE! rule as AND.

Microsoft's range check is =IF(OR(A2<0,A2>50),A2,"The value is out of range"), which returns A2 only when it's below 0 or above 50. Its commission scenario mirrors the bonus one:

=IF(OR(B14>=$B$4,C14>=$B$5),B14*$B$6,0)

Here the salesperson earns commission if Total Sales meet the goal or Accounts meet the goal. Hitting either one's enough.

IF with NOT: reversing the test

NOT takes one logical value and reverses it: TRUE becomes FALSE and FALSE becomes TRUE. So =NOT(A2>100) means A2 is not greater than 100. Microsoft also shows NOT inside AND inside IF:

=IF(AND(NOT(B14<$B$7),NOT(C14<$B$5)),B14*$B$8,0)

That pays when sales aren't below goal and accounts aren't below goal. It gives the same answer as the AND bonus formula, written the other way round. I'd only reach for NOT when it makes the formula easier to read, which isn't often.

IF, AND, OR and NOT side by side

Function	What it does	Arguments	Returns TRUE, or its result, when
IF	One test, two outcomes	logical_test, value_if_true, value_if_false (optional)	value_if_true when the test is TRUE, value_if_false otherwise
AND	All conditions must hold	logical1, logical2 and so on, up to 255	every condition is TRUE
OR	Any condition may hold	logical1, logical2 and so on, up to 255	at least one condition is TRUE
NOT	Flips TRUE and FALSE	logical	the value is FALSE

How to write your first IF formula

1. Select an empty cell and type =IF(
2. Type the test, for example C2>B2, then a comma.
3. Type the TRUE result (wrap text in quotes), then a comma.
4. Type the FALSE result, close the bracket and press Enter.
5. To test two things, put AND(...) or OR(...) in place of the single test.

Once IF makes sense, conditional maths is the obvious next step. My guides to counting cells that meet a condition with COUNTIF and adding them up with SUMIF use the same kind of test, and VLOOKUP is the one to learn when the answer lives in another table.

Google points to two separate functions for the jobs IF wasn't built for: IFS, which returns the value for the first TRUE condition, and IFERROR, for catching errors. They're different functions, so they get their own guides.

The other thing people ask about is covered in the full answer on how to do linear regression in Excel.

My verdict

Use IF for one yes or no decision. The moment you need two conditions, wrap AND or OR inside it rather than stacking IF inside IF; you'll read it more easily next month and so will anyone you share it with. And always fill in both results. A 0 you didn't ask for is the most common IF mistake there is, and it's also the easiest to fix.

Once you've sorted this, how to find and replace in Excel is worth a look.

Frequently asked questions

What does the IF function do in Excel?

It makes a logical comparison and returns one result if the test is TRUE and another if it's FALSE.

Why does my IF formula return 0?

Microsoft says a 0 means there's no argument for value_if_true or value_if_false. Put text, or TRUE or FALSE, in both.

How do I use IF with two conditions?

Put AND or OR inside the test. AND needs every condition to be TRUE; OR needs only one.

Do I need quotes around text in an IF formula?

Yes. Text has to be wrapped in quotes, except TRUE and FALSE, which Excel understands without them.

Is IF different in Google Sheets?

Slightly. In Sheets value_if_false is optional and blank by default, so a missing FALSE result gives an empty cell rather than a 0.