Many SEARCHABLE Drop-Down Lists in Excel (No VBA)

Рет қаралды 244,930

Күн бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Enhance your Excel sheets with searchable drop-down lists, ideal for situations where you need dynamic and interactive data entry. This tutorial is especially useful for creating multiple drop-down lists on the same sheet, tailored to professionals who manage large data sets or require efficient data entry methods.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/multiple-se...
🌟 Key Learning Points:
- Searchable Drop-Down Basics: Learn the fundamentals of creating a searchable drop-down list using Excel's dynamic array formulas, a method suitable for single-cell applications.
- Replicating Lists for Multiple Rows: Discover techniques to extend your searchable drop-down lists across multiple rows, ensuring functionality throughout a larger section of your spreadsheet.
- Data Preparation Table: Understand the importance of creating a data preparation table, which involves adjusting your original data set to suit the needs of the drop-down list.
- Using Dynamic Array Formulas: Explore how dynamic array formulas like FILTER, SORT, and TRANSPOSE can be used to craft the necessary list of options for your drop-down.
- Applying Data Validation: Gain insights into applying data validation to multiple cells, allowing each cell to have its own individual searchable drop-down list.
- Bonus Formula for Related Data: Learn an additional formula using XLOOKUP to display related data (like company names) once a selection is made from the drop-down list.
🚀 Practical Applications:
- Create efficient, user-friendly data entry points in your Excel sheets.
- Implement searchable drop-down lists for tasks like inventory management, survey responses, or any scenario requiring a selection from a large data set.
- Enhance your Excel reports or databases with dynamic, interactive elements.
00:00 How to create multiple searchable drop-down lists in Excel
02:00 Combine with legacy Excel formulas like the SEARCH function
08:30 Transpose functions
10:40 Bonus XLOOKUP function
★ Link to complete Excel Dynamic Arrays Class: www.xelplus.com/course/excel-...
Check out the complete Excel for Office 365 playlist: • Excel for Office 365 &...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 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

Пікірлер: 533

@LeilaGharani 5 ай бұрын

Grab the file I used in the video from here 👉 pages.xelplus.com/multiple-searchable-dropdown

@garys2187 4 жыл бұрын

I really like the way that you explain each step and working the formula from the inside out. It helps me to understand what is really going on.

@didasssendagi9023 2 жыл бұрын

Leila, Thank you for the videos. Having watched your videos for the last three years has improved my excel skills. I appreciate your work. You are indeed a true practicing economist, who define public goods as non-rivalry and excludable. You sharing your knowledge with us doesn't reduce your content level.

@LeilaGharani 2 жыл бұрын

Wonderful! Thank you for the kind feedback!

@17aig 4 жыл бұрын

What can I say that has not been said already you opened my eyes to so many options, explaining with so much clarity!! and solutions thanks a million

@teoxengineer 4 жыл бұрын

EXCELent performance…. incredible results and miracle of Excel...Dynamic array functions are useful getting these results. Thanks Leila and Microsoft Excel team 👍✌

@dreudax3294 2 жыл бұрын

I just recently found out about this channel, its excellent, and you are an excellent teacher, it would have helped me a lot if I found it sooner. Your videos are extremely clear, and your delivery method, and production is top notch. Infact I can comfortably follow your videos at 3.3 times with perfectly legible audio, while I cap at 2.5 for most other tutorials. Thank You.

@warrenanderson412 3 жыл бұрын

Once Again... You answered my pending question when I saw the first Searchable Drop Down List you posted. You Are The Best!!!

@yaxmokwa7612 4 жыл бұрын

Liking in advance. Will watch later in the evening. Thank you Leila, always.

@jkeithgreen2220 2 жыл бұрын

I have been searching for a resolution to this specific drop down list scenario for a long time. Your videos are great and you do an excellent job explaining and walking through the steps in setting things up. You have always been and remain my "go to" when I have excel questions.Thank you so much.

@Sergio-uq2nk 3 жыл бұрын

You're really brilliant Ms. Garany for solving this out for all of us. I appreciated

@vida1719 4 жыл бұрын

What a wonderful logic in this solution! I wish we had an inbuilt Search feature in Excel, so it doesn't require data preparation tables

@rrdebby 3 жыл бұрын

I'm grateful for this.Thank you Leila.

@mariostudio7 4 жыл бұрын

I've been waiting for this!

@sharonallende690 Жыл бұрын

Leila, THANK YOU!! You have solved my problems! Love you videos, You process is so easy to follow and understand.

@benjagomez4824 3 жыл бұрын

I Love your Tutorials, They made my work to function so Smooth. THANKS A LOT !!!!

