How to use IF in Excel
Returns one value if a test is true, another if false.
Syntax
logical_testA comparison, like F6>=100value_if_trueWhat to show when it is true[value_if_false]What to show when it is false optionalExample
Here’s IF on a small table of orders. The highlighted cells are the ones the formula reads.
=IF(F6>=100,"Big","Small")Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with IF
Text results need quotes
Write "Big", not Big. Without quotes Excel looks for a named range called Big and returns #NAME?.
Don’t put quotes around numbers you compare
F6>="100" compares against text and gives the wrong answer. Use F6>=100.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=IF(F6>=100,"Big","Small"
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Text without quotes
=IF(F6>=100,Big,"Small")
Text needs quotes. Big isn’t a function or a cell, so Excel can’t read it. If you mean the word, write "Big" with quotes.
Questions
What does the IF function do in Excel?
Returns one value if a test is true, another if false. The syntax is IF(logical_test, value_if_true, [value_if_false]).
Why is my IF formula giving the wrong answer?
Text results need quotes: Write "Big", not Big. Without quotes Excel looks for a named range called Big and returns #NAME?. Don’t put quotes around numbers you compare: F6>="100" compares against text and gives the wrong answer. Use F6>=100.
Does IF work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.