In this tutorial let us see How to create dependent drop down lists in Excel
You might be knowing the data validation feature in excel using which one can create drop down list letting users to select a value from a pre-defined list.
But the interesting question would be: how can you make the values in one drop down list depend on the values in another? Nothing but, how can you make a drop down list dynamic?
I will show you an example now.
Here, in column B I have the regions of India and column C provides State names in the chosen region. If the user selects “North”, they see a list of Northern States, if they select “East”, they see a list of Eastern States, similarly for west and south regions.
So, let us see how we can create this dependent drop down list now.
1. First create the lists you need. In this example, create list of States by region in a worksheet like this.
2. Go to Menu bar, go to Formulas tab and click on Name Manager
3. Create named ranges for each list now
4. Click on New and name it as “Region” and in the refers to field, select E3:H3
5. Similarly, name the list North as E4:E10, East as F4:F11, West as G4:G8, and South as H4:H8
6. Now, let us create data validation rule to provide a drop down list for Region, select cells B4:B7, go to Data tab and select data validation.
7. Select the validation criteria as list and in the source type =Region
8. Now, create a data validation rule for the dependent drop down list, select cells C4:C7, go to Data tab and select data validation.
9. Select the validation criteria as list and in the source, type =Indirect(B4). In this formula, INDIRECT simply evaluates values in column B as references, which links them to the named ranges previously defined. That means, if b4 is north, this formula would fetch north list.
10. Once we are done with this, let us test the drop down lists to check it is working fine
11. I delete this range B4:B7, and in cell B4 I Select North from the drop down, and I go to cell C4 to select the norther states as per our dependent list. Go to cell B5 select East, go to cell C5 and select a States from the dependent list. Similarly, you can try for other cells like this.
Got it friends. It is awesome right.
So, friends this is How one can create dependent drop down lists in Excel
I hope you have enjoyed this tutorial, If yes, please do give me a like, share and comment.
For further more interesting videos, please do subscribe dptutorials.
For Personalized detail learning, write to email@example.com
If you found this video valuable, give it a like. If you know someone who needs to see it, share it. Leave a comment below with your thoughts. Add it to a playlist if you want to watch it later.
★ My Online Tutorials ► https://www.dptutorials.com
LEARNING RESOURCES I Recommend: https://www.dptutorials.com/resources
Subscribe for more Awesome Tutorials: http://goo.gl/NyAtg2
Support the Channel via shopping: http://ift.tt/2jH38PR
Tools that I use for Vlogging:
• Laptop: https://amzn.to/2E5U3MO
• Canon 200D Camera: https://amzn.to/2HglDYA
• Benro Tripod: https://amzn.to/2ExRvYj
• Blue yeti microphone: https://amzn.to/2VwZyPn
• Collar Microphone: https://amzn.to/2VyOdcM
• Bluedio Headset: https://amzn.to/2W3xUZX
• Screen recorder: https://techsmith.pxf.io/2BMjA
• Boom Arm Stand: https://amzn.to/2VCQNi7
• Zoom H1 Audio Recorder: https://amzn.to/2VDumsT
• Harison Softbox Studio Lights: https://amzn.to/2LY7x3u
• Chroma Key Green Screen: https://amzn.to/2ExTr31
• Background Support Stand: https://amzn.to/2McqnEv
• Acoustic Foam Background: https://amzn.to/2EoQ1zj
• USB RGB LED Strip: https://amzn.to/2VItKm4
• Wireless Mouse: https://amzn.to/2E23cpN
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!
You Can Connect with Me at:
excel formulas in English, excel in English, excel tutorial in English,ms excel in English,ms excel tutorial in English, learn excel in English,vlookup in excel in English, learn ms excel in English, excel training, excel tutorial, Microsoft Excel 2007, learn excel,tutorial excel, ms excel tutorial, excel tutorials,ms excel 2007,Microsoft Excel training,learn excel online,learning excel,free excel training,online excel training,advanced excel tutorial,excel tutorial, excel formulas and functions, excel formulas, excel tutorial in Hindi, excel formulas and functions in Hindi, excel tricks, excel in Hindi, excel shortcut keys, excel