Searchable Drop Down List in Excel (Very Easy with FILTER Function)
Вставка
- Опубліковано 9 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover a straightforward method for creating searchable dropdown lists in Excel without using VBA. This tutorial is perfect for those looking to enhance their Excel skills and add dynamic, user-friendly elements to their spreadsheets.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/searchable-...
This technique uses data validation and (simple) Excel formulas to create a searchable data validation drop-down list. We use the Excel SEARCH function, ISNUMBER as well as the brand NEW Dynamic Array FILTER function. To make sure our searchable data validation drop-down list is sorted, we use the new dynamic array SORT function.
In this video, you'll learn:
▪️ Setting Up Searchable Dropdowns: Step-by-step guidance on creating dynamic and searchable dropdown lists using Excel's powerful features.
▪️ Utilizing Excel's Dynamic Arrays: Learn how to leverage Excel's new functions to make your dropdown lists responsive and efficient.
▪️ Enhancing Data Validation: Tips on improving your data validation process for a smoother user experience.
▪️ Incorporating Excel Functions: Insight into using Excel functions like FILTER, SORT, and UNIQUE to create more functional dropdowns.
We also use the FILTER functions instead of VLOOKUP or INDEX & MATCH to find the value that matches our selection.
00:00 Searchable Data Validation in Excel without VBA
01:11 Searchable Drop-Down List with Excel Dynamic Arrays
02:53 Excel SEARCH function
04:56 Excel ISNUMBER function
05:18 Excel FILTER function
06:42 Excel UNIQUE function
07:05 Excel SORT function
07:16 Create Data validation list with Excel Spill Array
08:26 Excel FILTER function for Lookup
09:28 Using Official Excel Tables
LINKS to related videos:
Dynamic arrays announcement: • Excel Dynamic Arrays (...
➡️ Join this channel to get access to perks: / @leilagharani
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/searchable-dropdown-file
Update 2023: In Office 365, data validation lists are now searchable by DEFAULT! Check out this video for more info: ua-cam.com/video/3Qh5nUmCVd8/v-deo.html
For Searchable Drop-Downs on MULTIPLE rows check out this video: ua-cam.com/video/waqzwMCYD9I/v-deo.html
Thanks for editing this comment. After trying this out last night, I was confused as they are already searchable.
Content - 10/10
Way of Explaining - 10/10
Your knowledge and teaching skills are extraordinary!!
Fully agreed!!
filter arry is not showing in my formula ,please help
And your buttering- 11/10
Annnd i just subbed
Man something similar I posted a few minutes ago, she show/teach us in a clean, fast and to the point manner that I just love it. She became one of my favorites at first sight.
This looks awesome. I've made a searchable dropdown before but it was way complicated and required like 4 extra column before it would work. Lots of data that had to be hidden on the same worksheet and that could be messed up by inexperienced users. Dynamic Arrays and the Filter function sound like the late Christmas present we didn't deserve but desperately need. Great video!
.. and this is the comment that helped me to figure it out on 2013. Thank you wouter114!
THAt's so great! Thanks a lot
You are brilliant, even after years of using Excel I always learn a few things from your videos.
Totally mind-blowing that this material is free of charge. Great content, keep it up! :)
I had watched office tutorials since years, and without any doubt you are the best
Excellent illustration dear Leila Gharani !
Effective useful for the IT development for the human society !
Appreciate your great services in these training videos !
We wish you and your family / team all the best of time, health and peaceful happiness !
Leila,
Thank you so much for sharing so many of these Excel tips and tricks. I'm an advanced Excel user with over 20 years of Excel Development behind me, and I find that a lot of these new features that you share are immensely useful! Thanks so much for putting these videos out there in an easy to follow and understand format! You are AWESOME!
Many thanks for the kind feedback. It's great to have you here :)
BRILLIANT! Great solution to a long-time need! I've had to do this in the past and it was a crazy mess.
Thank you! Yes - agree - it was a crazy mess before :)
Thank you
I used to do something similar with those awkward Combo Boxes.
Liela's method is superior and has the added plus of auto-expansion with additional rows of data.
Invested 11 minutes into the video, followed by 5 minutes applying my new found knowledge and 16 minutes later, my colleagues were ready to run with a new and much improved model. 🙌
Thank you!!! 👍
Excellent!
First I've been looking for a way to allow users of my sheets to narrow the DV list based on letter searches, without VBA. So thank you.
Secondly, I didn't know about the new 'Sort', 'Filter' and 'Unique' functions. These will save me a lot of time using permutations of INDEX MATCH COUNTIF etc.
You've added value to my work, thank you!
This channel is superb. Would recommend it to all excel users.
Best video for searchable drop down list!Awesome!!!
Thank you so much! beautiful Leila. I am using excel a lot, but still learn new thing from you. Love your voice and your way of teaching. You rock!!!
AWESOME, I've been looking this in years by now!
Best Excel channel out there. I like that you are always on the cutting edge of new features. I've been using Gsheets lately as well and I really like that you can select entire ranges from one cell onward like this: A2:A (which selects A2, A3, A4...). I wish Excel had that without a lot of complication.
Thank you George!
The technique and the way of delivering, I love it.
Thank you very much Leila! Your presentation allow me to keep up with Excel new stuff, which goes quickier than in the past i feel!
Hi Leila. Thank you so much for your videos!
I‘ve started working on a little company and they have NO tools for analysing their business. So I have to create Tools for Monitoring, vacation planning and much more. With your Videos, I can create and finalise my Ideas. Thank you for your overall good work!
I bless God for the day i found you Leila. Amazing tutorials.
Thank you for the kind words!
Osamudianmen Igbinovia hope she see this brogod 💯💯
Ditto
I really like your videos. The pace can easily be followed, the video editing is excellent, your voice is so calming and you are beautiful. Thumps up. Thank you for sharing your knowledge, ma'am. God bless you.
I see a huge value in this!! Big fan of your videos Leila! You have no idea how much you have influenced the way I am using excel nowadays. Big promotor of your videos. Often promoting them to my team all over the world. Thank you so much!
Many thanks for your support!
This video is great! I've seen a cpl different ways to do this with none of them as concise and easy. Even 4 years later, yours is the best! You got a new follower.
Great video. The question I have is that if I want to create a dynamic drop down for multiple cells?
I have also same query that this method does not work if e want to drag and drop same data validation to multiple cells
Thanks once again to my Beautiful/Brilliant/Brainy/witty Excel Teacher. Every Thursday, I am waiting to see your videos eagerly. Really You are doing a great Job for the Excel lovers/users. Thanks a lot . You are a shining/Twinkling star in the Excel Universe.
Thank you very much Gopala for your kind words and your support. I'm very grateful for the Excel universe.
@@LeilaGharani Great Salutations once again to my Excel Guru. Thank you
Definetely, Leila is the best Excel teacher ever ! I´ve been browsing thought many channels looking for soluttions to a new Excel system I have developed to a small business and most of the time is Leila that has saved me. All explanations are very
didactic, she is very sweet and we always get other knowledges together. Many, many, many thanks !
Thank you very much for the kind feedback Alexandre!
Your videos are amazing and easy to follow. I was able to create reports following your instructions. Thank you so much!
That's super cool ! i can make the telephone list fancy in my company :)
Thanks keep staying awesome Leila!
This 2019 video production shows what a Excel master Leila has been while compared with another "cookie" video produced by someone else 2 years later. To surpass Leila's originality, creativity and well thought layout and illustration has never been an easy task.
Leila, I love your tutorial and teaching talent so much.
Time's well spent. Thank you.:)
Thank you very much for the kind feedback!
Thank you so much for this video!! I own an auto mechanic shop where each tool is entered to inventory by the 'office' people but the mechanics need to quickly search if we have a tool and where it is located. I entered the formulas into my spreadsheet in one window, right along with this video in another window, and published it to the shop's desktop at the end of the video. 11 minutes invested watching this video will save my staff hours of having to search the tool room to determine if we have a specific tool and if yes, where it is.
That's a perfect use Shawn. It's great that you were able to implement this for your purpose.
Thanks! I love the way you explained everything. So easy to understand and follow. Wish I could give more but I hope others give and collectively it all compensates for the amazing work you do in sharing your knowledge. Thanks again.
Thank you very much!
Good morning leila, is there a way that this feature can be done, on every line item or row in excel? i find your videos very easy to follow, that's why i subscribed to your channel. thanks!
You are incredible - I am learning so much from you. Easy to follow, enjoyable - thanks so much!
I follow your videos they make complex things in excel so simple. Thanks 👍🏻
Never have I watched an Excel training this long. Watched at 2x speed. Absolutely full of gems. Really well done -/ thank you!!
Great to hear!
Hello! Very helpful content, thank you very much. I am trying to have multiple of the searchable drop down list on the same column. Does anyone know how to do so? I tried including the whole column in the search argument but it doesn't work.
Thank you!
ARRAY functions, currently one of the best functions in Excel which are saving a lot of time.
My next wish is to have more advanced conditional formatting (e.g. the line thickness depends on cell value, becauseat the moment it is possible to set only the standard thickness line)
It will be helpful and will avoid additional work with VBA just to set specific/more advanced conditional formating.
Perhaps M$ will consider that feature in some update...
Most of your Excel videos are worth the time, your teaching skills are 10 / 10.
The way you explain very clearly is amazing. Learned so much from your Channel. Thanks for sharing
Thank You Leila, another question what formula's could one use to get the same results that you're demonstrating
Mywy
Hng y
For those who are trying to get version 1904 for the new features you need to change your office level to "Insider" (just above the update button). This gives you access to pre-releases, be aware these can be buggy.
I am glad I found this video, the way you explain it is fantastic. Thank you so much for this Leila and I will definitely sign up for the courses... your teaching skills are amazing!!!!
My pleasure Carlos. I hope you'll like the courses.
You are simply one of the best, thank you!
Hello, I have watched your video on another computer, I am working with Office 365 yet your formula does not work for me, I do not have a filter, it already starts searching I get another error message, despite the correct forum formula, also want I have it in the drop down list, can you help me?
As a few others who have mentioned this, I needed a searchable dropdown list that could work for multiple rows. I found a solution that worked, however it is probably not very elegant and I would welcome some other suggestions! I needed to do this without VBA as I needed to share it with someone outside my organisation.
Note, that this only works if you are filling out a list that is always going down.
1. Column A in sheet "Data capture" is the column I want the validation list to display in.
2. Create a dynamic selection using name manager to select all cells with a value in: Name: nm_meter_input, Refers to: =OFFSET('Data capture'!$A$2,0,0,COUNTA('Data capture'!$A:$A),1)
3. Create the formula in a reference cell (not to be seen or used by anyone, only by another formula), in this example I have entered below formula in cell I2:
=IF(COUNTA(nm_meter_input)=0, "A2", IF(COUNTIF(tbl_meters[Name], INDIRECT("A"&COUNTA(nm_meter_input)+1))=0, "A"&COUNTA(nm_meter_input)+1, "A"&COUNTA(nm_meter_input)+2))
4. Enter the below formula in another cell, in my example I have entered in K2
=INDIRECT(I2)
5. Then reference to the cell K2 in the Filter formula
=FILTER(tbl_meters[Name], ISNUMBER(SEARCH('Data capture'!K2, tbl_meters[Name])), "not found")
I needed searchable drop-down in a table column with many rows. I used the solution of Leila, wrapped in TRANSPOSE function. This way in my helper sheet i didn`t get the #SPILL error cause the results were passed to the row cells. Then in the data validation I referred to the helper sheet using the #. I had concerns it won`t work because the results of the search are not spilled in a helper column but in rows. But it worked :). Not sure if my explanation is good, but I can send you some sample sheet if you need.
@@mihaelablagova3300 That one worked but I couldn't figure out how to copy&paste the cells with "drop-down list" and keep them linked to their own correspondent cells in the "search" column. The list I am preparing can be 10 lines or 100 lines, who knows. I can't do it one by one :(
im just new in these application and found these 11/10 tutorial, thank you for this Ms. Leila.
Just got myself into more advanced Excel and this is amazing, easy to understand and full of tips that are usefull, perfect.... Also "your" VBA is great 🙂
Hi, appreciate ur great efforts on explaining new things to us.
My question: Incase of "Not available" in your example - is there any possible way out to update the list at the dropdown area.?
+1
I gues you could with vba, using the change event. When cellvalue = Not found add text to table. Something like that. Im gonna try to make this work.
Hi Leila, Great function, However I get an error message when adding the "FILTER" part.
just check your Excell, go to FILE - ACCOUNT, check which office is showing, sometimes its shows previous version of offices, just click update button to update your office 365.
Did you solve this??
Wowww, I'm just so amazed by the teaching skills of you, Leila Jan. So concise, smooth, simple, and well-designed. And I'm not talking about this tutorial only; I've watched more than almost 10 videos, so far and I can't stop myself continuing. Just great JOBBB!!!
Wow, thank you!
love all your videos
I don't have Filter & Sort function in my excel, can u advise me how to install it? I use office 2016
$ office 365 $
Thank you very much..solved my problem as i always wanted searchable drop-down list like Google sheets in excel.
How to make searchable drop-down on Google sheets?
Empeze viendo un video especifico y termine encontrando un canal con excelente contenido y perfección al explicar! Muchas gracias
Great tutorial. So glad to have found your channel today!!!
I didn't realize it, but I think my Excel knowledge froze and stopped making new progress about 10 years ago. Learning so many new things from you. And I've only watched 3 of your videos, so far! Thank you.
Glad to have you aboard :)
This works good, however is there a way when I start typing into the cell box lets say "gar" it will pop up the choices without having to click the down arrow?
right around min 25 here ua-cam.com/video/srTteYoqcJs/v-deo.html
Great video, learned a lot. I have a question, how this data validation can be replicate in other rows? thanks
ua-cam.com/video/waqzwMCYD9I/v-deo.html
@@debjitpaul3399 Thank you!
Amazing, very well explained and simple ! Thank you
after watching many of your and others videos, your the best, clear, simple and thorough, becoming a fan....
Great tutorial. I have a quick question. What if you want to use the same lookup targets on multiple cells. I have a test script and each row has an assignee. I want to use the filtered lookup for each row based on the same list of global testers. If I copy the validation down, the filter is locked to the first cell.
Got the same issue, we are keeping a tracker of input based on the same input possibilities with over 1k rows to choose from so It would be great to have the function work on multuple rows without duplicating the drop down data sheet.
remove the f4 function for cell in the validation
Hello Angelo can you please explain bit more . How can we achieve this for whole column
@@angeloaquino1121 hello can you please explain bit more that how can we achieve this for whole column?
@@priyankamulay9140 lo que él quiere decir es es que la función f4 va a convertirla en absoluto y si quieres también puedes desactivarla de la misma manera. Presiona ctrl+f4
Is there a posibility for auto-fill-in at the moment when you begin typing?
My favorite show :) Not with Excel formulas - Excel calculations run after we click - this gives the data prep list the opportunity to update...
@@LeilaGharani Could you teach us how to do it, if possible? Perhaps with VBA? Thank you very much in advance, super grateful for all your tutorials
That was explained so perfectly! Thank you!
Hi, thank you for sharing this function. May I ask what if you want to use that dropdown function in every row, how do you make the "search cell" dynamic following the cell?
same question here..
@@wesverg658 is there an answer for this quest ? i have the same and need feedback.
is there an answer for this quest ? i have the same and need feedback.
many thanks
Have you get the solution of this?
@@rachmadhusnianto3506 - Found another tutorial that helped me to create a searchable and dynamic dropdown function.
ua-cam.com/video/fsL57bvd7Pk/v-deo.html
This only applies to a single cell search, is it possible to extend the technique to multiple rows/cells?
I tried this and you need to create a new list for each cell you want to be searchable.
Amazing Explanation! Thank you very much.
Thank you Leila. Very good!
Leila, This is very good content. However I have question, what if I have multiple drop-down cell or sheet where I want to use same master data list with Searchable Drop Down List..Is this doable?
You would need multiple search tables. The master data remains constant.
I was wondering if I could use "Goal Seek" as dynamic function to repeat it in each cell.... Thank you
A function I've been looking for. Thanks.
Hi Leila, Thanks for this guide!
ma'am can u show how to prepare school time table automatically .....
Hi Leila, would you mind to show us how to deal with multiple cells in a column using this searchable drop-down list?
At 6:28, she removed the D1 from the formula bar, then she selected B5 on the other sheet. I suppose instead of B5, you'll need to select your required cell range at this step. I'm not sure though because I don't have O365 to test this
@@SaddamKhan-jd2sj It is not working in that way.
@@SYEDMOHAMMMADKASHIF do you know the answer?? I'll have to practice this tomorrow and try to find an answer.
Please help Leila! :(
@@SYEDMOHAMMMADKASHIF there is already a video by Leila, just search: Many Searchable drop-down lists
Fantastic leila...Love new approach of Filter function!!!
Great video, very clear and easy to follow. Thanks!
Hi Leila, this is a fantastic addition to the drop-down techniques! I have a question though: if one wants to implement this say in a form where there is 3 columns of dynamic dependent drop-downs and many rows to fill in, the next challenge to solve is how to make each searchable without needing to set up a specific array column for each. I use this scenario a lot with NGOs where one might be selecting geographic info, eg State/Province -> District -> Town/Village. With up to 50 rows per form, that would be 150 filter columns to set up with this method! Any ideas of how to solve this (at least until Microsoft allows the array functions inside the validation formulas)?
Hi McKay, did you solve your problem? I am having same issue and looking to solve it as well..
@@GrishaNiwa No, unfortunately I haven't seen or figured out any straight-forward method that doesn't require tons and tons of hidden back-end columns, with all the fragility and calculation cost that requires. To be honest, I've just started pushing Google Sheets for more projects involving data entry because the auto-fill of data validation is just such a better experience.
@@McKaySavage Thank you for your feedback. At last i have moved forward with the tons and tons of hidden back-end columns...:) There are more options if you want to consider VBA .. but I disregarded this approach.
@@McKaySavage Have you considered using Slicers?
@@sarahogan1715 how would slicers help with an input form with multiple rows per form?
I love this! Thank you. How do I continue this drop-down in a column rather than just one cell, e.g. A2 is where my Drop-Down is now but I need to continue all the way to A1000?
did you solve this problem? i have the same question?
@@rcsamaral yes, she has the video on her channel that focuses on this question.
@@ianbarnette2071 which Video is that? I have the same issue
ua-cam.com/video/7mo4COng7Sg/v-deo.html
@@ianbarnette2071 Thanks heaps for your replay but I'm super sorry but I'm not sure how this solves the issue with searchable dropdown lists? Has anyone got it working? Thank you
Thank you very much. The way you teach combine many contents(formulas) to solve one problem is very good.
Amazing. Lifesaver, no words
I use excel 2007, here "filter () Dynamic array function" is absent so how can I use this formula? If have any VBA system please can you give me the link so it is very Helpful to me.
ua-cam.com/video/EKKFLt5ItKc/v-deo.html
It's working for only one cell I need similar data validation for A1:A10 cells please advise.
I need a solution for this also.
@@lordstevent9030 Yes, can it work for more than one cell? I was hoping it would until i tried it
Your is the best excel classes on UA-cam i have seen your method of explaining complex formulas is amazing and easy to follows thanks a ton for posting and sharing your experience and knowledge its very beneficial to people like me
Excellent video! Thank you.
how can i have multiple searchable drop down list ?
for example i have 50 rows, every row i need to have searchable dropdownlist, where the data is common. with single data preparation
Copy that cell and paste special. Validation.
@@hex-2748 done
@@hex-2748 That works for the normal data validation but I don't see how that would work here since the validation array is now looking at only a single cell to populate the list.
@@lordstevent9030 she has an updated video...Many SEARCHABLE Drop-Down Lists in Excel (No VBA)
Filter function doesn’t seems to be available for general public. How can we add this on our excel?
I think you need to have Office 365 installed.. I have Office 2016 and do not have the FILTER and SORT functions available.
You can use a simple IF function.
@@bluceree7312 how?
@@PaulDewseth Its been a long time can't remember but I think its actually not possible using IF.
@@bluceree7312 its fine, i found a solution for my current problem. thanks buddy
Thanks for sharing Leila. Great !
This is truly brilliant - thank you!
what if you wanted to implement this on a set of data on a row by row basis?
For example if I had the first drop down list in A2 and wanted the second drop down list in B2. And then A3 would limit B3 and so on. I can't seem to solve that issue...
Hello Chuz Lahoria, take a look at my answer.
I hope this help you.
@@jorgemacgenity1367 Don't see your reply... "I have question, what if I have multiple drop-down cell or sheet where I want to use same master data list with Searchable Drop Down List..Is this doable?"
@@kirtanpandya Here is the link to my topic: ua-cam.com/channels/Ao3Xf4NBuQxuSI1K7kADiw.html
@@jorgemacgenity1367 your link takes us to a blank, page, but I'm interested in this answer!
@@mdobejr That is his answer. Nothing. He doesn't know either.
I tried to do this a few years back with index/match, but the data got too much for Excel to handle and it slowed down terribly until the workbook crashed.
If this has been addressed, I would be very interested to know too!
I keep getting the following message when I include the Filter formula "That function isn't valid" I am using OFFICE 354 MSO? Do I need to use a different formula?
I've got the same problem, please help us....
@@abdullahdemir1243 just check your Excell, go to FILE - ACCOUNT, check which office is showing, sometimes its shows previous version of offices, just click update button to update your office 365.
@@ImranKhan-vb7ke hi Imran, I have same issue with excel 2016, it available FilterXml; filter function is only for 365?, thanks.
Did you fix this?
04/2021 and you still taking me to school.Thank you Leila
It's very satisfying watching someone know what they're doing, thank you !
The function FILTER() isn't working in my version of Excel 2016. Any suggestions please?
The new formulas will not come to Excel 2016, only Office 365. See 1:15
Is there any another option?
@Life Goes k, thanks for explanation.
Watch Neil Firth Searchable Dropdown list...if you"ll be using one cell dropdown ..for multiple cells, others use =cell("contents"), but this applies to all cells in a workbook.
ua-cam.com/video/fDB1Ktyhp3Y/v-deo.html this can act close to a filter function!
Awesome as usual
I don't have Filter formula in my excel, what is the alternate formula ?
With a combination of INDEX, AGGREGATE(15;6;Row()-(row of cell-1);...). It´s not a real matrix-formula, but you can just pull it down as well, because the value "ROW()-(row of cell-1)" will always increase by one for every following row. The 15 in AGGREGATE is for SMALL() and the 6 for ignoring false values, which is the major trick in that case. You just have to build your formula that way, that all your hits get the same value, typicaly just TRUE. It´s hard to explain, the exact formula depends on your specific needs...
But maybe you will find something on youtube or somewhere else if you search for that combination of functions...
ua-cam.com/video/c0CAlxo_BRk/v-deo.html
So Filter function will kill VLookUP, very nice!
Mostly yes - unless you just want the first match or you just have one match and you don‘t want your results to spill.
Thank you very much for your repply Leila! I admire your work.
i've learned millions of new things in this video. especially using table feature at the end blew my mind! thanks a lot!
My pleasure. I'm glad it's helpful.
Always a joy to watch and learn from your videos
filter doesn't working (is there any other function)
I did not find filter formula suggest another formula for the same work
I think it's new formula we must update. Office
so much useful, thank you Leila