Extract Data based on a Drop-Down List selection in Excel
HTML-код
- Опубликовано: 23 июл 2024
- In this video tutorial, learn how to extract or filter data in Excel based on a drop-down list selection.
This Excel trick is extremely useful in situations where you have a huge dataset and you want to extract part of it by making a selection.
0:00 Intro to the dataset
2:15 Creating a Drop-down List
3:30 Adding Helper Columns to have the formula that will help extract the data
8:18 Extract Data based on the drop-down selection
For example, suppose you have the sales transaction records or various products. You can use this technique to select the product item from the excel drop-down list and all the records for that item would get extracted and listed separately.
Since this is dynamic, you can change the selection from the drop-down, and the results would update instantly.
There are three steps in extracting data based on a drop-down selection:
1. Create a Unique list of items.
2. Create a drop-down to display these unique items.
3. Use helper columns to extract the records for the selected item.
It also uses Excel formulas (INDEX, MATCH, ROWS, and SMALL functions) to extract the data based on the drop-down selection. It can work for extracting the data on the same or different worksheet in Excel
This is a great way to give the user the flexibility to quickly filter the data and get the records that they need.
For example, you can create this to quickly extract the data based on the selection of country name from the drop-down. As soon you the selection is made, this will filter all the records for that specific country.
And you can select another country from the drop-down and it will instantly update and show you the results from the second country.
You can also extend the concept shown in this video to create multiple filters. For example, you can select country and product name and it will extract the data of records that match both the criteria.
Step-by-step written tutorial and download file: trumpexcel.com/extract-data-f...
Here are some other similar videos you may find useful:
✅ Dynamic Filter in Excel - Filter As You Type (with & without VBA) - • Dynamic Filter in Exce...
✅ Advanced Filter in Excel - • Advanced Filter in Exc...
Also, I have made all of my courses available for FREE. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-c...
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
Subscribe to get awesome Excel Tips every week: ruclips.net/user/trumpexc...
#Excel #ExcelTips #ExcelTutorial
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what topics you want me to cover in future videos.
Here are some other similar videos you may find useful:
✅ Dynamic Filter in Excel - Filter As You Type (with & without VBA) - ruclips.net/video/xBRkCv6RWcE/видео.html
✅ Advanced Filter in Excel - ruclips.net/video/ZUFEqjDLM2I/видео.html
Also, I have made all of my courses available for FREE. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
Dear sir
How can we use same in Google sheet. Pls help me to do this.
I am struggling to do same in Google sheet.
great application , is this workout example sheet available ?
@@AUSSIEMALAYALI2024 You can download the file from here: trumpexcel.com/extract-data-from-drop-down-list/
@@PiyushKumar-wl7py Google Sheets has a FILTER function that can do this. Have a look at this - spreadsheetpoint.com/filter-function-google-sheets/
how to use helpers with 2 criteria
What a phenomenal solution that can be used in so many circumstances. Thank you for the clear and through explanation!
I would love to say this is one of the most helpful excel videos I have ever watched and I have used this multiple times at my job! Thank you!
I just have to say that I don't leave many RUclips video reviews. But after looking at videos/answers for an interesting Excel problem that I had, I have to say that this is very well done, if you do it step by step.
Having somewhat of a programmer background, I overthought this six-ways-from-Sunday... looked at probably 100 other videos... and ended back here. Just do everything like he says. If you can't lock by hitting "F4," type it in. Do it all on one sheet, and if you have to copy the newly-created dynamic table to another sheet, do it at the end. If you have more columns, that's okay; just append to the right of what he displays.
It's a really good way around macros if those are the types of things that are blocked at your work, for whatever reason.
Nailed it! Thanks man.
This tutorial is amazing. It's not exactly what I was looking for but it taught me so much and I was able to make some slight adjustments to provide a resolution to our business problem. I was struggling for hours with VLOOKUP, but this did the trick. The screen capture is clear, the instructions precise and easy to follow. Thank you for such high quality content!
Please Subscribe me Dear
shut up corona
Fantastic, great that you took me from concept to outcome, and I managed multiple sheets and drop-down menus as well.
A wonderfully clear yet detailed project that has helped me enormously, many many thanks.
This tutorial is amazing. I love it. Exactly what I was looking for. Thank You so much.
This is cool. Thank you and respect from Thailand, Sir.
Thanks for this tutorial. I used this today and it worked like a charm. Your video saved me hrs of trial and error in my dashboard. So appreciative. 🙏🏽
I felt I really should thank you for sharing this. My manager was so impressed by this. Keep doing the good work. Because it is dynamic, it will come handy. Good job!
I was looking for something handy and straightforward, for quite a long time. this one is one of the best. thank you for sharing and for being precise
Why can't we use filter instead of using all these formulaes
ruclips.net/video/b1SX4wMWMq0/видео.html
This is very useful. I have a spreadsheet where I select the product category, using your information it now pulls in all the vendors who are in a certain product category. What I would like to do next is have a drop down or data validation that pulls in the returned vendors (from the product category search) in the list. Once I select a vendor it would return that vendor contact information.
This video is awesome! Thank you so much for uploading!
Have my deepest thanks dear for this wonderful tutorial . . .!!
Thank you. You have explained it well and quickly. I have a better understanding of ROWS, COLUMNS & INDEX.
Very good instructions. I used your idea to develop something i was working on. If i had not found this video, i would not have been able to finish my project. Thank You.
Amazing. This is exactly what I needed and it worked perfectly for me. Thank you.
Thanks Sumit, although here are various trainers here on youtube but I seem to find your techniques most useful and apt for my requirements. Keep up the good work !
Exactly what I was looking for. And your presentation is so good. Very easy to understand. Thank you lot
This is exactly what I was looking for. Great tutorial. Very simple. Love using Index function.
Thanks for this video it has helped me understand INDEX, ROWS, and COLUMNS. I have now completed something in excel I have wanted to do for a while and couldn't work it out. Keep up the good work.
Found all the answers needed in one place. Thank you!
This tutorial was amazing, it helped me alot. Please keep uploading these videos to people like me to learn Thank you. Will look forward for your other tutorial videos.
👍 WowWee!! This is exactly what I have been looking for, and the best part; it works flawlessly. Can't thank you enough.
This is was SO helpful! I was searching for DAYS to find the correct video. This told me exactly what I needed to do. Thank you SO much!! Continue the awesome work! 😊😊😊
Thank you for the clear tutorial!
Exactly what I needed. This was great!
Man, YOU ARE AWESOME, Thank you so much for this extremely helpful video!!!
Simple and So Effective. Thanks a lot for this video. Learnt so much. I was planning to write a VBA program to do this. Excel is great tool
Amazing! Mega helpful, very descriptive step by step! I didn't use Excel for a long time and was able to pick up very fast, due to your great explanation! Thank you again!
Glad you found the video useful!
THANK YOU!!! AWESOME AND SIMPLE!!
This video just solved my problem! Thank you so much
Glad the video helped 🙂
Thank you for a very informative and helpful video, exactly what I was looking for to solve my spreadsheet design
This is really useful lesson. Thank you so much !
This was extremely helpful. Excellent tutorial!
OMG.. I was searching all night for help with this formula. You are the only person I found with a video that made this actually make sense for the average person. Thank you so much. Really impressed.
Thanks for making this. Helped me a ton!
Thank you so much. After hours and hours browsing through different solutions came across your video. Very clear and insightful. Thank you again
Thank you!! been struggling with how to present this kind of data! such a life saver😅 I've been beating myself coz I dont seem to input any working formula for the result I want and here you are😇🙏🙏
Extremely helpful and showed multiple excel functions inside one video. It helped me a lot!. Thanks!
Glad you found it useful Ratna!
Exactly what I required. Excellent Video, clearly explained Thank you....
THANK YOU SO MUCH, THIS IS SUCH A BIG HELP WITH OUR THESIS COMPUTATION
This was very helpful. I was going to go the IF statements route which was going to be a loooong and tedious formula...
thankyou so much, very useful video, and very well explained.
Thank you so much for this tutorial and worksheet. This helped me create my dynamic lists in Mac Numbers. I've been fighting with it for a week. Now it just automatically updates. Totally thrilled :D
Glad the video helped Tina!
That's an absolute genius! Amazing!
This is a most excellent explanation with intermediate steps very clearly explained. Thanks. The next step would be to indicate how you deal with the dataset expanding or contracting, something that happens often in real life.
Simple & useful for finance & non-finance guys to prepare data models.
Excellent tutorial. Keep up the good work and please publish more Excel tips.
This video was able to help me extracting data from 28k rows. The best video I've ever watched.
Happy to know the video helped :)
You explained it in very simple way. Thanks!
Wonderful Job, I was looking forward for this method, but I wanted instead of country using dates, I made the change and it works perfectly. nice thanks man, and thanks God.
This is a good alternative for making a manual report instead of using a pivot table.
Thanks Sumeet. Just what I was looking for. Though nicely explained "WHAT IS DONE", however since "WHY IS WHAT DONE" was not so clear hence took a while to understand it.
Very helpful, and this video was posted 8years ago! Salute sir!
just wow!!! this is amazing! thanks for this. this' very helpful as i am working onto lots of data. I wonder what if under "list" we have a drop down for "all" which extracts all instead?
VERY Helpful! Thank you Sir!!!
I can really use this , thank you for posting .
Great video. Very easy to follow. Thank you.
This is an amazing piece of knowledge. I was thinking about similar usage for preparing a menu costing for a hotel. This gives a new dimension to go forward. Thank you Sumith, May you reveal new ways of doing things in Excel !!!
This was very helpful! I was trying for a week to make a catalog, I was using all kinds of ways, and finally found this simple one! Thank you very much! I just need to add pictures seperatly
This is what I was searching for quite sometime. Really explained well. But I need to view few more times to understand each formula. Thanks.
Thank you so much! this helped me with one of my projects
Oh man, this exactly what I was looking for. Thanks for sharing.
After 8 years I just stumbled across this video and it was exactly what I needed for a project at work
Thank you for such a well explained step by step tutorial, you have taught me heaps!
Thank you for a very easy to follow tutorial...this video enabled me to accomplish this difficult function quite easily. Much thanks...
Great video that helped me immensely!!!
Thank you , exactly what i was looking for
Exactly what I've been looking for! THANK YOU!!!
THANK YOU AND GOD BLESS YOU MY INDIAN FRIEND.
I was really looking for this method for few years. But today i came upto this video and it finally ended my search. Now i will start practicing.
Thank you so much for this so easy but technical solution.
Thanks a lot man this was so helpful 🙏🏼
Thanks. This is so helpful. Do you also have a tutorial about creating a drop down list that automatically shows the corresponding rows when conditions are met? Thanks again.
Thank you so much! It was a big help for me. Thank you. :)
Thank you very much for this. It's a very useful video.
This is going to make homeschooling so much easier!!! Thank you!!!!
Super useful content! Thank you!
The best video on internet for this topic. You saved me !!Thanks alot
Hi, thanks for your video.
i found a Very very very easy way to do that
put your data in table and you can use filters to do that.
But i'm thanks for your time and tech others for doing their work easier.
I know I'm late, but a few rewinds and I was able to duplicate your process. Very good work Sir. Keep it up!
Thanks ! Beautiful extract data on a drop-down list selection......................... thanks sir !
Great video! Thank you for your help!
Very Smart! Thanks for sharing
Great video. Thanks for sharing. If i want only column one and two to extract, how do i do that please?
Thanks. Very helpful.
Thanks for the Help !!
Thank You Very Much! I am eagerly awaiting your video about Excel to XML format to export to tally.
Boss this video is very much help full....thanks a lot 😘👌👋👋
Great video!! Thanks a bunch!!
So far I have referred many videos on Excel as well as VBA from this channel. I liked it very much. Good work and keep me posting.
Thank you. That's what I am looking for. :)
Great video! I appreciate how you actually broke down the steps into the 3 Helper Columns. Random issue that I'm experiencing is a #REF error instead of a #VALUE, therefore the IF ERROR formula is not helping to display blank cells. Any ideas what I might have done wrong?
what a man you are, really excellent , today I understood excel means excellent. from Malaysia
Thanks for sharing this trick.
That's so useful! Thanks a lot!
Excellent tutorial!
Very clear step-by-step explanation. thank you! SUBSCRIBED
Great! Thank you so much!