@LeilaGharani 3 жыл бұрын

Great to hear!

@liquidbydesign 3 жыл бұрын

Thanks for your video! I found a way to use multiple searchable drop-down lists with only one data validation prep list, that should reduce the file size if you have many drop-down lists. If you input the formula =CELL("contents") into a cell, that cell is updated with the most recently updated cell value in the worksheet. All I did was following the guide, using the formula =IF(CELL("content")=0,"",CELL("content")) in cell D1 and instead of Report!B5 in cell D2 I used D1. With that IF-function included, the formula returns "" instead of 0 if the last updated cell is empty. An important change to be able to browse the complete list with no text entered.

@karinaaguilar2279 2 жыл бұрын

I tried this option and works almost perfectly, but when I go to the next cell to fill, it filters and only displays the values that meet the past criteria. You said that with the IF function it would be solved, but that doesn't seem to be the case for me. Do you have any advice?

@joannelee4453 4 жыл бұрын

This is exactly what I was looking for. Thank you for this great video. You're my excel guru!

@LeilaGharani 4 жыл бұрын

Glad it was helpful Joanne!

@AhmedMIbrahim 4 жыл бұрын

Leila, you are a magician. I’m in love with Excel for more than 20years but you never failed to impressed me. I’m learning something new every time i watch your great videos. More importantly, while watching i’m smiling from start to end. It is really very entertaining 😂. I wish i can meet you in person one day.

@LeilaGharani 4 жыл бұрын

Many thanks for the kind words Ahmed. It's great that you're still dedicated to learning.

@claudemouaya5639 3 жыл бұрын

You....I send you so much love from Cameroon... appreciate what you're doing

@patricia294 3 жыл бұрын

This is just perfect and worked marvellously! Thanks for sharing!!

@mrpage221 4 жыл бұрын

Great video as usual. Thank you so much for making these videos. I've been a fan for a couple of years now and have even taken several of your classes.

@LeilaGharani 4 жыл бұрын

Great to hear Mark! Thanks for your ongoing support.

@mcd22630 4 жыл бұрын

That is a complicated solution that requires a formula with a number of functions. Thank God you are here to hold us by the hand through the entire process. Love your pronunciation of the word "character". ;-)

@LeilaGharani 4 жыл бұрын

You are very welcome :)

@ryanmiguelsantiago 4 жыл бұрын

As always, thank you Leila! Transposing the filter spill is a great idea! thanks

@LeilaGharani 4 жыл бұрын

You're so welcome!

@chopps007 2 жыл бұрын

I actually did this today before seeing this video and came up with the same solution except I made it via Columns as opposed to transposed. That’s a great solution. Something I noticed was that the size of the spreadsheet was huge and a way around this was to set the spilled range to NULL (using an IF statement) unless the corresponding validation list had an entry in it. The difference in size was staggering. I love your videos. Thank you

@saradanse1505 Жыл бұрын

Could you paste here your solution?

@miriamhodorkovsky5629 3 жыл бұрын

Leila, you are a genius. Love your videos. Thank you so much.

@anandnagarkatti 4 жыл бұрын

Got introduced to so many new functions I was unaware of. Thanks!

@LeilaGharani 4 жыл бұрын

Happy to help!

@hif99 4 ай бұрын

Super helpful video. Great Job Leila ! Thank you very much.

@alexandrevanhuysse6675 3 жыл бұрын

Amazing!! Can't express my appreciation for this video!

@LeilaGharani 3 жыл бұрын

Glad you liked it!

@cindyfink7096 4 жыл бұрын

I set out looking for this possibility this morning not even knowing what to call it. I found your first video on it, which helped, but needed the feature on multiple rows. Then I found this video and feel so lucky on my timing since you just put this out. Thank you! I will be subscribing and can't wait to check out your other videos.

@LeilaGharani 4 жыл бұрын

Welcome! Glad the videos helped :)

@taypaddy 5 ай бұрын

Miss Leila, you're just making our jobs so easy. I ca'n't thank you enough,

@LeilaGharani 5 ай бұрын

It's my pleasure! :)

@tnex 3 жыл бұрын

You are the best 😘😘😘. Exactly what I have been looking for. You just gave me a good night rest.

@sonchck6039 3 жыл бұрын

SUCH A LIFE SAVER

@arkietech5823 4 жыл бұрын

Very good explanation. I was looking for something like this. Thank you for many helpful videos. Can you do a video using multiple (about 20/worksheet) searchable drop down lists on 20-30 worksheets and all worksheets using the same master list from sheet 1 in same workbook?

