by Allen Wyatt
(last updated February 14, 2015)
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:
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.
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Finding and replacing information in a worksheet is easy. Finding and replacing in other objects (such as text boxes or ...Discover More
Finding and replace dates contained within other text in a cell can be a bit tricky. This tip looks at some approaches to ...Discover More
The Find and Replace feature in Excel is one of the workhorse editing tools you can use. When the Find and Replace dialog ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.