Create Dependent Drop Down List in Excel - EASY METHOD
Ғылым және технология
👉 If you're new to Excel and want to master the basics, check out my "Excel for Beginners" course: kevinstratvert.thinkific.com
In this step-by-step tutorial, learn how to create dependent dropdown lists in Excel. This feature allows you to select a value in one dropdown list that influences the available options in subsequent dropdown lists. It's an incredibly useful tool for creating dynamic and interactive forms, and I'll show you just how easy it is to set up.
In this tutorial, we'll create an order form for the Kevin Cookie Company, where the items in one dropdown list will filter the options available in another. For instance, if a customer selects "cookie," they'll see all our delicious cookie options, and if they choose "drink," they'll see all our refreshing drink options. We'll also set up additional dropdowns for more specific selections, like coffee types or smoothie flavors.
What You'll Learn:
- How to create named ranges in Excel.
- Setting up the first dropdown list.
- Creating dependent dropdown lists using the INDIRECT function.
- Troubleshooting common issues with named ranges.
- Making your forms more dynamic and user-friendly.
Host: Kevin Stratvert
📚 RESOURCES
- Workbook Download: Follow along with the exact same workbook I'm using by downloading it here: 1drv.ms/x/s!AmxrofZZlZ-whdt5C...
⌚ TIMESTAMPS
00:00 Introduction
00:19 Download the workbook
00:31 Background
01:07 Set up a named range
02:57 Set up first dropdown list
04:22 Create dependent dropdown lists
09:04 Troubleshoot named ranges
10:29 Testing the Dropdowns
10:49 Copy dropdowns to multiple rows
11:24 Wrap up
📺 RELATED VIDEOS
- Playlist with all my videos on Excel: • ❎ How to use Excel
📩 NEWSLETTER
- Get the latest high-quality tutorial and tips and tricks videos emailed to your inbox each week: kevinstratvert.com/newsletter/
🔽 CONNECT WITH ME
- Official website: www.kevinstratvert.com
- LinkedIn: / kevinstratvert
- Discord: bit.ly/KevinStratvertDiscord
- Twitter: / kevstrat
- Facebook: / kevin-stratvert-101912...
- TikTok: / kevinstratvert
- Instagram: / kevinstratvert
🎁 TOOLS AND DISCOUNTS
✅ 🎙️ Voicemod AI Voice Changer | 5% off | link.xsolla.com/KZBi89AY
✅ 🌐 Squarespace Websites | squarespace.syuh.net/XYaqYM
✅ 🔍 Grammarly | grammarly.go2cloud.org/SH3nL
✅ 📹 CapCut | bit.ly/installcapcut
✅ 🛍️ Shopify | shopify.pxf.io/XY9rPa
✅ 📋 Notion | affiliate.notion.so/rffva4tr71ax
✅ 🖼️ Figma | psxid.figma.com/lqjg97licpry
✅ 🤖 ElevenLabs Text-to-Speech | try.elevenlabs.io/taqepq60mptr
🎒 MY COURSES
- Go from Excel novice to data analysis ninja in just 2 hours: kevinstratvert.thinkific.com/
🙏 REQUEST VIDEOS
forms.gle/BDrTNUoxheEoMLGt5
🔔 SUBSCRIBE ON KZread
kzread.info?...
🙌 SUPPORT THE CHANNEL
- Hit the THANKS button in any video!
- Amazon affiliate link: amzn.to/3kCP2yz
⚖ DISCLOSURE
Some links are affiliate links. Purchasing through these links gives me a small commission to support videos on this channel. The price to you is the same.
#stratvert #excel
Пікірлер: 68
8:51 If you want to avoid adding an underscore in "Fruit_Smoothie", make it look more professional/readable, you can add a substitute function to replace the space with an underscore. =INDIRECT(SUBSTITUTE(B7," ","_"))
@KevinStratvert
18 күн бұрын
Love it! Great suggestion.
@jayczzzya
18 күн бұрын
Nice! I was wondering about that the minute I saw the underscore
@BBKing1977
17 күн бұрын
I was going to suggest the same thing. Personally I was thinking of using CamelCase for the named range, and then removing the space within the Indirect function, but both options would accomplish the same thing.
@jaag_tv
16 күн бұрын
In addition, you can indicate the name range directly in the "Name Box" instead of clicking the "Create from Selection". Anyway, this video shows the basic steps. Great! Looking forward for the next video!
@souzamotasacul
15 күн бұрын
Also, why Kevin it not using Tables for the list?
Simplified and well understood. Thank You for the videos!
This was the most simplest explanation ever,Kevin is living legen
@Li.Siyuan
16 күн бұрын
'most simplest"? I presume you mean "simplest". "most simplest" is a double superlative.
Congrats on 3 million subscribers ! 🎉
Name Range is one of the most overlooked features in Excel, yet one of the most powerful/useful of all.
Clicking the subscribe button from Africa, Malawi
Genius for its simplicity. I was watching your previous video regarding this subject and this last video is a lot easier.
KEVIN! This is sooo useful. I recently discovered data validation but had no idea I could make my life easier by naming the drop-down list. Thanks a mill.
Thanks Kevin! Brilliant way of using indirect method
Thank you Kevin! Brilliant explanation…👏👏👏
I submit that rather than creating a Fruit_Smoothie that you put as the source - =indirect(substitute(b7," ","_",)) - I also suggest adding headings for each of the other drinks and create a selection with a blank cell - this way on the dropdown it will have a blank cell as (the only) value for Plain Milk or Chocolate Milk selections.
Great video! I'll need to wait until there's class for intermediate to advanced. Looking forward that class (and that it's not $$)
Brilliant video!
Thanks Kevin for the tutorial. 💪
thanks for your sharing...appreciate it
In Lotus 123 -indirect- was not necessary. I don't know why MS has simplified this function. Same with the "D" functions: Dget, Dsum, etc. They are not dynamic, just useless. Thank you K.
Thanks Kevin
Always fantastic
Excellent!👍
Thank you!!
Great information, Kevin
Very nice video!
This is extremely awesome! 😊
Thanks Kevin. I am working on a food cost software template and this is handy to know.
Thank you
Thanks its helpful
Please more Excel for Accounting
Awesome...
SO, if I need training on Power BI, SQL how can I register? Also consider training on XLS forms, creating survey questionnaires in ODK etc.
Great video. So now building off this, I want to track quantities. Not just the for this order, but for history. This person orders a regular coffee with 2 sugar cookies, so I add another column for quantity, easy enough. But want to export that information to know how many coffees or sugar cookies or whatever are ordered over the month to see if I should make more or take them off the menu. Just a thought for the next video to build off of.
Happy Father's Day sir❤
That only works if the values on the first and second column are unique values. My case test requires that the drop down on the third column considers results from column one AND two. Additionally, column 2 results will essentially show a list of 2 values that can be applicable to items on column 1 but will generate different results in column 3.
can you also make a video about facebook ads ? thank you!
Appreciate the great video as always! I've never figured out a way to adjust the font size of the list within the dropdown box, or is it even possible?
This method is easier than the XLOOKUP one but I still prefer to use XLOOKUP, mostly because then you can avoid the underscores which don't look good. And also, you can add SORT to put the lists in alphabetical order. Or maybe there is a way to also have the list in alphabetical order with this Named Range method?
That’s very helpful. Just curious, will it still work if the item list were in excel tables instead of the named ranges. Thanks 🙏🏽
@TheodorHuxtable
18 күн бұрын
No cuz the list spills. it is possible, but u have to do a lot more reference many lists to get what u want.
@nboisen
18 күн бұрын
Yes, it will work if your lists are tables. But you will still have to select the table and give it a range or list name. You cannot use the table name.
@george-mattgrounder-bentle9019
18 күн бұрын
Yes it will. I think. You have to select the table column data, when defining the name range. This is very good if you want to keep adding options and the named range dynamically updates.
Need to expand to tables so the list can grow with increased items in the lists
Thank you Kevin! Question: When I attempt to copy the dropdown to multiple rows, Excel automatically keeps the value of the "Type" dropdown as the same one as the first row (in this case referencing A7, and for "Additional," it's B7). It doesn't change to A8, A9, A10, etc. Any tips for fixing this?
Hi Kevin. Is there a way to authomatically clear the depending selections once you select a different source list?
@monkfish1968
17 күн бұрын
you can do it with vba. Put this code directly into the Order Form sheet code window, not in a module Private Sub Worksheet_Change(ByVal Target As Range) Dim rngList1 As Range Dim rngList2 As Range Dim rngList3 As Range Set rngList1 = Me.Range("b7") Set rngList2 = Me.Range("c7") Set rngList3 = Me.Range("d7") If Not Intersect(Target, rngList1) Is Nothing Then ' Clear List2 and List3 when List1 changes rngList2.ClearContents rngList3.ClearContents End If If Not Intersect(Target, rngList2) Is Nothing Then ' Clear List3 when List2 changes rngList3.ClearContents End If End Sub
Create Dependent Like Subscribers 👍
That was amazing and simple. Question. If you add more to the lists is there a way that the order form will pick up the changes automatically? Or would you like me to make each column in the list a table?
@kerenferrisandco
15 күн бұрын
each list will need to be formatted as a table to be able to do this.
@KevinStratvert
15 күн бұрын
If you update the named list to include a new value, the drop-down list will automatically pick that up when someone goes to select an item. However, if someone has already chosen a value from the drop-down list, even if the list itself changes, the selected value won't change.
Like @Swane38, I would like to know why the font of the items in the drop lists is so damn small. Isn't there a way to make it bigger?
thanks good , i liked , . . . how can i do this for google sheets , thanks before 🔥🔥🔥🔥🔥🔥
what about if my list is another excel worksheet? Is there a way to also pull the price? For example, you have your item and each item has a price as a column on the list, how can the price be pulled in another column after you have selected the correct product?
Question. Can 2 different order forms refer to the same list sheet? Provided contents are similar
@KevinStratvert
12 күн бұрын
Yes, you can refer to a named list any number of times 👍
@nitrozickie97
12 күн бұрын
@@KevinStratvert thank you Kevin! Another question, I'm struggling to hide or remove the underscores from my header category in name manager. They come randomly as you mentioned. But the only solution I've managed to "clean up" is by find and replace method on my Order Form. Is there a way to get rid of those underscores that come randomly? I've also tried the indirect substitute formula but it sort of messes things up
hello, i just came from your “how to clean installe windows video” i went to download windows 11 from microsoft website to my portable ssd which i thought was ok my portable ssd has now been renamed ESD-USB which contained folders like boot, efi, sources I CANT SEEM TO FIND MY OTHER FILES THAT IS WORTH A LOT OF STORAGE DID IT REPLACE OR REMOVE THE FILES FROM MY PORTABLE SSD (sandisk)???? I rlly need yur help😢😢😢😢 i tried rebooting it but it wouldnt my laptop is TUf F15
👍👍👍👍
Is excel still worth learning?
@bobvines00
16 күн бұрын
@jeremys3951 Definitely! Many (or even most?) businesses use Excel, so learning it is recommended if you work for someone. As an Engineer, I had to learn it in order to "deep dive" into our business's Manufacturing Resource Planning (MRP II) data in order to analyze it for specific projects. If you're a student, you may very well have to use it in some college classes; I don't know what is taught in High School nowadays, so it may not be useful to you there if that's where you are now.
Interesting, but... this only works for a small number of predefined lists. How about another video where you have a bigger number of lists, or when they come from a query and aren't known in advance...
@patrickbrown907
9 күн бұрын
The way I’ve gotten around this is I make the dropdown lists I want to use a table. For example tblList[Item], tblList[Cookie], tblList[Drink] For the name manager Item = tblList[Item], Cookie = tblList[Cookie], Drink = tblList[Drink] Your first dropdown data validation formula is: indirect(“tblList[Item]”) The dependent dropdown validation is: =Indirect(A1) (assuming that’s your reference cell). The benefit of the table is that you can add items later on and you don’t have to define a set range. The downfall is if your list is large enough the dropdown always starts at the bottom, best way I’ve gotten around that is in your table leave the first row blank. I doubt this answers your question about queries or that this is the most optimal, but another way I’ve gotten it to work decently for what I need. I guess in theory if your query will always spit out lists under a specific Header you could get it to work
Are you an Ai?