Google Sheets - Build Your Own Search Box

[@goombah_]

Is there any way to make data appear un-highlighted until the search function is used?

[@oxpey4473]

Following the example of the video you have to put and AND condition on your formula, just only checks if the search box "IS TEXT" it will be drop False when is empty... so it works!!! Here u have the form: =AND(SEARCH($F$3,$C2)>0,ISTEXT($F$3))

[@Maidenas05]

Hello !! That is perfect well done ! How can we change , when the SearchBox is empty not to highlight everyting ?

[@EamonnCottrell]

You can do this with and and statement so that it checks if F3 isn't blank too =AND(SEARCH($F$3,$C2)>0,$F$3"")

[@xoterra_space]

Thank you for very helpful video! But what should I do if I want to leave the cell not in highlight when search box is empty?

[@CurranOC]

1. Create another conditional formatting rule and place it before the SEARCH() function's conditional formating. You can drag and drop by using the 4 dots on the left of each conditional format rule. 2. Apply to the same range you are using in the SEARCH() function. 3. Custom formula is... "=ISBLANK($B$1)" . Replace $B$1 with whichever cell your search box is located in. 4. Set formatting style to None or White.

[@donk73]

@@CurranOC Thanks for that additional function and for continuing to support your tutorial even though it's several years old.

[@saasaaluv]

thanks for the guideline! Its very easy to understand and helpful

[@ProlificOaktree]

Glad to hear that!

[@AdithyaShreshti]

This is brilliant, can we do something like this to find different tabs available in one google sheet? I don’t want to get into searching the content in each tabs though

[@juantapia3351]

I've been searching for something similar for weeks. No luck! Please let me know if you're able to find something along these lines.

[@asfandyarmalik8740]

Amazing Thank you!!!

[@ProlificOaktree]

You're welcome!

[@marksawesomeadventures]

This is Click Bait this is NOT a search Box! If you have an Inventory of a Thousand items just "Highlighting" the item ain't gonna make it.

[@user-uy4qb7lt6b]

Hi, This is perfect thank you, is there anyway to get rid of the extra highlighting when there isn't anything in the search box?

[@donk73]

I have the same question. If the search box is empty, the entire search field is highlighted

[@jj000999able]

Hi sir. Thank you for the tutorial. Have one doubt. Can you plz tell how to search multiple text or number in the search box?

[@jonlawrance8383]

Great video, helped me create a search box that works so thanks! The only problem now is that I have a large sheet with thousands of entries and the search doesn't automatically scroll down to the result so I'm scrolling manually looking for the highlighted item. How can I make it scroll to the result automatically?

[@ProlificOaktree]

Maybe go back to using the control F key combination.

[@jonlawrance8383]

@@ProlificOaktree ah maybe that would be better in that case, shame there isn't a way to display the results as it's satisfying to make something work, but thanks for your help and reply!

[@mobi47]

search box with vlookup

[@2trailertrucker983]

@@mobi47 Do you know how to set that up?

[@mobi47]

@@2trailertrucker983 yes its easy email me

[@vianca811]

Thanks for this!! It's very clear and easy to follow!

[@ProlificOaktree]

You’re welcome. Glad it helped.

[@JDeWittDIY]

Thanks for the video, works great! Is it possible to hide rows that do not match the search text?

[@ProlificOaktree]

I don’t think so

[@JDeWittDIY]

@@ProlificOaktree That's a bummer. Thanks for the reply though!

[@agoogleuser6937]

Yes with a GAS that creates a filter.

[@JDeWittDIY]

@@agoogleuser6937 Thanks. Any more details on how to go about that? What is a GAS?

[@sakmberjoshi978]

THANK YOU SO MUCH SIR REALLY THANKFUL FOR THE INFORMATION !

[@ProlificOaktree]

Welcome!

[@bmmusic]

thank you. needed this.

[@ProlificOaktree]

Glad it was helpful!

[@amagdenovski]

Very helpful, thank you!

[@ProlificOaktree]

You're welcome!

[@slimax2842]

thanks you sir

[@ProlificOaktree]

You're very welcome.

[@larocque3600]

I was able to get this to work, but it only searches one column. Which is fine. I’m wanting a second box to search the second column, but using the same formula and altering it to match my parameters is not working. Suggestions??

[@reading265days2]

Hey! I'm trying to make a search bar for a homemade book catalog. Every time I do this though only some bars gets highlighted, and when I search it gives me a book two bars above the actual thing I am searching for. Is there a way to fix this?

[@varuncd1096]

But, it does not filter the data with the same name. In a big raw data, we might need that. Please suggest something.

[@TheBookTeller]

