How to build a search box with conditional formatting

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.

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,namethe cell “search_box”.

This will make it easier to use later in a formula.

Then add some text, so you could see the rule applied once it’s created.

Now we need to add a rule that uses the search box.

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

When SEARCH finds something, it returns the position as a number.

If the text is not found, it returns zero.

see to it the row number matches the row of the active cell.

The dollar signs lock the columns, but the rows are free to change.

When SEARCH returns any number but zero, the rule will fire and the conditional formatting will be applied.

Now add a light fill that matches the color of the search box, and complete the rule.

The search box is now functional, and orders where the city is “Dallas” are highlighted.

You don’t have to enter complete words, because the SEARCH function just matches text.

There is a problem, though.

If we wipe the search_box, all rows are highlighted.

That’s because SEARCH returns the number 1 if the text to find is empty.

For the logical test, use ISBLANK(search_box).

If true, return zero.

Otherwise, use the original formula.

it’s possible for you to use this approach to search as many columns as you like.