Search and Highlight Data Using Conditional Formatting

Video – Search and Highlight Data Using Conditional Formatting

If you work with large datasets, there can be a need to create a search functionality that allows you to quickly highlight cells/rows for the searched term.

While there is no direct way to do this in Excel, you can create a search functionality using Conditional Formatting.

For example, suppose you have a dataset as shown below (in the image). It has columns for Product Name, Sales Rep, and Country.

Now you can use conditional formatting to search for a keyword (by entering it in cell C2) and highlight all the cells that have that keyword.

Something as shown below (where I enter the item name in cell B2 and press Enter, the entire row gets highlighted):

Search and Highlight Data in Excel - Demo

In this tutorial, I will show you how to create this search and highlight functionality in Excel.

Later in the tutorial, we will go a bit advanced and see how to make it dynamic (so that it highlights while you’re typing in the search box).

Click here to download the example file and follow along.

Search and Highlight Matching Cells

In this section. I will show you how to search and highlight only the matching cells in a dataset.

Something as shown below:

Search and Highlight Matching cells only

Here are the steps to search and highlight all the cells that have the matching text:

  1. Select the dataset on which you want to apply Conditional Formatting (A4:F19 in this example).
  2. Click the Home tab.Home Tab in the Excel Ribbon
  3. In the Styles group, click on Conditional Formatting.
  4. In the drop-down options, click on New Rule.
    Search and Highlight Data Using Conditional Formatting - New Rule
  5. In the ‘New Formatting Rule’ dialog box, click on the option ‘Use a formula to determine which cells to format’.New Formatting Rule dialog box
  6. Enter the following formula: =A4=$B$1Search and Highlight Matching cells - formula
  7. Click on ‘Format..’ button.Format to highlight the searched cells
  8. Specify the formatting (to highlight cells that match the searched keyword).Specifying the Color to Highlight searched cells
  9. Click OK.

Now type anything in cell B1 and press enter. It will highlight the matching cells in the dataset that contain the keyword in B1.

Search and Highlight Matching cells only

How does this work?

Conditional Formatting gets applied whenever the formula specified in it returns TRUE.

In the above example, we check each cell using the formula =A4=$B$1

Conditional Formatting checks each cell and verifies it the content in the cell is the same as that in cell B1. If it’s the same, the formula returns TRUE and the cell gets highlighted. If it isn’t the same, the formula returns FALSE and nothing happens.

Click here to download the example file and follow along.

Search and Highlight Rows with Matching Data

If you want to highlight the entire row instead of just the matching cells, you can do that by tweaking the formula a little.

Below is an example where the entire row gets highlighted if the product type matches the one in cell B1.

Search and Highlight entire row - dataset

Here are the steps to search and highlight the entire row:

  1. Select the dataset on which you want to apply Conditional Formatting (A4:F19 in this example).
  2. Click the Home tab.
  3. In the Styles group, click on Conditional Formatting.
  4. In the drop-down options, click on New Rule.
  5. In the ‘New Formatting Rule’ dialog box, click on the option ‘Use a formula to determine which cells to format’.
  6. Enter the following formula: =$B4=$B$1Search and Highlight entire row - formula
  7. Click on ‘Format..’ button.
  8. Specify the formatting (to highlight cells that match the searched keyword).
  9. Click OK.

The above steps would search for the specified item in the dataset, and if it finds the matching item, it will highlight the entire row.

Note that this will only check for the item column. If you enter a Sales Rep name here, it will not work. If you want it to work for Sales Rep name, you need to change the formula to =$C4=$B$1

Note: The reason it highlights the entire row and not just the matching cell is that we have used a $ sign before the column reference ($B4). Now when conditional formatting analyzes cells in a row, it checks whether the value in column B of that row is equal to the value in cell B1. So even when it’s analyzing A4 or B4 or C4 and so on, it’s checking for B4 value only (as we have locked column B by using the dollar sign).

You can read more about absolute, relative and mixed references here.

Search and Highlight Rows (based on Partial Match)

In some cases, you may want to highlight rows based on a partial match.

For example, if you have items such as White Board, Green Board, and Gray Board, and you want to highlight all these based on the word Board, then you can do this using the SEARCH function.

Something as shown below:

Search and Highlight partial data - dataset

