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. This formula uses SEARCH to look for text in search_box inside columns C, D, E, and F, glued together with CONCATENATE. Make sure the row number matches the row of the active cell. The key to understanding this rule is to remember that it will be evaluated for each cell in the table. 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 searchbox 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 clear the search_box, all rows are highlighted. That’s because SEARCH returns the number 1 if the text to find is empty. You can fix this problem by wrapping the SEARCH function in an IF statement that returns zero when the search box is empty. For the logical test, use ISBLANK(search_box). If true, return zero. Otherwise, use the original formula. Now, no rows are highlighted when the search box is empty, but the rule still fires when text is entered. You can use this approach to search as many columns as you like.
Dave Bruns
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.
