Searchable Drop-Down List in Excel - The Easy Way
HTML-код
- Опубликовано: 23 июл 2024
- Create a searchable drop-down list in Excel using the FILTER function. This is the easiest way to set up a searchable list.
Download the file used in the video - www.computergaga.com/_excel/f...
Introduction to the FILTER function - • Excel FILTER Function ...
This video shows two examples of setting up a searchable drop-down list. The first example searches for what you type anywhere in the text of the list items.
This is great when searching for people's names as you can search using the first or last name.
The second example will test only the characters at the start. The example here is to search for countries in a list. When you type 'Ca' you want countries like Canada and Cambodia to appear, not South Africa although it does contain the letters 'ca'.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/2JvnnRv
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2t3netw
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2viGg3J
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
This is awesome, so so helpful and just what I need and easy to follow tutorial. By far the best Excel channel on utube and I'm so grateful you share your knowledge with us us.
We learn so much from you and I can apply all sorts at work because of you . Much respect sir.
That is lovely to hear. Thank you so much.
Nice use of the new dynamic array functions. Can't wait for everyone to update so that these functions are more portable.
Absolutely. I can't wait.
Of all the days and night looking for the short cut to searchable dropdown list tutorial, you are the best . every thing you said was very clear and understood. waiting for new video.
Absolutely brilliant!!! The pace at which you teach so others can follow is perfect.
Thank you very much 😊
Of all searchable dropdown list videos i have watched, this is the best. Very well explained step by step process. Thank you so much. God bless you!
Thank you very much, Jessa 😊
Good tutorial lad! Easy to follow. Keep up the good work.
Thank you very much.
This is very cool. In particular you put the formula again and extend more functions. It makes me understand in detail. Thank you so much. 😊😊😊👍👍👍
You're very welcome. Thank you, Cindy.
Hi Alan. Great lesson! For your second example with the countries, I discovered that you can eliminate ISNUMBER and SEARCH with this construction: =FILTER(countries[Country],LEFT(countries[Country],LEN(invoice!D6))=invoice!D6,"No Match"). Just another way to get it done. Love this one :)) Thanks for the great tips and tricks. Thumbs up!!
Thanks, Wayne 👍
I only just stumbled across this incredible solution that has been gnawing at me for 2 years with all kinds of online searching turning up short! Big, big thanks to you.
You're very welcome.
Excellent! Very clear! Thanks!!
Thank you Immaculada.
I absolutely love this. Thank you so much!!
You're very welcome, Megan.
Thank you so much for the files for me to practice.
You're very welcome!
Very cool man...Very helpful. Thank you!
My pleasure Luciano. This technique is so much easier now.
Hello, excellent information, question? There is a way to include the autocomplete function to this list as well as the old one that will drop and show the options as you type? Thanks!
Very nice. Thanks a lot. Will use for sure!
Excellent! My pleasure Erica.
Great tips!
Thank you.
Superb tutorial Alan, many thanks, love your formulas for the filter feature.
Cheers
Mohideen
Thank you Mohideen.
ruclips.net/video/EKKFLt5ItKc/видео.html
Hi Computergaga. I love this. It has helped me out tremendously. My only issue is I am using it on an order form. Is there a way to have the formula work on each line of the form? For instance, I am using it as a Parts Description to have my order form auto populate what I need.
What are these dynamic array formulas without people like you Alan who show how we can get the best out of them? Thanks indeed. 👍 Thumbs up.. 🌟 Salim
Thank you Salim, my friend. I'm so excited about these dynamic array formulas.
Awesome! Thanks a lot. I have a scenario where by I have a large list of values of service type and I need to be able to search it for multiple rows rather than just one cell. So if I have to show 5-10 types of service I provide out of the 80 items from the drop down list, how to achieve this?
Great! Thanks for sharing knowledge.
You're welcome. Thank you Pravin.
@@Computergaga Just curious, hope the question is not offensive. Is it Scottish accent you carry? In any case, the accent is marvellous.
No offense taken. It is a London/Essex regional English accent. My last name (Murray) is Scottish, but I'm not.
This is just what I was looking for! I just have one question, is there a way to require the user to select one of the options from the dropdown? For example, at the end of your first demo you searched for "ja" so if you did not select one of the options from the dropdown, "ja" would be left in the cell. I need this functionality because the choice that the user makes drives a VLOOKUP function so if they select anything other than one of the prescribed options the VLOOKUP will fail. Thanks!
Brilliant!
Thank you Ajay.
ruclips.net/video/EKKFLt5ItKc/видео.html
Thank you for this extremely useful video. One question though: If I wanted the searchable drop-down list to work in the entire column, say 40 cells in the same column. How to go about that? Can i link this searchable list to the entire column?
Great video! Could I ask how can achieve this function for all cells in a column?
Pls update Google Style pop up as well for this video. For Spreadsheet, this is readily available in its Data Validation.
That is Excel magic and you are a magician.
Thank you, Natalie 😊
Thank you
You're welcome. Thank you Sani.
It worked great for me however let’s say I am working in a final invoice report for the year and all data was all on the same page, side by side so you had a client name list which is basically vertical down so clients names were let’s say D1:D86, how can I make this formula work so that it doesn’t just create a searchable box for D1 but goes all the way down and allows changes for each cell?
Amazing... thanks
You're welcome. Thank you Salman.
ruclips.net/video/EKKFLt5ItKc/видео.html
Great super thanks
You're welcome Arunachalam 😊
Thanks for the great tutorial. Can you also make a drop-down list opening as you start typing like Google search bar ?
You're welcome. I'm not sure how to do what you ask.
genius❤
😊
Thanks for the clear instruction! May I know is there a way to create autocomplete drop down list?
You're welcome, Arisu. Not easily. I think some others have done it, but it's not worth the effort.
Beautiful sir🥰
Thank you very much Muhammad.
@@Computergaga this is owner to me that you are my teacher. 🌹
Great
Thank you 👍
Hi - can it be used to search for numbers or product codes ??
@Computergaga I was lost at 2:52 when you finish the formula, there'll be a whole new table which has exactly the same amount of rows as the first table. How can I do it? Anyone know the answer please help
This video is brilliant!! I managed to follow and recreate however then noticed this isn’t the complete function I need 🤦🏻♀️
On sheet 1 I need a searchable drop down list for company names ✅ and the 2 boxes underneath will populate with the telephone number and email address. Sheet 2 contains all this information but obviously the formula from this video isn’t specifically linked to each row of data on my second sheet so vlookup won’t pull it through. Do you have any pointers please?
Thank you. It sounds like you're asking for a VLOOKUP (or other lookup formula) for each cell to return the telephone number (VLOOKUP 1) and the email address (VLOOKUP 2). It will search for the company name chosen from the list, to find these related details.
I have an introduction to VLOOKUP video here ruclips.net/video/qZ1ybnAXprk/видео.html
Great content, thanks, but I am missing basic Excel features as how do you expand the search results to the entire column when closing bracket/pressing enter at around 3:19 minutes of the video? Mine is not working like that, it keeps the search results only on the first cell, not expanding to the entire column. Thanks
Thanks, Denis. That behaviour is dynamic arrays - ruclips.net/video/X2ybRZbvR5k/видео.html It is only available in Excel 365, 2021 and online versions.
I have a searchable drop down list video that works in all versions too - ruclips.net/video/srTteYoqcJs/видео.html
Thank you for this video. For me, it only updates the drop-down list when I press the arrow AFTER I have pressed enter and filled in the value. Yet, for you it seems to be updated immediately after you click the drop-down arrow. So no need to press enter first to update it. I am on Mac, could this have something to do with it? I do use Office 365
For example: If I have "an" typed into the cell. It shows "Anna-Maria". But when I type in "br" and don't press enter, it still shows "Anna-Maria" and nothing that contains "br". Only after I press enter, it shows the new results
Hi Alan, can you show how this technique can be used in such a way that you select from a drop-down list (let's say a unique invoice number or a customer name) when you have about 500 rows of data. Can you filter the search and it then take you to the row (or rows) that apply to what you chose from the drop-down list so you can then add or amend data? You'd then want to unfilter to show the entire list of data again. This would all be done from one worksheet. One scenario I'm thinking of is a list of servicedesk calls that people have raised, and you're recording notes the status of the IT request that you are handling on a user's behalf. Does this feature in your beginner to expert Excel course?
Hi Stewart, I have replied to you in my Excel course messages more comprehensively.
In the example you give here. You just need the normal filter feature. It has a built-in search capability.
This is exactly what I needed! Love it and it's working for me. I'm trying to build a quote tool so would love some advice on how to expand this to multiple cells pulling from the same list. At the moment you show it returning a value to one cell. What if there were multiple lines on the invoice? Is that possible?
I have the same query! Did you find a solution. I see that the author has not responded on this particular query
@@vishnudutta21 Use the next trick, it will search from the cell where you just type
The input cell is where do you have the search values from In the Search(Find Text,
Designate a location Like "G1" can be ANY CELL so type in or (simple copy and paste the next line) in G1
=INDIRECT(CELL("address"))
so from now on anything you type anywhere will appear in G1
So you could use multiple cell as input..
@@balwek2 Still could not get it, can you write out the whole formula please?
@@beejdeej can be ANY CELL so type or (simple copy and paste the next line) into your designated cell
=INDIRECT(CELL("address"))
I use this trick on all my drop down cell. Thousand of them on multiple pages, works fine...For part number search input.
extra ordinary
Thank you very much Shahariar.
Hi!
I’ve got a slightly different approach to the formula under Filter Names.
Instead of using the ISNUMBER and SEARCH functions my formula reads:
FILTER(countries[Country],LEFT(countries[Country],LEN(invoice!D6))=invoice!D6,"")
Thanks for your video. However, when I named the dynamic range, although I tried the almost same function as you, in name manager, I found the value of this named range is {...} . What is the issue? Thanks in advance.
Sry, I comment in a wrong place. It should be in your old viedo.
Amazing as usual mate, just one question, in your previous video; "Create a Searchable Drop Down List Just Like Google - Excel Trick", at the end you added a line of VBA Code to show the Combo Box without clicking the arrow. Is there a way to do that with the validated drop down list here? Thank you!
Thank you, Ignacio. You can drop down the list with the Alt + Down arrow shortcut. If that is what you are asking.
@@Computergaga Hi, I was referring to the validated drop down list to change size automatically depending on what you type for the search, just as you did it in the previous video by adding the code: Private Sub ComboBox1_Change()
ComboBox1.DropDown
End Sub
to the combo box, in this case I guess it would have to be something to the validated drop down list but I haven't found the syntax, thank you!
Thanks, man. But will this work for just figures? Serial number and such.
My pleasure. Sure, that shouldn't be a problem.
Absolutely brilliant!!! can yu halp me please, my laptop use MS Exell 2013, but I'm not see filter function... tks
Thank you. The FILTER function is only available in Excel 365 and Excel Online unfortunately.
This was very helpful! Thank you! I did encounter one issue. At first my data validation list worked like yours, then something happened and now the list does not update until I leave the search cell and come back to it. When I hit enter or click out and return to the search cell it has the results, but not until. I have confirmed my calculation setting is automatic. I also have this issue using your sample file, so I think it must be a setting? Thanks so much!
You're welcome. I'm not sure what is causing your issue.
I have the same issue. Did you figure out the reason after?
I have this same issue, do you have the solution ?
Can you tell me if you teach EXCEL via distance learning? do you get a certificate after doing it and if so what is the examing body?
I do Naz. I teach on the Udemy platform and have a few courses. For example my Ultimate Excel course here - bit.ly/2LVuHDW
You do get a certificate on completion for proof of learning. This is provided by Udemy.
Hello sir!!! I hv a question regarding filtering..i hv some data like i hv 2 different lists of stocks. First list consists stocks near support and another list on the same sheet is for stocks near moving average. And i want to filter the common stocks name from the two lists. How to do that sir?? Could you help me out?
I think Power Query is your best option. Check out this video - ruclips.net/video/jDGJhcp4waQ/видео.html
Excellent! Is there a way that I just enter the letters and populates automatically without me to click on the dropdown arrow, or at least, dropdown the box automatically. When we are handling hundreds, thousands data, it hurts the hands with too many clicks to open the arrow down list.
You would have to use a VBA combo box then. Honestly, Excel really should be having such a basic feature taken care of - it's so easy in Apple Numbers! I use an Excel plug in from Excel campus. It works well but it's pop can be a little irritating because I use it when writing an invoice, as I need to fetch a product name and then enter a quantity (to a cell on the right), for which it doesn't close. You could also check out kutools but it's to-pay and I haven't tried it.
sir, your this formula has give me full support as i was needed. but i have apply this dropdown list in multiple cells, but i could not success.first cell vale is appearing. kindly guide me if you understand my question.
Thank you, but I do not have a FILTER function in my Excel 2016.
Can I use any function instead.
I have a formula for an older version here - ruclips.net/video/srTteYoqcJs/видео.html
You can also use searchable list functionality in Excel Online.
Hello Sir, I don't get the results as per your presentation. I created the search formula, as you shown in the video but when I pressed enter it did not populate the result for each row in the table. Only populate the the 1st row. So, I am stucked here, can you please help?
Wonderful. Also, can you prepare a tutorial about drop-down list that when we select an item, list range is refreshed except for selected item?
is it possible?
Thank you, Emre. I have a video on that here - ruclips.net/video/_TP45EJqE4M/видео.html
@@Computergaga Thank you for the information.
Thank you for sharing this valuable information. However, it would be even better if it had an auto dropdown feature.
Yes I agree
This is great, but is there a way to show the whole list if no characters are entered?
Can u make the data validation cell to auto expand droplist without clicking it, just tiping in the cell? Is it possible?
I don't believe this is possible, Ariton.
@@Computergaga i saw something with an addin plugin, but it doesn't work for excel 365, i was thinking maybe can be done in other way. Well i guess i have to do hard work. Thanks anyway!
Thank you for this video. I have one problem- when I create the searchable dropdown list, all of the blanks in my table also show in the dropdown, instead of only the values that I want. How can I fix this? Thank you again
You're welcome, Emily. A second criteria can be added to exclude the blanks. The criteria would be written as;
countries[Country]""
So, the full formula would be something like;
=FILTER(countries[Country],(ISNUMBER(SEARCH(C2,countries[Country],1)))*(countries[Country]""))
how did the formula gets applied to all the rows?? ( 02:51 ) how did he select all the members of colomn 1 and applied the formula??can any one replay???
i downloaded the file and wanted to try it out, unfortunately my excel version don't have the filter function. :(
Only available in the Office 365 version unfortunately.
This works great, however, if I lock a particular series of cells (that I don't want the user to change) then protect the sheet, the drop down quits working. I am not locking any of the cells that involve any of the lookup formulas. Any Ideas?
Hi Alan, as long as the drop-down list cell and any other cell to type into are unlocked this shouldn't be a problem 🤔
Hi , Great function, However I get an error message when adding the "FILTER" part.
Thank you Zurab. You need Office 365 to have the FILTER function. That may be the issue.
@@Computergaga Hello, Function seems great but I also got the Error message even I'm using Office365... Is there any update to install?
is their any other option instead of "FILTER" formula ? For the user not having Microsoft 365.
There is this approach - ruclips.net/video/srTteYoqcJs/видео.html
But it is not straight forward.
Greetings.
Why can't i type FILTER function in my excel 365 ? is there another way?
Sounds like you need to change the update channel of your 365. This tutorial shows you how - bit.ly/2G8qHhx
And you will get the FILTER function and more.
This is not work in office 2016, if i am not wrong it would be work properly in google sheet. Am I right or wrong? Please let me know.
The formula will only work in Excel 365 and Excel Online. Google Sheets, it can be done also but a different way.
sir just I will says that this type searchable dropdown list in the skies but you give on earth. I was held to get this type of help/video....your video give me a complete guidance.thank you very much again sir.
You're very welcome.
Searchable drop-down lists are actually available as standard in the newest Excel version now if you have it. I have a short post on it ruclips.net/user/postUgkxmXOgQXcEjcpMKC6SuBedpQzcK8VooRf9
is it only use for office 365 ?? i did not find FILTER function formula on my office 2016/19 ... why ? what can i do pls help me sir ...
There are several videos on the subject here on RUclips. Try writing: Excel Searchable Dropdown in the searchbox on the top of the page, and you will probably find a video that meets your needs.
Yes, it is only available in 365. I have a video here on how to do it in previous versions - ruclips.net/video/srTteYoqcJs/видео.html
Can we prepare/insert Searchable Drop Drown List within a Table?
Hi, what if I want to search in more than one cell ?
Would probably need some VBA to generate the filtered list instead based on the Active Cell. The set up the list as in the video.
sir.. could it be possible to create a searchable dropdown list with links,, thank you..
Sure. This video will show you how to make a drop-dow list of hyperlink s- ruclips.net/video/LTWvuIWMbhc/видео.html
Combine that with the searchable list from this video.
@@Computergaga i allready done it sir, but for a large list, it keeps me scrolling, i want it to be searchable, thank you sir,,
by the way sir, i love all your videos, it helps me a lot,
After I type in the search function it doesn't spill the formula down. Does anyone know why this is happening?
You might not have that functionality in Excel yet. It is a new feature with another rollout due this month to Microsoft 365 users only.
When I do "Enter" in the SEARCH function it only searches in the 1st cell, how can I fix it?
Sounds like you don't have the dynamic arrays yet. You can select all the range to apply it to (not one cell) and then use Ctrl + Shift + Enter for an array formula. But if you don't have dynamic arrays you won't have FILTER.
@@Computergaga I applied the formula for every single cell, and it worked. Thank You!
I am unable to find filter formula in my excel, using office 2013
The FILTER function is not available in that version Najam. It is available in Excel 365, Excel Online and Excel 2021 only.
hieee buddy.. my excel dont have FILTER function.. what to do?
FILTER is only available in version 365, 2021 and online.
I do have another searchable drop-down list video on this channel that works in all versions.
@@Computergaga ohaky.. thanks buddy.. i'll look into that video
Excel 2019 doesn't have filter function. What to do?
No, it is only available in Excel 365. I have a video on how do it in other versions here - ruclips.net/video/srTteYoqcJs/видео.html
i am using Window 10 and there is no formula like =FILTER, is there any other formula to use instead of FILTER Function
I also have this video - ruclips.net/video/srTteYoqcJs/видео.html
It is more work without FILTER, but can be done.
The thing is, I need to input multiple values in the column, when I move to the next row, it does not reset the list back for me. You can only insert values in 1 single row. Dragging doesn't work either.
Found the solution, use CELL("Contents") inside the SEARCH()
whay in my exel nothing filter function????????? someone can hellp me?
The FILTER function is only available in Excel 365. You may have another version.
Love your work however, my only gripe and it's not with you only and that is the fact that right upfront it should be mentioned if these Office 365 solutions have backward compatibility functionality which if not mistaken, they don't and in many cases if not most, these formula's are not meant in isolation as the end result, a dashboard etc, requires others to access and manipulate but without Office 365, you're buggered Mate!
Maybe someone can help assess the time it will take to have updates (service packs of sorts) that will make all these new formula's functional even in outdated versions of Excel. I.e. if it is only meant for people that have 365, then me thinks it would be silly to design any outbound dashboard or similar with 365 functionality until Office offers the WORLD a free upgrade to office 365 if you get my meaning!
Still and will always love your work Alan...
Thank you buddy. Microsoft wants everybody on 365 and 365 offers so much more than the regular versions it is the way to go. It is harder for huge corporations to make the change if they are not on 365 already.
Is this function in excel or office 365?
365 only Mukesh.
ruclips.net/video/EKKFLt5ItKc/видео.html
So sad that FILTER function is not available in normal Excel, but Office 365 and Google Spreadsheet.
Yes, I'm afraid 365 is where it is all at.
I can't see = filter function in my excel
The FILTER function is only available to Office 365 subscribers. And is very new so only those on the monthly channel at the moment, another update in a few months. You can switch to the monthly channel though, details here - www.computergaga.com/blog/how-to-change-to-the-office-365-monthly-update-channel/
I am unable to find filter function with excel 2016
The FILTER function was released in 2020 and will only be available to 365 users.
@@Computergaga Thanks
R u british?
Love that accent
Yes indeed. Thank you 😊
this is the easy way?? :P :P Isn't there a simple search function in Excel (2019 for mac) where you type in a search box your search letters/words) and the sheet narrows down the columns that contain your search query? So as you type in more letters in this search box, the resulting columns get less and less in your sheet? You now, simple, user friendly, stuff! ;)
Actually when you use the LEFT function you don't need the ISNUMBER and SEARCH functions. This formula returns the same results: FILTER(coutries[Country],LEFT(countries[Country],LEN(invoice!D6))=invoice!D6;"Not found")
I have no idea why but i try the # on the validation box, it does not work.
Make sure you have selected the first cell in the dynamic array Eric.
@@Computergaga
I did, or it is only valid for 2019 or 365 ?
my input is : =$D$2#
It is true that it work only in 365?
Mine Excel is 2016
@@Computergaga -
The Source currently evaluates to an error
=$a$1# or = a1#
Same result
@@erictsang789 Yes, it is only available to 365
Doesn't work. If I do use your template and recreate those steps.. i'll get errors. :/
You probably don't have the FILTER function yet. It is currently out for O365 users on the Monthly Channel. All O365 users by July.
I meant for the Country data validation drop down , not the names one.
Yes, I think you could provide the entire Country column instead of the empty string in the last argument of FILTER
For the 7544575683453453464523543654 time sam example found on internet. Who needs this in real life? In real life user needs seach that use many filters/columns at same time.
Thanks, this is perfect but I have a problem. I have a column full of validation data, this works if you only have one. How could I do? Maybe if you made a video .... Thanks again
I have the same situation have you found anything for this?
@@williamchua5274 ruclips.net/video/e2-uc3nOKlE/видео.html