Create a Search Box in Excel without VBA

Is it possible to create a search box in Excel, without using VBA?

Yes, and it’s easy!

We will use Conditional Formatting to highlight the fields that match the search string. For example, if you look at the table below, we want to highlight row 8, 11, 15 and 25 if we search for “RG”, because “RG” is part of the product name in those rows.

EasyExcel_8_1

The secret is the SEARCH function. The SEARCH function looks for text within a text and returns its position. For example, the formula =SEARCH(“RD”,A5) will return 1, because it finds “RD” in the first position in the text in A5. If we search for “XX” instead, it would return #VALUE.


We will use Conditional Formatting to highlight the search results. Conditional Formatting needs a TRUE or FALSE to determine whether or not to apply the formatting. The SEARCH function, however, returns a number if it finds what we are looking for, so we have to put it inside the ISNUMBER function. That will give us a TRUE if the result of the SEARCH formula is a number:

=ISNUMBER(SEARCH($C$2,A5))

Select the range you want to include in the search and click on Conditional Formatting from the Home ribbon and choose New Rule (or shortcut: Alt > H > L > N). Choose “Use a formula to determine which cells to format” and type the formula in the formula field.

EasyExcel_8_2

OK, let’s try it and see if it works: If we type “RG” in the search field, all products with “RG” in the name will be highlighted! And it works with numbers too!

EasyExcel_8_3

There is one problem with this formula, though. If you leave the search field empty, all cells will be highlighted. Here’s the workaround:

We use the AND function to add the additional criterion; that the search field must be empty for the function to be true. The syntax for “not empty” is <>“” (<> means not equal, and “” (2 double quotes) means empty or null). This is the formula we can use in Conditional Formatting to make it perfect:

=AND(ISNUMBER(SEARCH($C$2,A5)),$C$2<>“”)

Note: If you use comma as the decimal separator as default (applies to most non-English users) you have to replace the commas in the formulas with semicolons.

Read more useful Excel tricks here:


__________________________________________________________________________

Bonus tip 1

How can we find out how many matches we got? Try this formula:

=SUM(–(ISNUMBER(SEARCH($C$2,$A$5:$A$54))))

This is an array formula, so you have to close it with Ctrl+Shift+Enter

Bonus tip 2

Is it possible to create a list of results? Yes, but it’s not easy, so I’ll just give you the formula:

=IFERROR( IF( ROWS( $F5:F5)=SUM( –( ISNUMBER( SEARCH( $C$2,$A$5:$A$54)))),INDEX( $A$5:$A$54,SMALL( IF( ISNUMBER( SEARCH( $C$2,$A$5:$A$54)),ROW( $A$5:$A$54)-ROW( $A$5)+1),ROWS( F$5:F5))),”"),”")

(close with Ctrl+Shift+Enter and copy down)

__________________________________________________________________________

Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.

Did you enjoy this article? Get free email updates! Enter your email address here:

Delivered by FeedBurner

Clip to Evernote

15 thoughts on “Create a Search Box in Excel without VBA

  1. Tried it several times and I’ve no idea where I’m going wrong but it doesn’t work??

    Running office 2007 and even tried replicating your exact example and still nothing?

  2. Thanks for the tips. :) But mine was still highlighted everything if the search box is left with nothing. I did double-checked the formula but still cannot find what fault it was.

  3. Not working for me either, and I copied and pasted the formulas. All cells are still highlighted when my search bar is empty, even after I add the second formula.

  4. Thanks for this useful tip. When search item is empty this was formatting the cell, so i found a new solution. Instead of =ISNUMBER(SEARCH($C$2,A5)) , I used =ISNUMBER(IF($C$2″”,SEARCH($C$2,A5),”")). And this worked fine.

  5. Thank you so much for your helpful tips! I can not get the list of results to work. Do you think you would be able to help me out, please?

  6. Pingback: /745

  7. Don’t waste your time this guys formula clearly doesn’t work.
    Use this instead for your conditional format.
    =IF(ISBLANK(Search_Box),0,SEARCH(Search_Box,$A7&$B7&$C7&$D7&$E7&$F7&$G7&$H7&$I7))

    Adjust Inputs and Outputs to your own spreadsheets.
    Search_Box = name of the cell I used as a search box
    $A7-$I7 are the target columns for my search starting with row 7.
    make sure you select your target data, or cells, before applying the condition.
    Good Luck.

Leave a Reply

Your email address will not be published. Required fields are marked *


9 − eight =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>