How to build a search box with conditional formatting
In this video, we'll look at a way to create a search box that highlights rows in a table, by using conditional formatting, and a formula that checks several columns at once.
This is a great alternative to filtering, because you can see the information you're looking for highlighted in context.
Let's take a look.
Here we have a table that contains of order data. We could add a filter, and use it to explore the data.
But filters can be a little clunky.
You have keep changing the filter, and you can't see what you're looking for in the context of other data.
Let's take a different approach and add a "search box" above the data. We'll use conditional formatting to highlight rows that contain text typed in the search box.
First, label the search box, and add a fill color. Next, name the cell "search_box". This will make it easier to use later in a formula. Then add some text, so you can see the rule applied once it's created.
Now we need to add a rule that uses the search box. Select the entire data range, and add a custom conditional formatting rule that uses a formula.
To make the rule flexible, we're going to use the SEARCH function. SEARCH takes 3 arguments: the text to search for, the text to look within, and, optionally, a starting position. When SEARCH finds something, it returns the position as a number. If the text is not found, it returns zero.
The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....