Excel| how to highlight the search word

Question

Hello i would like to ask on how to highlight the word being searched on excel. thanks

Answer 1

Hi,

One way you can achieve this is by using a formula in the conditional formatting of Excel to highlight the results of entry that you searched. Below are the steps we took on our end to create a search box in Excel and have the items matching the search entry highlighted:

1. Select the table or range containing the data that will be linked to the search box, then select Conditional Formatting > New Rule under the Home tab. (See screenshot below.)

2. When the New Formatting Rule dialog box pops up, you need to:

2.1) Select the option "Use a formula to determine which cells to format" option in the "Select a Rule Type" box;

2.2) Enter the formula =ISNUMBER(SEARCH($B$2,A5)) into the "Format values where this formula is true" box;

2.3) Click the Format button to specify a highlighted color for the searched value;

2.4) Click the OK button. (See screenshot below for sample data range used in the formula.)

Note:

• In the formula, $B$2 is a blank cell you need to use it as a search box, and A5 is the first cell of your selected range you need to search values within. Please change them as you need.
• The formula is not case-sensitive.

Now that the search box is created, when you type search criteria into the search box in cell B2 and press the enter key, all matched values in the specified range are searched out and highlighted immediately as below screenshot shown.

If you would like me to provide additional advice, simply respond to this thread. I will get notified and I’ll reply to you as soon as I can.