Exceljet

Quick, clean, and to the point

Introduction to Booleans

In this video, we’ll introduce the idea of boolean values in Excel.

A boolean is a data type with only two possible values.

In Excel, these are the logical values TRUE and FALSE.

You’ll notice that Excel treats TRUE and FALSE in a special way.

If I type the word “true” in lowercase, Excel automatically capitalizes it.

The same thing happens if I type “false”.

Note also that Boolean values are automatically centered in the cell.

Excel actually has a function called “ISLOGICAL” that will test for the boolean values TRUE and FALSE.

If I use this function to test the values in this range, you can see that the function only returns TRUE when a cell contains TRUE or FALSE.

All other values return FALSE.

A Boolean expression is a logical statement that returns  TRUE or FALSE.

Boolean expressions are also called logical expressions.

For example, 5>3 is a boolean expression that returns TRUE. And 5>10 is a boolean expression that returns FALSE.

Boolean expressions in Excel often use cell references. D7=“apple” returns TRUE, and D7=“orange” returns FALSE.

Boolean expressions can use all available math operators.

They can also make use of other functions.

The ISEVEN function here returns TRUE because 100 is an even number.

The formula in H12 returns TRUE because the year of the date in D10 is 2021.

It’s common to see boolean expressions inside other formulas. 

For example, in L7 we use a boolean expression as the logical test inside the IF function to test if J7 is greater than or equal to 70.

Because the result is TRUE, IF returns "Pass".

The same formula in L8 returns "Fail".

The formula in L9 tests if J9 is greater than 200. If TRUE, the result is J9 multiplied by 5%.

If FALSE, the value in column J is multiplied by 3%.

Finally, note that Excel will evaluate numeric values as Booleans on-the-fly when needed.

The formula

=IF(B5,TRUE)

Returns FALSE when B6 is zero, and TRUE, when the value is 1 in the row below.

In fact, as you can see, Excel will evaluate any non-zero number as TRUE.

We'll look at how to convert Booleans to numbers, and how this relates to array formulas in upcoming videos.

Dynamic Array Formulas are available in Office 365 only.

Related shortcuts

Author 
Dave Bruns