Search a Spreadsheet Full of Data using Conditional Formatting

Posted on March 31st, 2009 in Learn Excel - 25 comments

Many of us use spreadsheets to manage huge lists of data, like customer data bases, salesperson data bases etc.

Today we will learn a little conditional formatting trick that you can use to search a worksheet full of data and highlight the matching cells.
Search a large spreadsheet of data using conditional formatting
First identify which cell you want to use as search bar. Lets say we choose F4.

Now, Select the data cells you want to search and go to conditional formatting.

Formulas in Conditional FormattingWe will write a simple formula that returns true if a cell has the content you typed in the search bar (F4) and false if the cell doesnt. You can try something like ISERROR(FIND(LOWER($F$4),LOWER(B7)))=FALSE.

But there is a problem with this, it returns true when the search bar is empty, and thus you end up highlighting all cells. So we add a further condition that will highlight the matched cells only if the search bar contains some data.

The formula looks like,

=AND($F$4<>"",ISERROR(FIND(LOWER($F$4),LOWER(B7)))=FALSE)

Finally set the formatting you want to use. I choose dull orange color. You can choose blue, green or pink too.

Hit ok and you are good to go.

Additional Material on Conditional Formatting:
Excel Conditional Formatting Basics
Highlight Top 10 Items in a List using Conditional Formatting
5 Rock Star Conditional Formatting Tips

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

25 Responses to “Search a Spreadsheet Full of Data using Conditional Formatting”

  1. al m3tasem says:

    great tip with Conditional formatting

    why we use LOWER(B7) in the formula ?

  2. azmat says:

    Al m3 - this is to avoid any mismatch due to Case difference in value we are searching and related match.....

  3. Jon Peltier says:

    Neat trick. I need to remember it.

  4. The.Q says:

    This is a great trick, which I wouldn't have extrapolated from conditional formatting at all. Jeez, with all the great tips I'm getting from you, people are starting to think I'm an excel expert!!

  5. Sumeet says:

    Chandoo, I want this to work in a negative way. It should highlight all the cells which does not contain the search text. How can we do this?

  6. Chandoo says:

    @Al: I used lower so that you can do case insensitive search as Azmat clarified...

    @Azmat... Super, donut to you

    @Jon.. thanks 🙂

    @The.Q: that is the purpose of this blog. "excel and charting tips for YOUR success"

    @Sumeet: Of course you can. Just change the formula to, =AND($F$4<>"",ISERROR(FIND(LOWER($F$4),LOWER(B7)))) and you have it.

    • Chandoo says:

      @Sumeet.. the formula should have been... =AND($C5>=LARGE($C$5:$C$34,10), NOT(AND(IF( COUNT(IF($C$5:$C5= LARGE($C$5:$C$34,10),1,""))>1,1,0) , $C5=LARGE($C$5:$C$34,10))))

  7. Wilhalm says:

    I've tried this on a couple of sheets and have failed to get it working (Ashamedly) is there an example for this tutorial?

    Great site by the way, i've learnt a lot from it. Keep up the good work

  8. Sumeet says:

    Hi Chandoo, I have a spreadsheet with some addresses. The first row has name, second has address and third has country. I want to separate each record with a color ie. first 3 rows should show one color, then next 3 rows (second record) should show a different background color and so on. How can we do this.

  9. paresh says:

    Hi Chandoo,

    In excel 2007, the objective can be achieved [ not highlighted of course] by filtering and using filter - text filter -contains. The more I read about excel 2007, the more impressed I get..

  10. Denise says:

    Hello Mr PHD
    I also cannot get this to work
    Any chance of posting a sample xls file please?? :-)))
    Many Thanks Mr Chandoo
    denise

  11. Chandoo says:

    @Wilhalm & Denise: Please download the sample file from : http://cid-b663e096d6c08c74.skydrive.live.com/self.aspx/Public/customer%20search%20CF.xls

    Since this is a fairly simple one I didnt upload a file. But now I do... so go ahead and use it to learn this better.

    @Sumeet: You want the zebra stripes.. 🙂 you can do them using conditional formatting. in your case, the formula can be, =mod(int(row()/3),2)=0
    This will return true for 1,2,3,7,8,9,13,14,15 etc and false for 4,5,6,10,11,12 etc. thus giving you the zebra stripes...

    how does the formula work... ? well, that is your home work.. here are some pointers... to help you get started:

    http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/
    http://chandoo.org/excel-formulas/int.html
    http://chandoo.org/excel-formulas/mod.html
    http://chandoo.org/excel-formulas/row.html

    Let me know if you need more help...

  12. Denise says:

    Thank you Mr PHD. Appreciate it.
    Denise

  13. Dave says:

    Sorry - I didn't mention that I would like to hi-light the members results in the the main results spreadsheet, so that they can see their positions and compare with others, and not make a seperate list. Many thanks 🙂

  14. Chandoo says:

    @Dave.. did you leave another comment earlier.. I dont see it. May be WP ate it.

    You can use conditional formatting to check conditions based on data on another sheet. For this first you must define a named range for the data on the other sheet. Once such named range is defined, you just need to refer to the named range in the main sheet's conditional formatting formula. I am sure you can figure out how to write the formula.

    Let me know if you face any difficulty.

  15. sinfonian0294 says:

    Chandoo:

    Thanks for this great tip! I'd like to expand this to be able to highlight where two separate fields contain their respective search texts.

    For example, I have a long list of names (First, Middle, Last in B, C, D respectively). I've set up conditional formatting on both columns B and D so that a search for First Name shows in column B and a search for Last Name shows in column D. The trouble is, with a common name, I'd like to see hits where both fields match. Is there a way to extend this conditional formatting so that if both search boxes contain text, only those rows where both B and D match the search text could be displayed in a different color? (I hope this explanation makes sense).

    Thank you for your great blog and forums. Every week my colleagues and I discuss what we've learned from you!

  16. sinfonian0294 says:

    Actually, I think I figured it out!

    I simple combined the two AND() formulae as the conditions of one big AND() formula. After I got the correct number of parentheses in place, it works like a dream!

    Thanks again for all your great work on this website!

  17. UMARGUL says:

    HELLO DEAR,

    I tried to use this conditional formating tip for as a search engine but it highlights the below cell instead of the cell containing that text.

    and i also want to know that is there any formula for convertng integers into text instead of this one
    =BAHTTEXT("587") it results in baht language

    Ok dear
    Khuda Hafiz - Take care

    Umar Siddiqui

  18. bill says:

    Is there a way to use conditional format on a cell so that when someone enters a "X" the cell turns dark blue and inserts a .5?

    thanks

    • Chandoo says:

      @Bill.. you need formulas, not CF alone. Assuming your data (Xs) is in A1:A10, in B1 write =if(a1="X",0.5,"") and fill down to B10.

      Once you are done, select B1:B10 and then go to CF > add rule. Specify condition as =B1=0.5 and set blue color as condition.

  19. ashish says:

    i want change the spreadsheet background by using the if else formula the logic is if cell1 has value so the background will be red if cell1 is blank so background will be green so how can is possible tell me?

  20. Excellent tip! Thank you 🙂

    I have recorded a video to show how this tip works.

    I hope you find it useful! 

    http://youtu.be/eM9_kSSYLVo 

  21. [...] video and post were inspired by the excellent article ‘Search with Conditional Formatting‘ by [...]

  22. chern says:

    Hi Chandoo,

    Thanks for the great tutorial.

    Can I please ask, why do we use the "" sign in the formula?

    Thanks!

    Regards,
    Chern

Leave a Reply