Here are the steps to do this:

  1. Select the dataset on which you want to apply Conditional Formatting (A4:F19 in this example).
  2. Click the Home tab.
  3. In the Styles group, click on Conditional Formatting.
  4. In the drop-down options, click on New Rule.
  5. In the ‘New Formatting Rule’ dialog box, click on the option ‘Use a formula to determine which cells to format’.
  6. Enter the following formula: =AND($B$1<>””,ISNUMBER(SEARCH($B$1,$B4)))Formula to search for partial matches
  7. Click on ‘Format..’ button.
  8. Specify the formatting (to highlight cells that match the searched keyword).
  9. Click OK.

How does this work?

  • SEARCH function looks for the search string/keyword in all the cells in a row. It returns an error if the search keyword is not found, and returns a number if it finds a match.
  • ISNUMBER function converts the error into FALSE and the numeric values into TRUE.
  • AND function checks for an additional condition – that cell C2 should not be empty.

So now, whenever you type a keyword in cell B1 and press Enter, it highlights all the rows that have the cells that contain that keyword.

Bonus Tip: If you want to make the search case sensitive, use the FIND Function instead of SEARCH.

Click here to download the example file and follow along.

Dynamic Search and Highlight Functionality (Highlights as you type)

Using the same Conditional Formatting tricks covered above, you can also take it a step further and make it dynamic.

For example, you can create a search bar where the matching data gets highlighted as you’re typing in the search bar.

Something as shown below:

Dynamic Search and Highlight in Excel

This can be done using ActiveX controls and can be a good functionality to use when creating reports or dashboards.

Below is a video where I show how to create this:

Did you find this tutorial useful? Let me know your thoughts in the comments section.

You May Also Like the Following Excel Tutorials:

  • Sergio says:

    Sorry men. I do not why, in my case, it does not work. In the bets of the scenarios, it highlight like two cells that they do not have anything to do.

  • EmoCore Zurc says:

    Thank you Sumit.

  • marco alexis maghuyop says:

    Hello, Why don’t =AND($C$2″”, OR(ISNUMBER(SEARCH($C$2, $B5)))) works?Coz I tried that formula so that even if I just input a string or a keyword, it will highlight that particular word, not the entire row. That formula works but if only I try to input the product, but if i try to input the name and geography it wont work.

  • Kiran says:

    Hi Sumit,
    I find Search and Highlight Cells using Conditional Formatting very useful however I tried every other possibility to but doesn’t highlight any data. Same applies with Gantt chart as well I could not format the date schedule. I am using Microsoft office 2007. Hope you can help me out.

    • Χρήστος Φλωροπουλος says:

      Hi , I can’t see the necessity here for OR either, i used =SEARCH(input;$B16&$C16&$D16&$E16&$F16) , where $B16 &…. your range for row to be highlited and “input” the name of the cell that searches, also make sure you select the area you want with Shift+Ctrl before you go to Conditional formating, hope it works for you.

  • Deepak says:

    I didn’t understand why we have used OR functions here –

    =AND($C$2″”,OR($C$2=$B5:$D5))

    • Sumit Bansal says:

      OR formula would highlight the entire row. The first formula showcases how you can highlight matching cells only (in the countries column). I have used the OR formula in the second tab (in the download file), where the intent is to highlight the entire row.

      • Deepak says:

        I understand OR is to highlight the entire row but I did not understand the logic. I mean without OR why it is not working ? -> =AND($C$2″”,$C$2=$B5:$D5)

        • Sumit Bansal says:

          It doesn’t work without OR as $C$2=$B5:$D5 does not return a single TRUE or FALSE. Rather it returns an array of True/False. If you do not use OR, there would always be FALSE in the array, and hence AND would always return FALSE.

  • Deepak says:

    I have a doubt, why C2 is not equal to D5, I mean –> =AND($C$2″”,$C$2=D5) but instead you used =AND($C$2″”,$C$2=B5) which works here.

    Can you please explain ?

    • Sumit Bansal says:

      Hello Deepak.. when we select the entire range (B5:D22) in this case, the formula is applied keeping the active cell in mind. And conditional formatting evaluates each cell based on it. For example, when it is evaluating cell B5, it used the formula =AND($C$2″”,$C$2=B5). When it moves to cell C5, it evaluates it with the formula =AND($C$2″”,$C$2=C5)

  • >