How to Create a Highlighting Search Box in Excel 2016

In this HowTech written tutorial, we’re going to show you how to create a highlighting search box in Excel 2016.

Join us on Facebook
Our App

Create the search box cell and optionally edit some characteristics.

Under the Home tab, open the Conditional Formatting drop-down and click New Rule.

Choose to use a formula to determine which cells to format. Use the SEARCH formula. The first argument will be a reference to the search box, then the columns of data. Use $A2 for example to start at A2 and down the column, and use & to concatenate another column on. “=SEARCH($J$3, $A2&B2&$C2)” for example.

Click Format and choose a style.

The search box should be working, but when it’s empty it triggers all the cells. To remedy this, go back to Conditional Formatting and click Manage Rules.

Go under This Worksheet and click Edit Rule.

Use an IF statement: “=IF(ISBLANK($J$3, 0, SEARCH(…)))”. This will return 0 if the cell is blank and return the SEARCH() formula if it isn’t.

This is a HowTech tutorial, visit our website and watch our videos for more.

[howtechoffice]

❗ Subscribe To Our Main YouTube Channel

[SweetBudgetPlanner]

Thank you for this tutorial, This was the most helpful I have found after searching for 2 days to find a Search Box that would highlight the search results. I also need the search box to GO TO the Next, then next, etx.

[EducacionsinFronteras]

Thank you so much for adding the formula on the description. You rock!

[Funkiztah]

Thank you!!! Saved me a lot of time! Straight to the point tutorial. Very short but very informative as always.

[mirarif]

Yes, it does work. Thank you Teacher. But I wish I could get the result on top row because I don't want to scroll to down.
Provide me such formulas if you know. Thanks again :)

[rauljimenez]

This solution is genius!!! I was looking for a video I had seen on how to do this. Is was super complicated, full of work-arounds. Thank you very much!!!

[cattleman]

Thank you, what a great video. Just did it and it will be one of my goto formulas from now on. Have a very great day today!

[HisChiveliness]

This is an excellent tutorial. This method will save me boatloads of time. Thank you.

[Jojosmith]

Thank you very much for the useful lesson. Is it possible to have a search of the form the pull the data from the other sheet & display it on the form (the workbook that has 2 different sheets: Form sheet, Database sheet? thanks

[WittayaThanasongtrakul]

good method. clearly and easy understanding. Thanks

[DestroyForgeEmerge]

I have a request if you don't mind. First thank you for this amazing feature it really helps finding data. I want to know what formula/function you can add to this highlighting feature to only show the highlighted rows and making the un-highlighted information disappear

[mwhitaker]

Love the formula. One question. I like how it finds and highlights them but is there a way to make it move to it on the spreadsheet. I have a very long spreadsheet and it would be nice if it went right to it like it does when you hit ctrl f

[dannywhite]

This was extremely helpful, and I have used it a number of times successfully, so thank you for your tutorial! However, in a recent spreadsheet I have found that the whole row is not being highlighted; the highlighting appears only in the last 6 columns (of 19) for the particular row where is finds the text/value. Any idea why this could be?

[mgtrn]

Hi, very informative this.

But, is there any way to keep the searched value/cell highlighted when searching for another value? Like in way it's checked off somehow. Thanks

[roysosa]

love the video, could not understan the last part, formula blurry, can you write it down please

[shaukatsamad]

Thnx, it is very working perfectly, i doing my job exactly what u say to do it for me, the question is this I like to jump cursor again in the same place where i have written text to find the text or any article I mean in the same cell... I take the cursor by hand ... for typing text or article.... pls tell me the formula

[RobertoRodriguez-dezz]

How can I leave that results stay highlighted? I'd like to have the highlighted serve as a kind of check off list

[PesterBotLoFi]

Very helpful.. I couldnt figure out how to get rid of it being completely highlighted.

[Behrouzboroomand]

Thanks a lot but I couldn't finish it as the writing & resolution was so small to read. Would you be kind enough to type down just the final command here? Thank

[anthonylillie]

Hey, nice video. I have a question if anyone might have an answer. No matter what I search using this method, it always highlights the row directly beneath whatever it's reading. Anyone know how to fix?