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.
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.
We 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
25 Responses to “Search a Spreadsheet Full of Data using Conditional Formatting”
great tip with Conditional formatting
why we use LOWER(B7) in the formula ?
Al m3 - this is to avoid any mismatch due to Case difference in value we are searching and related match.....
Neat trick. I need to remember it.
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!!
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?
@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.
@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))))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
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.
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..
Hello Mr PHD
I also cannot get this to work
Any chance of posting a sample xls file please?? :-)))
Many Thanks Mr Chandoo
denise
@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...
Thank you Mr PHD. Appreciate it.
Denise
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 🙂
@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.
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!
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!
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
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
@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.
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?
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
Apologies, link to wrong video upload.
Correct one is http://youtu.be/sm2Gp7fJ4VE
[...] video and post were inspired by the excellent article ‘Search with Conditional Formatting‘ by [...]
Hi Chandoo,
Thanks for the great tutorial.
Can I please ask, why do we use the "" sign in the formula?
Thanks!
Regards,
Chern