Excel guide · 5 min read

IF formula not working in Excel: causes and fixes

Quick answerPut the test on its own in a spare cell (for example =B3>=70) and see whether it returns the TRUE or FALSE you expect. If it does not, the data is the problem: a number stored as text, a number in quotes, a hidden space or a blank cell. If the test is right but IF shows FALSE or 0, an argument is missing.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	F
1	Employee	Quiz score	Safety form signed	Bonus	Pass mark
2	Maya Lin	92	Yes	150	70
3	Omar Haddad	65	Yes
4	Grace Kim	78	Yes	100
5	Leo Brandt	64	No
6	Ana Ruiz	85	yes	100
7	Sam Patel		No
8	Ivy Chen	70	Yes
9	Tom Reyes	45	Yes

Download this sheet as an Excel practice file (.xlsx)

Quick diagnosis: test the condition by itself

The sheet tracks a safety quiz: score, whether the safety form is signed, and a bonus. The pass mark is 70. Omar's score was pasted in as text, Grace's "Yes" has a trailing space, Sam missed the quiz, and Ivy's bonus has not been entered. Every one of those breaks an IF in a different way.

IF only does what its first argument tells it. Copy that part, without the IF, into a spare cell. Omar scored 65, so =B3>=70 should be FALSE, but it returns TRUE. The IF is fine; the data is not.

Before you dig deeper, rule out the problems that are not about IF at all. If the cell shows the formula text instead of a result, the cell is formatted as Text or Show Formulas is on. If the result does not change when you edit a score, calculation is set to Manual (Formulas > Calculation Options > Automatic).

=B3>=70

Result: TRUE

TRUE, even though Omar's score is 65. That points to the data, not the IF (cause 3).

Cause 1: IF shows FALSE or 0

=IF(B5>=70,"Pass") has no value_if_false. Leo scored 64, the test is FALSE, and with nothing to show Excel displays the word FALSE. Add the third argument: =IF(B5>=70,"Pass","Fail") returns Fail. Leave a trailing comma with nothing after it, =IF(B5>=70,"Pass",), and you get 0 instead.

A 0 also appears when IF returns an empty cell. Ivy passed with exactly 70, but her bonus in D8 is blank, so =IF(B8>=70,D8) shows 0, not an empty cell. Use =IF(B8>=70,IF(D8="","",D8),"") if blanks should stay blank.

=IF(B5>=70,"Pass")

Result: FALSE

FALSE: Leo's 64 fails the test and the formula has nothing to show for that case.

Cause 2: the wrong branch because a number is stored as text

B3 holds "65" as text. When Excel compares text with a number, text always counts as bigger than any number, so "65">=70 is TRUE and Omar passes. =ISTEXT(B3) returns TRUE and confirms it. COUNTIF(B2:B9,">=70") skips text completely and counts 4 passes, so two formulas on the same sheet now disagree.

Inside the formula, two minus signs turn the text into a number: =IF(--B3>=70,"Pass","Fail") returns Fail. The lasting fix is to convert the column: select it, click the warning icon and choose Convert to Number.

The same rule bites when you put quotes around the number in the test. =IF(B2>"70","Pass","Fail") returns Fail for Maya's 92, because "70" in quotes is text and 92 is never bigger than text. Quotes go around text you are matching, like "Yes", never around numbers. The reverse also happens in results: =IF(B2>=70,"1","0") returns text, and SUM over a column of those text 1s and 0s gives 0. Write =IF(B2>=70,1,0) instead.

=IF(B3>=70,"Pass","Fail")

Result: Pass

Pass, which is wrong: Omar scored 65, but the score is text, and text ranks above every number.

Cause 3: ="Yes" fails because of a hidden space

Grace's form column says "Yes " with a space at the end, so C4="Yes" is FALSE and she is chased for a form she signed. =LEN(C4) returns 4 for a 3-letter word, which proves it. Wrap the cell in TRIM: =IF(TRIM(C4)="Yes","Cleared","Chase") returns Cleared. Better still, clean the column once with TRIM or Find and Replace, and use Data > Data Validation with a Yes/No list so it cannot happen again.

