How to Create a Search Bar in Google Sheets
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.co...
RELATED ARTICLE
Check out the post below where I go through all three formulas you learn about in this video.
spreadsheetlif...
RESOURCES
- Free Spreadsheet Templates: spreadsheetlif...
- The Spreadsheet Life Blog: spreadsheetlif...
LEARN EXCEL OR GOOGLE SHEETS
- Excel Total Course: spreadsheetlif...
- 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
Пікірлер: 143
Thank you for sharing
@spreadsheetlife
7 күн бұрын
You are very welcome! 😊 Thank you for watching.
Dayummm!! Bro make more of these.... in exactly same simplified way!! too good.
@spreadsheetlife
Жыл бұрын
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.
@spreadsheetlife
10 ай бұрын
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..this is so helpful
@spreadsheetlife
3 ай бұрын
So glad that you found this helpful!
thanks bro very useful tricks , ( pro explanation , pro work) , 🎉new better filter method 🎉 ,
@spreadsheetlife
Жыл бұрын
Thanks so much for the feedback! So awesome to hear you've found it useful!! 😄🎉🎊
Thanks Halp full tutorial. Need more explanation for accounting and generate reports for construction companies.
@spreadsheetlife
6 ай бұрын
If you have specific questions, you are welcome to send me an email and I will answer the best that I can.
Wow.... this is amazing..... Thank you, Sir!...
@spreadsheetlife
8 ай бұрын
You're very welcome!
Thank you for this video clip of yours, this is very helpful, and with the sample link that is very generous of yours
@spreadsheetlife
8 ай бұрын
Super happy to help! 😄👍
Thank you!
@spreadsheetlife
10 ай бұрын
You're very welcome!
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?
@spreadsheetlife
3 ай бұрын
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.
@spreadsheetlife
2 ай бұрын
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?
@magnitar
2 ай бұрын
Yes, you are correct. I have tried repeating part of the function with the "+", but can't quite seam to make the formula functional.
@spreadsheetlife
2 ай бұрын
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.
Thank you very much...
@spreadsheetlife
6 ай бұрын
You're very welcome! 😊👍
Great work, you are superb
@spreadsheetlife
5 ай бұрын
Thank you so much! So glad you enjoyed the video! 😄👍
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.
@spreadsheetlife
Жыл бұрын
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!
@lcobbey
Жыл бұрын
@@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).
@spreadsheetlife
Жыл бұрын
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.
If I wanted to have nothing display when the search box is empty, how would I do that?
@spreadsheetlife
Жыл бұрын
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))))
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!
@spreadsheetlife
Ай бұрын
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.
@jesusramos5706
Ай бұрын
@@spreadsheetlife Copy! With your experience do you believe there is a function/command that could be created to enable a feature like that? Also, are you self taught or perhaps took a college course to be so well versed at google sheets?
@spreadsheetlife
Ай бұрын
I'm sure it's a possibility, as spreadsheet technology continues to improve over time. However, I do not thing it would be possible to create a feature like that with what Google Sheets can do right now. Perhaps someone much smarter than me would say differently, but with what I know, I think it's a little out of reach at the moment. As for my learning journey, I am self-taught when it comes to Google Sheets. My skills really started to blossom when I practiced making all sorts of spreadsheets. Task tracker, budget planner, etc. The more I created, the better I got. Practice, practice, practice! Also, my skills continue to improve as I help others learn. After all, as you work hard to make things clear for others, those things become even more clear for you.
@jesusramos5706
Ай бұрын
@@spreadsheetlife Well said! Thank you for your input! I've also emailed you on a question regarding my spreadsheet. Thank you!
@spreadsheetlife
Ай бұрын
Awesome. I got your email and will be following up on it ASAP.
Hi can I use "&" to search more column not just the "Description". Example, I want to search the date
@spreadsheetlife
3 ай бұрын
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.
How would you do this is the search was a sheet itself and you multiple other sheets with data?
@spreadsheetlife
Ай бұрын
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?
thank you very much
@spreadsheetlife
11 ай бұрын
So glad this could help!! 😊👍
is it possible to do this with the exact same function but be able to delete data from the search results?
@spreadsheetlife
24 күн бұрын
Unfortunately, it will not work this way. Anything returned by the FILTER function can only be viewed but not edited or deleted. All changes must be made to the source data itself. I'm sorry that's the case.
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!
@spreadsheetlife
7 ай бұрын
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
@justkathrynn
7 ай бұрын
It works!! Thanks a lot!
@spreadsheetlife
7 ай бұрын
Awesome! I'm so glad it worked!
Thank you
@spreadsheetlife
4 ай бұрын
You're welcome!! 😊
Nice video! I was using drop downs for the non-search field column, but looks like drop downs do not show.
@spreadsheetlife
10 ай бұрын
Thank you! I'm a little confused about your question. Could you please clarify?
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?
@spreadsheetlife
10 ай бұрын
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
@mvptarheel299
4 ай бұрын
@@spreadsheetlife I was not the one to ask the question, but this is exactly what I was looking for, thank you!
@spreadsheetlife
4 ай бұрын
Awesome! I'm so glad that this was what you were looking for!
i love u thank u so much
@spreadsheetlife
5 ай бұрын
You're very welcome! 😄👍
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!
@spreadsheetlife
3 ай бұрын
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.
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?
@spreadsheetlife
4 ай бұрын
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.
@stephenfahey7402
4 ай бұрын
@@spreadsheetlife great thank you, i didnt notice it. that helps a lot.
@spreadsheetlife
4 ай бұрын
Awesome!! Super glad 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!
@spreadsheetlife
10 ай бұрын
Check this out! The formula searches two columns. Let me know if this answers your question! docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy
@DAGL
10 ай бұрын
@@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 :)
@spreadsheetlife
10 ай бұрын
Happy to help! 😄👍
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.
@spreadsheetlife
7 ай бұрын
Just to make sure I understand, are you asking how to show no results when nothing is typed into the search bar?
@clarissemoraga1262
7 ай бұрын
Yes, that is correct.@@spreadsheetlife
@spreadsheetlife
7 ай бұрын
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 it possible, to make it apply any changes made in the search field onto the source data?
@spreadsheetlife
7 ай бұрын
Unfortunately that is not possible with the method shown in this video.
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!
@spreadsheetlife
2 ай бұрын
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.
@daveho4244
Ай бұрын
@@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.
@spreadsheetlife
Ай бұрын
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
@daveho4244
Ай бұрын
@@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.
@spreadsheetlife
Ай бұрын
I got your email and sent a response from there! 😄👍
Probably missing something, but i get an error msg saying I am missing one or more open parentheses when I use it
@spreadsheetlife
7 ай бұрын
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.
...also, is there any way to have it search more than one column?
@spreadsheetlife
3 ай бұрын
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?
@georgestoll3653
3 ай бұрын
@@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.
@spreadsheetlife
3 ай бұрын
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?
Hi! Id like to know is there any way to count how many links are in the column? Thanks!
@xianjir
11 ай бұрын
For example, i named a cell "Present" with a link inside. Can i count that?
@spreadsheetlife
11 ай бұрын
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.
Can you help me, my seaech result only show where the function, it doesnt show the whole line
@spreadsheetlife
6 ай бұрын
You are welcome to share your spreadsheet with me via email, and I can take a look at it. spreadsheetlife.com/contact/
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?
@spreadsheetlife
10 ай бұрын
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
@renjithlal100
10 ай бұрын
@@spreadsheetlifeunable to open the spreadsheet
@spreadsheetlife
10 ай бұрын
Try opening the link on a desktop or laptop computer and not a mobile device. That might be the issue. Hope this helps!
How do you do this and have the search on a separate sheet?
@spreadsheetlife
3 ай бұрын
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?
@backgroundsounds3420
3 ай бұрын
@@spreadsheetlife yes, for example I have a list on sheet 1 but have my search bar on sheet 2
@spreadsheetlife
3 ай бұрын
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 change the data in the filter?
@spreadsheetlife
9 ай бұрын
I'm so sorry, but I'm a little confused about your question. Could you try and clarify it for me?
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!
@spreadsheetlife
Жыл бұрын
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!
@DAGL
10 ай бұрын
@@spreadsheetlife so it would be possible to search, on the same cell, for both results? or i should have it separately?
@spreadsheetlife
10 ай бұрын
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 we sort the data from another sheet (sheet1, sheet2)?
@spreadsheetlife
5 ай бұрын
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.
@malikibr7117
4 ай бұрын
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
@spreadsheetlife
4 ай бұрын
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 KZread comments.
@malikibr7117
4 ай бұрын
@@spreadsheetlifethank you for the help, i really appriciate it. May i know the email for further discussion?
@spreadsheetlife
4 ай бұрын
Yes, it is: info@spreadsheetlife.com
Hi i would like to know if i search i get a result, but i will be able to edit the data within the search results i get, because i see you cant edit data within your results you search for. Can you maybe make it edible.
@spreadsheetlife
5 күн бұрын
Since the search results are coming from a formula, you cannot edit them directly. The only way to edit them would be via the source data itself. I'm so sorry that's the case.
@DesignerzNet
5 күн бұрын
@@spreadsheetlife thank you
@spreadsheetlife
5 күн бұрын
You're welcome! Sorry I didn't have a better answer for you.
If have search anything from source data then how to search? (What should take search range)? (I little confusing)
@spreadsheetlife
7 ай бұрын
Are you asking how to search in more than one column of the data? Or are you asking something else?
@SandeepSharma-ym2um
7 ай бұрын
@@spreadsheetlife yes, more than one column. (If on column text and one column no.)
@spreadsheetlife
7 ай бұрын
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
@SandeepSharma-ym2um
7 ай бұрын
@@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.
@SandeepSharma-ym2um
7 ай бұрын
@@spreadsheetlifein MS using helpnof Create a Table. (Ctrl+T)
Is this gonna work on Android tablet or android phone?
@spreadsheetlife
11 ай бұрын
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?
@spreadsheetlife
Жыл бұрын
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?
@jingyitan443
Жыл бұрын
@@spreadsheetlife Hi, i was wondering if you have any methods for the situation you just described?
@spreadsheetlife
Жыл бұрын
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
How to make it to a dropdown
@spreadsheetlife
7 ай бұрын
To create a dropdown, you would have to implement data validation.
this doesnt work for me
@spreadsheetlife
10 ай бұрын
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...
@spreadsheetlife
6 ай бұрын
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.
@abdougalsen
5 ай бұрын
You saved me ! Thanks !
@spreadsheetlife
5 ай бұрын
Awesome! So glad that worked for you!
Nope, error.
@spreadsheetlife
8 ай бұрын
Try taking a look at the practice file. It might help.