Search with conditional formatting
Searching for rows containing a particular word or text can be easily accomplished with the auto-filter function. Just type the word into the box found within the auto-filter dropdown list (for Excel 2013 and above) and auto-filter will display those rows that contain the word or text found in that particular column. What if the word you wish to search for can be found in multiple columns? In this scenario, auto-filter will not work. One of the solutions to this problem is to apply conditional formatting to the whole range of data, just like how we do it below.
First, fill a cell with a colour and 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.
When you have completed the steps, you will get the same as shown in the picture below.
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 are 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.
Here is how it is done.
First select the range that you want to format conditionally. In our example above select the whole range from A4:H25.
Go to the Home tab and select conditional formatting (for Excel 2007 – 2016). Conditional Formatting is grouped within Style.
In the dropdown list, Choose Manage Rules.
In the pop-up window, select New Rule.
Follow the following steps in the next pop-up window.
1. Select the option “Use a formula to determine which cells to format”.
2. Type the formula “=search(search_box, $b4 & $c4 & $d4 & $e4 & $f4 & $g4 & $h4)” in the formula box.
3. Click on format tab to select the fill colour.
4. Click Ok.
When you have completed the steps, you should get the same setting as shown in the picture below:
The values are selected, but the conditional formatting rule is defined so that only the format settings can appear.
Click ok. After that you will see a dialog like below.
Click OK. Then the yellow colour format will be applied for the rows that contain “Australia” is now displayed in thr below.
How the formula =search(search_box, $b4 & $c4 & $d4 & $e4 & $f4 & $g4 & $h4) works.
This formula uses SEARCH to look for text in search_box inside columns B,C,D,E,F,G and H, glued together with concatenation.
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 data list. 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 colour of the search box, and complete the rule.
The search box is now functional, and formatted where the country is “Australia” are highlighted. You don’t have to enter complete words, because the SEARCH function just matches text.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
