How to Create a Search Bar in Google Sheets
ฝัง
- เผยแพร่เมื่อ 10 ก.ค. 2024
- In this video, I show you how to create a search bar to filter your data in Google Sheets.
All it takes is one formula, and you can type in your search terms and get back only those records that contain the search text. I explain everything from the setup to how the formula works.
Check it out. Learn something new. And go out and impress your friends and coworkers.
PRACTICE FILE
Download the practice file to follow along:
docs.google.com/spreadsheets/...
RELATED ARTICLE
Check out the post below where I go through all three formulas you learn about in this video.
spreadsheetlife.com/create-a-...
RESOURCES
- Free Spreadsheet Templates: spreadsheetlife.com/free-temp...
- The Spreadsheet Life Blog: spreadsheetlife.com/blog/
LEARN EXCEL OR GOOGLE SHEETS
- Excel Total Course: spreadsheetlife.com/excel-tot...
- Google Sheets Masterclass: Coming soon...
TIMESTAMPS
00:00 - Intro
00:20 - Setup
01:26 - Formula
02:42 - Explanation
03:45 - How to Sort the Results
04:34 - Conclusion
05:05 - Outro
Dayummm!! Bro make more of these.... in exactly same simplified way!! too good.
You got it! More videos like this coming in the very near future for sure!!
I love your videos. They are clear and helpful. Thanks for sharing.
Thank you for your kind words. I'm so glad to hear you find my videos clear and helpful! It really makes my day!
Thank you for this video clip of yours, this is very helpful, and with the sample link that is very generous of yours
Super happy to help! 😄👍
Wow.... this is amazing..... Thank you, Sir!...
You're very welcome!
thank you..this is so helpful
So glad that you found this helpful!
Thanks
Halp full tutorial.
Need more explanation for accounting and generate reports for construction companies.
If you have specific questions, you are welcome to send me an email and I will answer the best that I can.
Thank you!
You're very welcome!
thanks bro very useful tricks , ( pro explanation , pro work) ,
🎉new better filter method 🎉 ,
Thanks so much for the feedback! So awesome to hear you've found it useful!!
😄🎉🎊
Thank you very much...
You're very welcome! 😊👍
thank you very much
So glad this could help!! 😊👍
Great work, you are superb
Thank you so much! So glad you enjoyed the video! 😄👍
Thank you
You're welcome!! 😊
i love u thank u so much
You're very welcome! 😄👍
Is there a way to edit the populated information from your original search and have it be updated back in its original cell?
Also, I found your video to be very helpful! Thank you!
I'm so glad you found the video helpful.
Unfortunately, you cannot edit the results generated by the FILTER function. You would only be able to make changes to the data via the original source data. I'm sorry that's the case.
This is a great tutorial! I'd like to know if there's a way for this search to be used by multiple people at the same time without overwriting each other (viewing each others' results). I.e., I'd like each user to see an instance of the Sheet with only their results showing.
Thank you so much! And what a fascinating question!
I don't think there is any easy way to accomplish the task you have described using the method I demonstrated in the video. However, I highly encourage you to check out the Filter Views feature available in Google Sheets. This feature seems like it would fit your situation much better. Check out the link below, and scroll to the section about Filter Views. support.google.com/docs/answer/3540681?hl=en&sjid=218351776827139505-NA#null&zippy=
I hope this helps!
@@spreadsheetlife Yes, thanks for the reply. I saw that option, but I was really looking for something that was more like a searchable database (where I wouldn't need to explain the steps to other users). I appreciate the reply! Your videos are awesome because they are so clear (and cover great topics).
Understood. I'm sorry I wasn't able to provide the solution you were looking for. But thank you so much for your kind words, and I'll do my best to continue creating clear and relevant content.
Nice video! I was using drop downs for the non-search field column, but looks like drop downs do not show.
Thank you!
I'm a little confused about your question. Could you please clarify?
Is it possible, to make it apply any changes made in the search field onto the source data?
Unfortunately that is not possible with the method shown in this video.
Hi! This has been the most comprehensive video I've seen regarding search bar in Google Sheet. I was trying to do the same thing however my data set is on a different tab and I keep getting the mismatched range error. Any idea what could be causing the problem?
It's hard to diagnose the problem without context. You are welcome to email me screenshots or share the file. info@spreadsheetlife.com
Thank you so much for this video! You are an expert at making complicated things simple for non techy people! Is there any way to not only search multiple columns, but to also make the result be only the date in that row and the one description cell that meets the criteria? Not the entire row.
Thank you so much! That is quite a compliment. You really made my day.
So just to clarify your question, you would like to know how to search both the date and description columns, and then return a result that is only the date and the description with no other columns returned. Is that correct?
Yes, you are correct. I have tried repeating part of the function with the "+", but can't quite seam to make the formula functional.
Ok, got it. This one was tricky, but I have a solution for you. Please see the spreadsheet below.
docs.google.com/spreadsheets/d/1lRtIx9BCi3LbyNcyAhaqtSOS8KvfLBl6-csPJsS0RVU/copy
The CHOOSECOLS function allows you to select which columns you want from the original data set. Then the second argument of the FILTER function filters by date. And the last argument is setup like I demonstrate in the video to search the description column for the search text.
I hope this helps.
This is a great help. Is there anyway of being able to search for a range of values from the same data set? In other words more than one of your descriptions?
Do you mean, is there a way to search for values in multiple columns instead of just one column? If so, see this spreadsheet. The formula looks in both the Description and Notes column.
docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy
@@spreadsheetlife I was not the one to ask the question, but this is exactly what I was looking for, thank you!
Awesome! I'm so glad that this was what you were looking for!
Hi! Id like to know is there any way to count how many links are in the column? Thanks!
For example, i named a cell "Present" with a link inside. Can i count that?
To count cells with hyperlinks, you will probably need to implement a VBA solution. If you'd like to explore some options, it would be easier for me to help via email. You can message me at info@spreadsheetlife.com.
How would you do this is the search was a sheet itself and you multiple other sheets with data?
To clarify your question, are you asking how you would pull this off in the case that your source data is scattered across several different worksheets?
Hi, thanks for this! Really helps a lot. Just one question, is there a formula if i want to add more columns to filter? Thanks in advance!
I'm so glad you found this helpful! To answer your question, yes. Take a look at this. It might help.
docs.google.com/spreadsheets/d/166QpQrdxqtMFU9KjzE0h8_GTpEJxB8C6afZzOeHjmJ4/copy
It works!! Thanks a lot!
Awesome! I'm so glad it worked!
If I wanted to have nothing display when the search box is empty, how would I do that?
Wrap the formula inside of the IF function. Test if the search bar cell is empty, and return a double quotation mark with no space in between if TRUE. Otherwise, return the rest of the formula. For example, the full formula would look something like this:
=IF(C2="","",FILTER(F5:G18,ISNUMBER(SEARCH(C2,G5:G18))))
Hi! Thank you for your video. What is the formula if we just want the values to show only when we search it? Because when I follow your instructions, it shows all of the values first then when you search something on the search button that's when the time it will show only the one you typed in.
Just to make sure I understand, are you asking how to show no results when nothing is typed into the search bar?
Yes, that is correct.@@spreadsheetlife
Take a look at this. I modified the formula to show no results when nothing is typed into the search bar. I hope this helps.
docs.google.com/spreadsheets/d/1UBg8LrbM1O8p1MjEam3oLPd3uv69MdarK1zf0eFHHUg/copy
Is there any way to retain the formatting of the results? I'm using this for a listing of videos with links, and it wipes out the links and any text formatting. Thanks!
Unfortunately, using this method will not retain the formatting and links. I'm sorry that is the case. To retain formatting and links, it would be better to select the source data and create a filter by going to Data > Create a Filter. I hope this helps.
hey! thanks for the explanation (is similar to what i was working on) but i have a question, is it possible to search within the 2 columns? i mean, not just the "description" but also into the "date"?
i´ll be really thankful if you can help me with this; you already won a new subscriber! cheers!
Check this out! The formula searches two columns. Let me know if this answers your question!
docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy
@@spreadsheetlife it actually does, thank you very much! (I ended up asking to chatgpt and it was basically the same idea), so thanks for your time :)
Happy to help! 😄👍
Hi can I use "&" to search more column not just the "Description". Example, I want to search the date
Check out this spreadsheet - docs.google.com/spreadsheets/d/11Q9IaXrLm9mDF4_3nkyQcTqOUiOGdWOpYHi1p2RAe3s/copy
You will notice that I added a plus sign and then another criteria which in this case checks to see if the date values are equal to a date typed into the search box. Now if you type in a date, the results will be filtered by that date. And if you type in a search term, then you get results with that search term in the description. The plus sign creates an OR search. You either search for a text term in the description, or search for a date in the date column.
I hope this helps.
Hi, how do I make multiple search boxes in the same sheet? For example I want to search it by date instead of by name. Thanks!
Hi there!
To create another search bar, just select a new cell (for your second search bar), and then enter in the same formula as before. The difference this time is that you select the date column (per your example) instead of the name column when specifying the column to search in. Everything else should be the same.
I hope this helps to answer your question!
@@spreadsheetlife so it would be possible to search, on the same cell, for both results? or i should have it separately?
Let me see if I understand your question correctly. Are you asking if you can setup one formula that will search based on the criteria from two different search boxes?
Can you help me, my seaech result only show where the function, it doesnt show the whole line
You are welcome to share your spreadsheet with me via email, and I can take a look at it.
spreadsheetlife.com/contact/
Probably missing something, but i get an error msg saying I am missing one or more open parentheses when I use it
Gotcha. You may have missed a parentheses when typing. You are also welcome to email me, and I can take a look at if for you.
Can we change the data in the filter?
I'm so sorry, but I'm a little confused about your question. Could you try and clarify it for me?
Great video and if you're not busy I would appreciate some help on an issue im having. I am trying to create a search that is position sensitive. What i mean is that if I type into the search "abi" it will only return words that begin with "abi" and not every word that contains those letters. Are you able to show or explain how thats done?
I'm so glad you enjoyed the video!
As for your question, check out this spreadsheet right here: docs.google.com/spreadsheets/d/12inF5hkqs3OSKR8CZ9fwl9gcuQLbPARziuqabccW8Oo/copy
You can see the differences between the normal search that I teach in the video and a left search where you only get back results that start with the search term.
I hope this helps.
@@spreadsheetlife great thank you, i didnt notice it. that helps a lot.
Awesome!! Super glad this helps! 😄👍
Hai brother thanks... How to bring more than two spread sheet data into one search bar. And according to updating the data in the sheet, will we get the search result?
See the spreadsheet linked below. The formula searches for the search phrase in the description columns for both datasets.
docs.google.com/spreadsheets/d/1lMPwwbI3erYaAQzZk9hb41ZHRr7oqH-pVf9O2GuypeQ/copy
@@spreadsheetlifeunable to open the spreadsheet
Try opening the link on a desktop or laptop computer and not a mobile device. That might be the issue. Hope this helps!
...also, is there any way to have it search more than one column?
Yes there is. What kind of search would you be after? Would the search term have to be present in both columns, or in either column?
@@spreadsheetlife Hi, thanks for the quick responses! I basically have two columns one with the title of a video and its link, the other column has a bunch of key words. One sheet for the original list, another sheet for the search bar and its results.
I see. So you'd like to search in both columns and return any records where the search term is found in either column. Is that correct?
How do you do this and have the search on a separate sheet?
Just to clarify, are you asking how to reference the source data if it were on a different worksheet tab or in another spreadsheet file?
@@spreadsheetlife yes, for example I have a list on sheet 1 but have my search bar on sheet 2
Ok, gotcha. To reference a range on another sheet, you include the sheet name, an exclamation point, and then the cell or range reference. For instance, look at the reference below.
=Sheet1!A1:A10
The above example references A1:A10 on Sheet1.
You can also read more about referencing across worksheet here: spreadsheetlife.com/how-to-reference-another-sheet/
Can we sort the data from another sheet (sheet1, sheet2)?
Yes. You can use the SORT function to sort data from another worksheet. All Google Sheets formulas and functions can reference and use data from different worksheets.
Can you elaborate the detail please.. it would be really helpfull since i am new to this.. i tried to use the method from countif (used from another sheet) but didnt succeed in this matters
If you'd like, send me an email and I can take a closer look at what you're trying to solve. It would be easier to help via email than through TH-cam comments.
@@spreadsheetlifethank you for the help, i really appriciate it. May i know the email for further discussion?
Yes, it is: info@spreadsheetlife.com
If have search anything from source data then how to search? (What should take search range)? (I little confusing)
Are you asking how to search in more than one column of the data? Or are you asking something else?
@@spreadsheetlife yes, more than one column. (If on column text and one column no.)
I see. Take a look at this. One search field searches in one column, and the other searches in the other column. I hope this helps.
docs.google.com/spreadsheets/d/166QpQrdxqtMFU9KjzE0h8_GTpEJxB8C6afZzOeHjmJ4/copy
@@spreadsheetlife OK Thanks for update me.
This is helpful.
But as I want to make a search box like in MS Excel. (Where you can search any thing like *pe
And result shows you from table = Pen, Pencil, APE-505, stopped.)
It is helpful for if data is big don't know what is exact data.
@@spreadsheetlifein MS using helpnof Create a Table. (Ctrl+T)
Is this gonna work on Android tablet or android phone?
Google Sheets should function the same way regardless of what device it is used on. So the answer to your question is yes. 😊👍
Bro can you help me?
How to search between 2 or more row?
Hi there! Could I ask you to please clarify your question? The method shown in this video searches through multiple rows.
Are you asking for a method to search through multiple fields. For example, let's say your data had a description, category, and extra notes field. Are you asking for a method to search through all of the fields (columns) instead of just one?
@@spreadsheetlife Hi, i was wondering if you have any methods for the situation you just described?
Yes, absolutely. To search in additional fields, you would have to add a plus after the ISNUMBER function, and then repeat the ISNUMBER and SEARCH functions. The difference this time being that you select the new column that you want to search in. I realize that this can be very hard to understand with just words alone.
Check out this spreadsheet which demonstrates an example.
docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy?usp=sharing
Is there a way to hide the source data? I need others to search the file, but not see everyone else's data. (Private) And a big thanks for making this video. Even this noob can follow!
Yes, there are a few things you can do.
You can always hide the columns that contain the source data. The formulas will still work.
You can also store the source data on a different worksheet tab, and then hide that worksheet. Let me know if either suggestion helps.
@@spreadsheetlife Doesn't work. I can hide the source, but the data still appears under the search bar. I'm trying to hide everything except when someone searches then only their data appears.
Oh, gotcha. I see. So once you hide the data, modify the formula to look similar to this. =IF(ISBLANK(C2),"-",FILTER(F5:G18,ISNUMBER(SEARCH(C2,G5:G18))))
This will return a single dash if nothing is entered. You can also see it in action in the spreadsheet linked below.
docs.google.com/spreadsheets/d/1HoApRZxgjjO7lPAkEByvnY58ZzA7WVAYBBV_WMhHGes/copy
@@spreadsheetlife You are so kind. I got everything exactly how it should work EXCEPT when I go to publish it by embedding the code, the search function doesn't work. Any ideas? And is there a way I can pay you for your time? I hate taking advantage of you and your obvious expertise.
I got your email and sent a response from there! 😄👍
How to make it to a dropdown
To create a dropdown, you would have to implement data validation.
this doesnt work for me
Double check your setup and formula. If you are still having issues, let me know some more details and I might be able to help!
#ERROR! Even if I copypaste the the exact same formula...
I'm sorry you are getting an error. Sometimes the error is that a semi-colon is used as the argument separator instead of a comma in some regions of the world. Try that, and it may fix it.
You saved me ! Thanks !
Awesome! So glad that worked for you!
Nope, error.
Try taking a look at the practice file. It might help.