Thank you

[@ProlificOaktree]

You're welcome

[@shitallohakare9328]

Wow that's nice, Thank you Sir.

[@ProlificOaktree]

You are most welcome

[@loussaiefraouf3252]

please what is the formula to make it search in all of the columns not only in the name column? For example, I want to search with names maybe date maybe amount

[@daklaker2287]

Thank you master!! Would you pls show me how to indicating just only row having data that we search, so how could i do that? Thank you!!

[@brazzilianboy]

Great content, but I have one question, there is any way to put some text placeholder like "type to search" in the search box?

[@Kumarmullackal]

Thanks a lot. It helped me out !!

[@ProlificOaktree]

Glad it helped!

[@123-o5e]

Hello. This is very helpful!! Can we also search for data from a range of cells ? ex. C2 to M1000

[@KwinDecipulo]

THANKS!

[@hamjalalsarker1241]

Very helpful

[@ProlificOaktree]

Glad you think so!

[@nature_through_my_lens]

Can I add this search box on my google site and it directly shows the result based on the conditional formatting?

[@034242024]

thank you , you help me you great

[@ianmccusker8092]

Hi Adam, Enjoyed the tutorial thanks. I'm not a coder or anything but I was trying to create a search box facility on my website (like google search) that using Google Sheets could link to an external website that will have over 400 pages. The way I want it to work is the person would type their name in the box and if it was on the Google sheet it would appear in the box and once they clicked on it it would take them to that particular web page. Just wondered if you were able to point me in the direction of where I can find a video where someone explains how to do this. Thanks for your help in advance.

[@ConsulthinkProgrammer]

Hmm, use Apps Script and build some webApp to recall the particular data from Google Sheets.

[@OurTayloredHome]

Thanks for the video. Very helpful. How would I need to change the formula if I wanted to search the text of multiple columns instead of just one like in this example?

[@OurTayloredHome]

Wait! I figured it out. In case you are wondering... =SEARCH($A$2,$B2&$C2&$E2)>0 My search box is in A2 and the fields it searches are column B, C, and E starting on row 2.

[@hannah51238]

@@OurTayloredHome Legend, ty!

[@hannah51238]

The forumla just highlights everything. Do we need to change the '0' to a letter value?

[@splatterdaynightmares]

Is there a way to do all of this by creating a front end using Google forms? I'm trying to find a cheap, decent database that has a great front end but nothing compares to filemakre pro. I tried the form but it only lets me back in with a link or the spreadsheet itself which is quite infuriating because I'm not sending it to others, it's only for myself. Thanks

[@carrietam6121]

I followed your tutorial but why is it not working for my google sheets?

[@malin380jey]

can we get a search box to find multiple names at the same time using same box ?

[@prashantcreative5901]

👍.......but how to convert image table into Google sheets

[@KeraniganjDhaka]

Any way to add search option in google form?

[@daklaker2287]

i cant lock highlight the collumns, can you explain me clearly sir?

[@melvinrsmith]

This is great and I've got it to work. I must be missing something but when I go back to edit the conditional formula, it doesn't show. It just shows a blank one and no others. Is it hidden somehow? If I want to change it, how do I do it without retyping? Lastly, could I use this formula to search across multiple sheets if the spreadsheet has them? If so, how? Thanks!

[@ProlificOaktree]

Make sure you highlight the range that is getting the highlighting applied. If you do that first, the conditional formatting rule should show up.

[@melvinrsmith]

Oh, and when I use this search method, the cursor drops to the cell below the search box. Is there a way to move the cursor back up to the search box so I don't have to move it back up manually each time?

[@ProlificOaktree]

Not that I know of

[@tcz1713]

This was super helpful, thank you!

[@ProlificOaktree]

You're so welcome!

[@mustakimali836]

Tnx

[@ProlificOaktree]

YW

[@claudiacolati8810]

