The Excel workbook is included with our video training.

Abstract 

In this video, we’ll introduce the idea of boolean values in Excel. Boolean values are a key building block in dynamic array formulas.

Transcript 

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. 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 have 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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.