@SangNguyen-bw8vh 2 жыл бұрын

Thanks a lot! Exactly what I was looking for

@MOOONCROCHET 3 жыл бұрын

Thanks a lot!!! Exactly what I was looking for!

@jasvirsingh9754 4 жыл бұрын

Hi Leila..... Amazing tutorial... thanx for all the wonderful knowledge that I have gained from you......If the reference report!b5 is replaced with function Cell("contents")....this will get value to be searched from the active cell. Now now the dropdown can be copied other cells.....No need to transpose and other steps....

@chrisweeks2602 3 жыл бұрын

nice methodical explanation. Very easy to follow. Thanks!

@Luciano_mp 4 жыл бұрын

Great Leila. Thank You!👍

@kamilkuczma5264 3 жыл бұрын

Just yesterday evening I tried to figured out how to create multiple-row searchable drop-downs using dynamic arrays. Finally i did it hard way, by using indirect address, multiple columns and manually setting up all data validation for every cell in the range (Was nightmare:P) And today's morning i just found this tutorial...Transpose...PasteSpecial-Validation...Brilliant! I feel so stupid now:) Great guide, just like always!

@meomeo999 3 жыл бұрын

Brilliant. Thank you so much!

@danielepernia2823 3 жыл бұрын

Thanks Leila , I learned a lot with your video, Now I'm cracking this thing

@josuad6890 3 жыл бұрын

Huge thanks man, seriously.

@richardcorel2471 3 жыл бұрын

Thank you for this video. Very well explained and very useful.

@user-fu6xx4lk9g 11 ай бұрын

thank you so much, i was looking for this formal

@555VEL 4 жыл бұрын

Nice approach. Thank you so much.

@mixrtraining-accountingfor8885 4 жыл бұрын

I have added new learning because of you Ms Leila. Your excel content is very helpful for my channel EXCEL-ACCOUNTING training. I am excited to learn new excel tricks so I can apply it in my Accounting Tutorial using excel because I want to grow in youtube like you. You have a new subscriber here :)

@shannonpatterson5145 3 жыл бұрын

Wish I'd seen this earlier. I used columns for dv prep which made replicating DV time consuming and prone to breaking. I don't know how I missed the Transpose formula - will help clean up the DV prep 😀 Thank you!!!

@vitietajonera4159 3 жыл бұрын

Thanks Leila for this great solution!

@OakleyTurvey 4 жыл бұрын

Great stuff as usual. Thanks once more.

@amateurhour-solderingelect345 2 жыл бұрын

Thank you for this video! Very useful for something I'm working on for work.

@samanthadaroga4811 8 ай бұрын

This was so helpful! Thank you kindly.

@AhmedHassansaad 3 жыл бұрын

The best Excel experience as always Chapeau Leila, really great work as always

@LeilaGharani 3 жыл бұрын

Thank you! 😃

@rauldelgado220 3 жыл бұрын

THANK YOU Leila! you are incredible smart!

@AhmedAkili 3 жыл бұрын

WoW, really great idea, thank you so much, it's so helpful

@manissery1956 2 жыл бұрын

At last I have a solution for my problem. Thank you very much

@HusseinKorish 4 жыл бұрын

Leila ... this is an amazing technique ... thank you very much ... i learned alot today just from one video

@LeilaGharani 4 жыл бұрын

Glad it was helpful!

@samsimkins5981 4 жыл бұрын

Fantastic! I had tried using the cut and paste special to transpose, but that doesn't solve the issue. I didn't even think of transposing the spilled range! Well done and thank you.

@willman18696 4 жыл бұрын

Incredible Leila, thanks for sharing this!

@LeilaGharani 4 жыл бұрын

My pleasure!

@kulibekov 3 жыл бұрын

Thank you very much!

@sat1460 4 жыл бұрын

i need this and u have solved with transpose ...great mam.

@Brushoi 4 жыл бұрын

Thanks a lot!!! Exactly what I was looking for! By the way, Leila, how would you be able to create multiple dependent dropdown lists for many rows like that?

@jamiladdas 4 жыл бұрын

Thank you so much .. Amazing demonstration and very easy to apply .. Saved me from a lot of headache.

@LeilaGharani 4 жыл бұрын

You are welcome!

@jonalynbonador4163 3 жыл бұрын

Thank you! Really helpful ❤

@alisobhi7341 Жыл бұрын

Great solution without VBA. I enjoyed your uTube trainings.

@jayeshhbraaval7041 3 жыл бұрын

Very useful information. You are the best....

@ManishAnand1365 Жыл бұрын

You are a life saver