Hi! Love the videos, they are super helpful! I am currently having difficulty with texts moving from their spot whenever I write something above it on Google docs. I have currently began writing a novel and made a table of contents with bookmarks and links to go along with it (with the help of your videos!). I have placed this table of contents as the first page to the document, which will be regularly updated as I write new chapters and add each one to the table of contents. My problem is that because the TOC starts as the first page and the "first page" to chapter 1 starts on the next page, how do I prevent that first page of chapter 1 from moving down every time I add to the TOC? I've tried searching the internet, mostly people suggesting I do a page break, but every time I try using it, it just doesn't seem to work for me (I'm probably just doing it wrong). Thanks in advance for the advice!

[@ProlificOaktree]

Thank you, I'm glad you have enjoyed my videos. You may want to try a Section Break. See how they can work with custom page numbering here: kzbin.info/www/bejne/nHXUnmdvlMd3bK8

[@anitabisht547]

It is not working it just moves up and down to different rows but does not find the correct one. Please help sir

[@joefromdc]

Only isssue I have is how to highlight the box for results and not when the box is empty

[@StaceysDogGrooming]

is there a way to have a search box on one page, when you search it looks in page 2, but then displays the results on page one?

[@demosss5717]

You can do this by using queries

[@syaifulkhusna7299]

the formula's is not valid, why?

[@rishalchand9510]

He can we make it possible, if i share this sheet with many people and i only want each person to see their pay only, based on the email they sign in maybe? Or how so others don't enter other employees name and see their pay

[@ProlificOaktree]

That doesn't sound like something that I would try to do in one spreadsheet.

[@rishalchand9510]

@@ProlificOaktree Please do, looking forward for the video

[@andreanicagelacio5202]

How to remove the background color and just highlight the one that I search for?

[@reasonerkevin]

I poked around for the same thing and found this to work: =IF ($B$1>$C$1,SEARCH($B$1,$A4)) It's sloppy and requires that C1 block be empty but it does the trick. Basically If your cell has a greater value than the empty cell next to it, do the search. So as the two fields when blank are equal, it won't do the search until you put a value in the field. Hopefully someone will come along and improve this as I'm just learning myself and I'm sure there's a better way.

[@oxpey4473]

Following the example of the video you have to put and AND condition on your formula, just only checks if the search box "IS TEXT" it will be drop False when is empty... so it works!!! Here u have the form: =AND(SEARCH($F$3,$C2)>0,ISTEXT($F$3))

[@vianca811]

I have a question! When you delete what's written on the search box, all of the range will still be highlighted right? Then when you do type something, it will just highlight what you've written. Is there a way that nothing will be highlighted until I type something in the search box? Like it will remain its original color.

[@ProlificOaktree]

That's a great question. I can't think of any way to do that off the top of my head.

[@ConsulthinkProgrammer]

Use REGEX and IF function to do that

[@titunbandhu7174]

=IF(ISBLANK($F$3)," ",SEARCH($F$3,$C2)>0)

[@Tanorov]

@@titunbandhu7174 Cheers dude! Saved me so much time!

[@oxpey4473]

Following the example of the video you have to put and AND condition on your formula, just only checks if the search box "IS TEXT" it will be drop False when is empty... so it works!!! Here u have the form: =AND(SEARCH($F$3,$C2)>0,ISTEXT($F$3))

[@gokum7955]

Second view!!!! Thank you for the info teacher!!

[@ProlificOaktree]

Welcome!

[@NDK0]

It just says the formula is invalid

[@stevensousa5622]

Awesome but in my case the key cell (F3 in your example) will be empty most of the time. How do I not let it highlight everything when its in its empty state .

[@andya6021]

Here's my solution: Create 2 key cells. Key Cell #1 (F3) is for user input, Key Cell #2 (F4) is the one used by conditional formatting. In Key Cell #2 insert the formula (=IF(F3=0,"@",F3). This means if the input cell (Key cell #1) is empty, Key Cell #2 will be "@" and therefore not trigger the formatting. When the user types a search query into Key Cell #1, Key Cell #2 will mirror it and trigger conditional formatting as intended. (You can use any symbol you want, as long as it won't trigger formatting). You can completely hide Key Cell #2 by changing the font to white

[@collaredkeeper]

@@andya6021 This is the correct Answer to alot of people's questions regarding removing highlighting! Nice Idea.

[@oxpey4473]

Following the example of the video you have to put and AND condition on your formula, just only checks if the search box "IS TEXT" it will be drop False when is empty... so it works!!! Here u have the form: =AND(SEARCH($F$3,$C2)>0,ISTEXT($F$3))

[@tsuxcreative7335]

@@andya6021 Thanks Andy! Believe it or not I've been trying to get rid of the highlight for like 45 min now. lol

[@beastrider4839]

THIS SEARCH BOX IS SEARCHING THE CONTACT NUMBER???

[@ragibnoorantor417]

just press ctrl+f, and search

[@zubairahmad9256]

How can can you tell me plz

[@damodarbangalore7003]

First view and first Comment 👍

[@ProlificOaktree]

🎉

[@selvarajandiappa3143]

Hi.. Just to check.. can you help us set up..we give you the fields and requirements..we will be happy to pay you for the services

[@ProlificOaktree]

Thanks for asking, I appreciate the compliment. I don't do individual consulting as my focus is creating content.

[@syaifulkhusna7299]

help please