What is Boolean logic in Excel? TRUE, FALSE, and logical tests

Exceljet's glossary states the underlying idea in one line: "Boolean algebra is a mathematical system that represents logical expressions and relationships using only two values: TRUE and FALSE." Everything below follows from there being exactly two.

TRUE and FALSE are values, not words

Type TRUE into a cell and you do not get a text string. You get a logical value, right-aligned like a number and understood as a condition by every function that takes one. Google documents its TRUE function as returning "the logical value TRUE" and adds the note that explains why almost nobody calls it: "In most cases, Google Sheets will automatically convert the TRUE literal to the logical TRUE value, equivalent to this function." Excel behaves the same way.

The practical consequence is that "TRUE" in quotation marks and TRUE without them are different things: one compares text, the other compares logical values. When a condition that looks obviously correct returns FALSE, quoted booleans are the first thing to check.

Comparisons are where booleans come from

Almost no formula types TRUE by hand. Booleans arrive as the result of a comparison operator — the family covered under Operators and precedence, applied here for what it produces rather than for where it sits in the order of evaluation.

Operator	Meaning	=A1 … B1 returns
=	Equal to	TRUE if the values match
<>	Not equal to	TRUE if the values differ
>	Greater than	TRUE if A1 exceeds B1
<	Less than	TRUE if A1 is smaller
>=	Greater than or equal to	TRUE if A1 meets or exceeds B1
<=	Less than or equal to	TRUE if A1 is at most B1

Ablebits gives the rule that makes them predictable: "The result of the comparison in any given case can only be either TRUE or FALSE." There is no third answer and no partial match, which is what lets comparisons be nested and combined without ambiguity.

Two behaviours catch people out. Text comparison ignores capitals — Excel "ignores their case and compares the values symbol by symbol", so ="Apple"="apple" returns TRUE; EXACT is the case-sensitive alternative. And comparison operators match whole values, not parts of them, which is the job Wildcards do inside criteria arguments.

Booleans are 1 and 0 in disguise

This is the part that turns Boolean logic from a vocabulary lesson into a technique. Exceljet: "TRUE can be represented by the number 1, and FALSE can be represented by the number 0", and "any math operation in Excel will coerce TRUE and FALSE values into 1's and 0's automatically."

The demonstrations are one keystroke each: =TRUE+0 returns 1, =TRUE*1 returns 1, =FALSE+0 returns 0, =FALSE*1 returns 0.

The standard way to force that conversion deliberately is the double negative — two minus signs in front of an expression, the double unary. Exceljet describes what it does to an array of results: it "coerces the TRUE and FALSE values that result from the expression" into "1s and 0s". Negating once flips the sign, negating again flips it back, and the round trip leaves a number where a logical value used to be.

Pair that with SUMPRODUCT and you have the pattern that predates most of the modern criteria functions and still outlives them in flexibility. Exceljet's two examples are the whole idiom: =SUMPRODUCT(--(A1:A10="red")) counts the matching cells, and =SUMPRODUCT(--(A1:A10="red")*B1:B10) sums the values in column B where column A matches. Multiplying two boolean arrays gives AND, adding them gives OR, and the arithmetic happens across the whole range at once. Exceljet's summary of the payoff: Boolean logic can "simplify formulas and eliminate the branching seen in nested IF formulas."

AND, OR, NOT and XOR

When a test needs more than one condition, the logical functions combine booleans without arithmetic.

Function	Ablebits' description	Syntax
AND	"Returns TRUE if all of the arguments evaluate to TRUE."	AND(logical1, [logical2])
OR	"Returns TRUE if any argument evaluates to TRUE."	OR(logical1, [logical2])
XOR	"Returns a logical Exclusive Or of all arguments."	XOR(logical1, [logical2])
NOT	"Returns the reversed logical value of its argument. I.e. If the argument is FALSE, then TRUE is returned and vice versa."	NOT(logical)

XOR is the newest of the four — "in Excel 2013, Microsoft introduced the XOR function" — and the least used, because "exactly one of these is true" is a rarer requirement than "all" or "any".

All four accept numbers where they expect logical values, under a rule worth knowing before it surprises you: "If an argument of a logical function contains numbers, then zero evaluates to FALSE, and all other numbers including negative numbers evaluate to TRUE."

Where booleans end up

IF is the obvious consumer: its first argument is a logical test, and the whole function does nothing but pick a branch based on TRUE or FALSE. =IF(B2>100,"over","under") is a comparison feeding a switch.

Less obviously, the criteria arguments of SUMIFS, COUNTIFS and their relatives are not booleans. They are text-or-value criteria — ">100", "red" — evaluated internally against each cell. Writing =COUNTIFS(A:A,B2>100) fails not because the logic is wrong but because the comparison resolves to TRUE before the function sees the range, and TRUE is then matched literally. The result is an empty count rather than one of the values described under Formula errors — and it is exactly the case the SUMPRODUCT idiom above sidesteps.

How Formula Foundry handles this

Formula Foundry does not evaluate anything. It never tells you whether a condition is TRUE, because it has no calculation engine — it parses, formats, explains and translates formulas, and the spreadsheet computes the answers. What the editor does with booleans specifically is modest and worth stating plainly: TRUE and FALSE are constants, so they are colour-coded as constants alongside numbers and quoted strings. That makes a quoted "TRUE" visibly different from a bare TRUE while you are typing, which is the single most common boolean bug — but it is syntax highlighting, not a verdict.

The bigger help is structural. Logical formulas grow by nesting, and a chain of conditions is the type of expression the formula bar renders worst: =IF(AND(B2>100,C2="red"),IF(D2<>"",D2*0.2,0),IF(OR(E2,F2),1,0)) is a single line of brackets to Excel and a shape in the add-on's editor, which opens it across multiple lines, indents along the nesting and highlights matching parentheses. The visual builder goes further, rendering the same formula as a form with each argument named and each nested group shown as a group, so it is obvious which condition governs which branch.

The parser catches the mechanical faults before the formula reaches the cell — unbalanced brackets, a missing operator between the halves of a comparison — collected in one pass rather than one at a time. It cannot tell you that AND should have been OR. The free playground runs the same editor in the browser with nothing to install.

FAQ