Finding Boolean Values

by Allen Wyatt
(last updated February 14, 2015)

4

Michael has a long column of calculated logical values ("TRUE" or "FALSE"). He would like to use Find and Replace to quickly find the first occurence of FALSE. However, when he selects the column and searches for FALSE, "FALSE", 0, etc., Excel always returns a "cannot find the data..." message. Michael is wondering how to do this type of searching.

The answer to this question depends on how the TRUE and FALSE values are showing up in your cells. If you simply type FALSE into a cell and press Enter, then Excel considers that a Boolean value and formats the cell accordingly; the same goes if you type in TRUE. You could also type either of the following into a cell:

=TRUE
=FALSE

In all these instances you can do a regular search operation and just search for either TRUE or FALSE, and Excel finds the cells. It is a different story, however, if the TRUE or FALSE shown in a cell is the result of a formula. For instance, consider the following formula:

=2 > 4

The result will show as FALSE in a cell, and a regular search operation won't find the FALSE result. Why is that? The reason is buried within the Find tab of the Find and Replace dialog box. Display the dialog box and click the Options button so the box is expanded. (See Figure 1.)

Figure 1. The Find tab of the Find and Replace dialog box.

Note the Look In drop-down list; it is this setting that controls how successful Excel is in finding your Boolean values. If the setting is Formulas, then Excel looks inside the cell contents to do its searching. In the case of the first two ways of getting a Boolean value into a cell—either typing TRUE or FALSE or entering =TRUE or =FALSE—then looking inside the cell produces a match. If looking inside the other cell (the one with =2 > 4), a Boolean value is not found there, so there is no match.

To get the widest matching, you need to change the Look In drop-down list to Values. This type of searching finds the results of formulas, meaning it finds what is displayed by whatever operation is done within the cell. Setting this drop-down list to Values and then searching for FALSE will match a cell where FALSE is entered, where =FALSE is entered, and where =2 > 4 is entered.

There is a drawback to searching for Boolean values, however. Suppose a cell contains the following text:

That was a false statement!

Searching for FALSE using Values will also match this, as will searching using Formulas. This, even though the text doesn't represent a valid Boolean value. This can be frustrating, depending up the characteristics of your data. You can get around that, somewhat, by choosing the Match Entire Cell Contents check box in the dialog box.

There is another way to find the first FALSE (or TRUE) value in a column, however: Use the filerting capabilities of Excel.

  1. Make sure your data has column headings defined.
  2. Display the Data tab of the ribbon.
  3. Click the Filter tool in the Sort & Filter group. Excel places a drop-down arrow next to the column headings.
  4. Click the drop-down arrow next to the heading of the column containing your Boolean values. You'll see a number of options, including a checklist of all the values in the column.
  5. Make sure that only the FALSE value has a check mark next to it.
  6. Click on OK. Your data is collapsed so that only FALSE values show in the column.

The filtered data allows you to easily see the first row with a FALSE value. You can later remove the filter by again displaying the Data tab of the ribbon and clicking the Filter tool.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1786) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Determining if Num Lock is On

Need to know if the Num Lock key is on or off? You can use a short bit of macro code to figure out the state of the key.

Discover More

Specifying Print Quantity in a Cell

When you print a worksheet, you can specify in the Print dialog box how many copies you want printed. If you want the print ...

Discover More

Formatting Currency

If you want to format currency values so that Excel uses periods between groups of thousands and commas as a decimal ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Searching for Line Breaks

If you need to find where line breaks are located in cells, there are a couple of ways you can proceed. Here's a quick ...

Discover More

Finding and Replacing with Subscripts

Want to use Find and Replace to change the formatting of a cell's contents? You would be out of luck; Excel won't let you do ...

Discover More

Searching by Columns, by Default

Do you often want to search through a worksheet by column rather than by row? Excel defaults to searching by row, of course, ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 0 + 7?

2016-02-06 16:44:43

RAK_da_Pira

The VBA equivalent of the search for the first FALSE in a range is:

================================================================================

Dim search_range as Excel.Range, R_false as Excel.Range

Set R_false = search_range.Find(False, , xlValues, xlWhole, , , True)
R_false.Select

================================================================================

That's False, and not the string "FALSE" as WhatToFind, which is good news, as this will be independent of the language.


2015-02-15 08:22:20

Uma

Thank you for showing me this hidden gem.


2015-02-15 06:32:33

Michael (Micky) Avidan

@Jerry,
There is a very simple workaround - once you use a PERSONAL Template File.
All you need is to put, there, an "OPEN" Event Macro and you are set:
=============================
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Add
ActiveSheet.Cells.Find What:="", After:=ActiveCell, LookIn:=xlValues
ActiveWorkbook.Close savechanges:=False
Application.Wait (Now + TimeValue("0:00:02")) ' Needed to avoid Excel Freez !!! 5/3/2013
Application.ScreenUpdating = True
End Sub
============
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2015-02-14 10:53:21

Jerry

I could never understand why Microsoft set the default value of "Look in" to "Formulas"! (Maybe to protect formulas from being inadvertently changed by a Replace action?) 99% of the time we want to find a value in the cell, not in the underlying formula. Many of my Excel students have been confused by this setting. They will often say, "But I can SEE it in the cell. Why doesn't Excel FIND it?!"


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.