Upper and lower case is not the problem: Ana typed "yes" and C6="Yes" is TRUE, because = ignores case in Excel. If you need case to count, use EXACT(C6,"Yes"), which returns FALSE here.

=IF(C4="Yes","Cleared","Chase")

Result: Chase

Chase, even though Grace signed: C4 holds "Yes " with a trailing space.

Cause 4: nested IFs in the wrong order

A nested IF stops at the first test that is TRUE. Grades written from the bottom up, =IF(B2>=50,"C",IF(B2>=70,"B",IF(B2>=85,"A","F"))), give Maya's 92 a C, because 92>=50 is already TRUE and the rest is never checked.

With >= tests, start at the highest band and work down: =IF(B2>=85,"A",IF(B2>=70,"B",IF(B2>=50,"C","F"))) returns A. With <= tests, start at the lowest. If you have more than three or four bands, a lookup table is easier to check than a long nest.

=IF(B2>=50,"C",IF(B2>=70,"B",IF(B2>=85,"A","F")))

Result: C

C for a score of 92: the first test is TRUE, so Excel never reaches the A band.

Cause 5: 60<=B5<=69 does not test "between"

Writing a range check the way you would on paper is valid syntax, so Excel accepts it without complaint. It then works left to right: 60<=B5 is TRUE for Leo's 64, and the next step compares TRUE<=69. Excel ranks TRUE and FALSE above every number, so that is FALSE, and the formula returns the "not between" result for every score.

Split it into two tests inside AND: =IF(AND(B5>=60,B5<=69),"Retake","") returns Retake for Leo.

=IF(60<=B5<=69,"Retake","")

Result: "" (empty text, so the cell looks blank)

An empty result for Leo's 64, which should get Retake. The chained test is always FALSE.

Cause 6: blank cells count as 0

Sam missed the quiz, so B7 is empty. In a comparison, an empty cell is treated as 0, so B7<70 is TRUE and Sam is marked Fail as if he had scored zero. Test for the blank first: =IF(B7="","Absent",IF(B7<70,"Fail","Pass")) returns Absent.

B7="" is the safer test because it is also TRUE for a formula that returns "". ISBLANK(B7) is only TRUE for a truly empty cell.

=IF(B7<70,"Fail","Pass")

Result: Fail

Fail for Sam, who did not take the quiz: the empty cell counts as a score of 0.

Common mistakes

Writing AND or OR between the tests

=IF(B2>=70 AND C2="Yes",...) is not valid and Excel refuses the formula. AND and OR are functions: =IF(AND(B2>=70,C2="Yes"),...).

Typing "TRUE" and "FALSE" in quotes

=IF(B2>=70,"TRUE","FALSE") returns text, and a later test like =E2=TRUE gives FALSE. Leave the quotes off, or just use =B2>=70.

Hard-coding the pass mark

Put it in an input cell such as F2 and write B2>=$F$2, so changing the mark updates every row.

Patching every formula instead of the data

--B3 and TRIM(C4) fix one cell's symptom. Convert text numbers and trim the column once.

FAQ

Why does my IF formula return FALSE?

The test was FALSE and the formula has no third argument. Add value_if_false, for example =IF(B5>=70,"Pass","Fail").

Why does my IF formula give the wrong answer?

Test the condition alone in a spare cell. Usually a number is stored as text (text ranks above every number), a number is in quotes, or a text cell has a hidden space.

Why does IF return 0 instead of a blank?

IF returned a reference to an empty cell, or a trailing comma left value_if_false empty. Return "" for the blank case instead.

Why does IF show the formula instead of the result?

The cell was formatted as Text before you typed the formula, or Show Formulas is on. Set the format to General and re-enter the formula, or press Ctrl+` to turn Show Formulas off.

Is the IF function case-sensitive?

No. ="Yes" also matches "yes" and "YES". Use EXACT inside the IF when case matters.

Functions used

Related guides

Updated November 4, 2026