@doctoratomic 3 жыл бұрын

I've only recently discovered your channel. Your Searchable Drop Down List video showed up in my recommended list and answered my long-time question if there was a solution without needing to know VBA. At the end of watching, I was asking myself, "But what about creating multiple searchable drop down lists?" Lo and Behold, the KZfaq algorithm served up the answer with your follow up video. You are a great instructor, and I look forward to checking out your other videos. You have a new subscriber!

@LeilaGharani 3 жыл бұрын

That's great! Welcome aboard :)

@amjadyahia7357 Жыл бұрын

Brilliant, very good explanation. I was looking for this long time ago, many thanks.🌺🌹

@abdullahalshafie6194 3 жыл бұрын

Thanks so much Leila you are very helpful and self challenging

@deepakmirchandani1348 Жыл бұрын

thanks a lot ma'm for the solution , of this problem i was looking for a long time. great

@TandangSugistiawan 4 жыл бұрын

Thank You So Much.. this is very helpfull

@mariostudio7 4 жыл бұрын

Excel has become really efficient since 2018! Very fast improvement!

@LeilaGharani 4 жыл бұрын

That's true. Dynamic Arrays was a big step in the right direction.

@rahulapawar7202 2 жыл бұрын

Worked like a charm, thank you very very much. Keep those helpful videos coming up.

@LeilaGharani 2 жыл бұрын

Glad it helped

@harshvrm100 4 жыл бұрын

Thank you so much . great and that’s what, i was looking for.

@LeilaGharani 4 жыл бұрын

Glad it helped!

@1991lauratjeuh 2 жыл бұрын

Thank you !!

@ismailismaili0071 4 жыл бұрын

Thank you that was epic

@axox1990 4 жыл бұрын

very intelligent way thanks

@amargunjal1 3 жыл бұрын

Thanks Leila, its very useful

@ravenhish 2 жыл бұрын

always Love your video...thx Leia...I Learn more about excel from you everyday...and I found another solution to these by using CELL function...well it has a weakness, but it better than copying the formula for each row...you should look into it...

@Michael-bg7os Жыл бұрын

You are the best!!!

@imrandaud478 Жыл бұрын

Brilliant ❤

@user-wl6kk7tg3u Жыл бұрын

Very Very Thanks

@tamersalem7542 3 жыл бұрын

Thank you 💟 very much

@wesleylim3871 4 жыл бұрын

You inspired me a lot. Because of you I was able to gret the job title that I want. 😍

@LeilaGharani 4 жыл бұрын

Wow, that's great. Congratulations!

@Firesun07 4 жыл бұрын

Awesome thanks!!

@ruthnielsen7387 3 жыл бұрын

Fantastic. Thank you so very much.

@LeilaGharani 3 жыл бұрын

Most welcome, Ruth!

@iscbala 4 жыл бұрын

Thank you Leila for your immediate response. Actually I am having the monthly subscription pack for Office 365 Personal version. Kindly clarify

@reneeroy-independentscents5223 3 жыл бұрын

lol i am here because i watched your other video and need to do the search in multiple rows! THANK YOU!! :)

@LeilaGharani 3 жыл бұрын

You're very welcome!!

@Chamchijjige 3 жыл бұрын

Thankyou very much for sharing and teaching.

@LeilaGharani 3 жыл бұрын

You are very welcome

@ituryu 4 жыл бұрын

Thank you for this insightful and useful video, it was a solution that I have been searching for, but the only issue I am encountering now is the inability to have this replicated as a copy and paste in multiple sheets in the same workbook !!!

@krn14242 4 жыл бұрын

Love the new intro... Very professional.

@LeilaGharani 4 жыл бұрын

Thank you!

@johnpatrick4185 3 жыл бұрын

Thanks!

@patriceairoldi 4 жыл бұрын

Great video, thank you Leila !

@LeilaGharani 4 жыл бұрын

Glad you liked it Patrice!

@os3m 3 жыл бұрын

You are amazing, this is exactly was i looking for, the transpose do the trick :D

@LeilaGharani 3 жыл бұрын

Great!

@shooben 4 жыл бұрын

Wow, this is awesome! Thanks!

@LeilaGharani 4 жыл бұрын

Glad you like it!

@wannzmochambari8889 2 жыл бұрын

Matur nuwun Leila

@Kingco245 11 ай бұрын

Thanks for all the helpful videos; I have gained a lot of useful insight from your channel that helps me interact better with Excel. Sorry if my question below is out of context: what areas of Excel do you think I need to know/master to be ready for a Data Entry role? I'm trying to start a new career in Data Entry