Convert Numbers into True and False in Excel

Add to Favorites

How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very important when working with more complex formulas, especially arrays.


Convert Numbers into TRUE and FALSE

Real World Example

More Examples (Easier)


Convert Numbers into TRUE and FALSE

For the numbers, I will use 1 and 0, but it could be any series of numbers.

We use a simple logic formula to test if the value is a 1 or a 0 and this logic formula is what will output TRUE or FALSE.

Sample Data:


Enter the logic formula:





Copy the formula down:


If you want the 1 to evaluate to TRUE and the 0 to FALSE, just change the formula to this:



Now, I used 1 and 0 for the numbers but it could have been a situation where I had numbers like this: 0,1,2,3,0,1,2,3 and in that case I might want only the 0 values to evaluate to TRUE and the others to evaluate to FALSE. This occurs often with using the MOD() and ROW() function to do something at a set interval of rows in Excel; this is particularly true for the real world example in the next section and the additional links in the section after that.

Real World Example

The above example seems pretty useless and that's because this technique is often buried deep within a complex formula and the result of this technique is never directly seen by the user.

Usually, an array formula would use this technique.



This is an Array Formula and so you must enter it using Ctrl + Shift + Enter.

This will sum values that occur every so many rows in a list; so, it could add together the values from every second row, or third row, or 5th row, etc.

Here is a screenshot of it from the Sum Values from Every X Number of Rows in Excel tutorial.


MOD(ROW(A1:A6),2) is what returns the integer (number) that we can then use to get a TRUE and FALSE value.

=0 is added to the end of the previous piece of code and that is what causes the logical comparison to happen that then results in a TRUE or FALSE value being output.

The array formula that contains all of this then uses the TRUE and FALSE values to check which values it should sum. It will only sum the TRUE values.

More Examples (Easier)

The real world example that I used above is kind of complex so I wanted to include some of our other tutorials that use this same feature but that are not array formulas and are much easier to understand.

Increment a Value Every X Number of Rows in Excel

Automatically Shade Every Other Row When You Add Data in Excel

Shade Every Other Row in Excel Quickly


This little trick is something that may not seem important but it's easy to remember, just a logical comparison, and it will come in handy when you start to use more complex functions and also array formulas in Excel, I promise.

Make sure to download the sample file for this tutorial so you can work with this example in Excel.

Excel Function: FALSE(), TRUE()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Multiply by True and False in Excel
Tutorial: You can multiply numbers by TRUE and FALSE in Excel. This effectively allows you to elimi...
Count Cells Containing TRUE or FALSE in Excel
Tutorial: Count the number of cells that contain TRUE, FALSE, or one of the two. This lets you count...
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...
Quickly Convert Formulas into Their Output Values in Excel
Tutorial: This tutorial teaches you how to convert a formula or function into its displayed output ...
Compare Values in Excel - Beginner to Advanced
Tutorial: How to compare text, numbers, and dates in Excel - including case sensitive text comparis...
Introducing Logic into Formulas and Functions in Excel
Tutorial: In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a ...