Quick, clean, and to the point

How to build a search box to highlight data

In this video, we use conditional formatting to create an interactive search box to highlight data. This is a nice alternative to filtering, because the information you're looking is highlighted in context, and the search box checks multiple columns at the same time (using concatenation).

Video Transcript

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

Key functions

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts. Read more.