TRUE and FALSE in Excel: What They Mean in Formulas

TRUE and FALSE in Excel are logical values (also called Boolean values): the answer Excel gives to a yes-or-no question. Every comparison, such as =B2>=50, returns TRUE or FALSE, and functions like IF and AND are built on them. This guide explains where TRUE and FALSE come from, how formulas use them, and the traps that give wrong totals in reports and job tests.

What are TRUE and FALSE in Excel?#

TRUE and FALSE are a third type of value, next to numbers and text. When you type true into a cell, Excel turns it into TRUE in capitals and centres it, which shows it's a logical value and not text.

You will see them in three places:

• Comparisons. =10>5 returns TRUE. =10<5 returns FALSE.
• Logical functions. AND, OR, NOT and the IS functions, such as ISNUMBER, return TRUE or FALSE.
• Function arguments. Some functions take TRUE/FALSE as a setting. The best-known is the last argument of VLOOKUP, explained in VLOOKUP TRUE vs FALSE.

Excel also has the functions =TRUE() and =FALSE(), but typing TRUE or FALSE on its own does the same job.

How comparisons return TRUE or FALSE#

A comparison puts an operator between two values and returns TRUE or FALSE. All examples use this table:

Column D holds =B2>=50, filled down. Ana's 72 is at least 50, so D2 shows TRUE. Ben's 45 is not, so D3 shows FALSE.

Text comparisons ignore capitals, which is why ="ana" matches Ana. Use EXACT when case matters. The full list of operators is in the guide to comparison operators in Excel.

How IF and AND use TRUE and FALSE#

The IF function reads a TRUE/FALSE test and returns one value for TRUE and another for FALSE:

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

For Ana this returns Pass, and for Ben Fail. If you leave out the last part, =IF(B2>=50,"Pass"), Ben's row shows the word FALSE. That stray FALSE in a report almost always means a missing argument. The IF lesson and the guide to the Excel IF function cover more patterns.

The AND function returns TRUE only when every test is TRUE. To find students who passed and paid:

=AND(B2>=50,C2)

C2 is already TRUE or FALSE, so you don't need C2=TRUE. This returns TRUE for Ana, Dev and Femi. Chloe passed but hasn't paid, so her row is FALSE. OR returns TRUE if any test is TRUE, and NOT flips TRUE to FALSE. See AND, OR and NOT in Excel for both.

IF also accepts numbers as the test: 0 counts as FALSE and any other number as TRUE. So =IF(C9,"Has value","Zero") works when C9 holds a number.

Why TRUE equals 1 and FALSE equals 0#

In arithmetic, Excel treats TRUE as 1 and FALSE as 0. =TRUE+TRUE returns 2 and =(B2>=50)*10 returns 10 for Ana. This lets you count and add with conditions.

The usual way to convert is the double minus, --. The first minus turns TRUE into -1, and the second turns it back to 1:

=SUMPRODUCT(--(B2:B7>=50))

B2:B7>=50 produces six values: TRUE, FALSE, TRUE, TRUE, FALSE, TRUE. The -- turns them into 1, 0, 1, 1, 0, 1, and SUMPRODUCT adds them: 4 students passed.

Multiplying two TRUE/FALSE arrays works like AND, because 1 × 1 is the only product that gives 1:

=SUMPRODUCT((B2:B7>=50)*(C2:C7))

This returns 3 (Ana, Dev and Femi passed and paid). Adding two arrays works like OR, as long as you then check whether each result is greater than 0.

Why SUM ignores TRUE and FALSE#

SUM, AVERAGE and COUNT skip logical values that sit in a range. So =SUM(D2:D7) returns 0, not 4, even though column D has four TRUEs. That surprises many people.

To count the TRUE cells, use COUNTIF with TRUE as the criteria, without quotes:

=COUNTIF(D2:D7,TRUE)

This returns 4. COUNTA, on the other hand, counts TRUE and FALSE cells as filled, so =COUNTA(D2:D7) returns 6.

Common traps with TRUE and FALSE#

These are the mistakes that most often break logical formulas:

1. Text that looks like TRUE. Data imported from another system may hold the text "TRUE". It sits on the left of the cell, and =C2=TRUE returns FALSE. Check it with ISTEXT or ISLOGICAL from the IS functions.
2. Quotes around TRUE. =IF(C2="TRUE","Paid","Unpaid") compares C2 with the text "TRUE", so a real logical TRUE gives Unpaid. Leave the quotes off: =IF(C2,"Paid","Unpaid").
3. Text compared with numbers. In a comparison, text always counts as larger than any number. So =B2>100 returns TRUE if B2 holds the text "45".
4. Expecting formatting to convert. A number format can't make TRUE show as 1. Use --, *1 or N.
5. Writing =C2=TRUE inside AND or IF. It works, but C2 alone is shorter and does the same.

Checkboxes in Microsoft 365 (Insert > Checkbox) store TRUE when ticked and FALSE when not. So everything above applies to task lists with checkboxes too.

Practise it#

TRUE and FALSE are easy to read and easy to get wrong under time pressure. Practise TRUE/FALSE comparisons in a live spreadsheet: you write tests like =B2>=50 and the sheet checks them instantly. Then turn those tests into results in the IF function lesson.