The dependent drop-down is all about showing values in a drop-down list according to the selection of the value in another drop-down.
Today, in this post, I’d like to share with you a Simple 7-Steps Process to create this drop-down. But first of all, let me tell you why it is important. In the below example, you have two drop-down lists. The size drop-down is dependent on the product drop-down.
If you select the white paper in the product cell then in size drop-down will show small and medium. But, if you select gray paper then its size will be medium and large.
So here the basic idea to create a dependent drop-down list is to get the correct size as per the product name. Let’s get started.
Steps to Create a Dependent Drop-Down List in Excel
For creating a dependent drop-down list we need to use named ranges and indirect functions.
- First of all, you have to create named ranges for drop-down lists. For this, select the product list. Go to -> Formulas -> Defined Names -> Create from the selection.
- You’ll get a pop-up. Tick mark “Top Row”
- & click OK.
- By using the same steps, create two more named ranges for sizes. One is for white paper and the second is for gray paper.
Quick Tip: By using this method to create a named range, the value in the first cell will be considered as the name, and the rest of the values as the range. You can also use a dynamic named range for this.
- Now select the cell where you want to add the product drop-down and Go to -> Data -> Data Tools -> Data Validation.
- In the data validation window, select “List” and in “Source” enter the below formula and then click OK.
=Indirect(“Product”)
- Select the cell where you want to add a size drop-down list. Go to -> Data -> Data Tools -> Data Validation.
- In the data validation window, select “List” and in “Source” enter the below formula and click OK.
=Indirect(“A5”)
Finally, your dependent drop-down list is ready.
How Does it Work
First, you have created three named ranges. Then we used one named range to create a product drop-down. After that, for the second drop-down list, you have used the indirect function & refer to the value in the product cell.
If you notice, our size-named ranges have a name equal to the values we have in the product drop-down.
When we select “WhitePaperSheet” in the product cell, then in the size cell indirect function refers to the named range “WhitePaperSheet” and when you select “GreyPaperSheet” it will refer to the named range “GreyPaperSheet”.
Three-Level Dependent Drop-Down List
In the above example, you have created a two-level dependent drop-down list. But sometimes, we need to create a list with three-level dependencies. For this, all you have to do is create a third drop-down list which is dependent on the selection of the second drop-down list.
Let’s say we want to add a drop-down list with “Length x Width” of the sizes for paper sheets.
And, for this, you have to create a third drop-down list that will show the “Length X Width” as per size selection.
Here are the steps:
- Create three more named ranges using the same method which we have used above.
- Select the cell where you want to insert your third drop-down.
- Open drop-down options & insert the following formula in the source.
=Indirect(“C5”)
- Click OK.
Now, your three-level drop-down list is ready
I find when copying the validation and named ranges to other workbooks, the “indirect()” formula does not work. Is there a fix for this?
Hi Puneet
If you make a selection from all three drop downs, then go back and change the first drop down to another selection, the drop downs of second and third step can be invalid because they are based on original selections and don’t flag a change. e.g start with white paper, small, 12x 12 then change first box to gray paper. 2nd box stays small but that is not a valid option for gray
You need to insert error check formulae to highlight this.
Okay, I need some HELP! I need a total of 4-drop down lists.
Dimensions | Part Number | Description | Ribbons
The Drop Down for “Dimensions” would display various sizes, then “Part Number” would be dependent on the “Dimensions” cell, then “Description” would be dependent of the “Part Number” cell, and finally the “Ribbons” is dependent on the “Description” cell.
I don’t want to use VBA. I can get my Dimensions & Part Number to work using the “Offset” function, but then I can’t get my next two (2) drop downs to function.
Sample Data:
Dimensions Part Numbers Material Descriptions Suggested Ribbons
–Select Dim– –Select P/N– –Select Material– –Select Ribbon–
0.250″ H x 0.750″ W THT-1-724-10 UltraTemp Amber Polyimide R4300
0.250″ H x 0.750″ W THT-1-727-10 UltraTemp Gloss Polyimide R6000
0.500″ H x 1.000″ W THT-5-423-10 WorkHorse Glossy Polyester R4900, R6000, R6200
0.500″ H x 1.000″ W THT-5-423-10-YL Yellow WorkHorse Glossy Polyester R4900, R6000, R6201
0.750″ H x 1.500″ W THT-6-428-10 Metallized Polyester R4300, R4500, R6200
Any help would be GREAT!
On the basis of dependent drop down list in UserForm ,how can I saperated data in saperated worksheet by VBA
Really you are great, amazing experienced, Excel. Expert
I want to create 6 level drop-down list.
Is it possible ?
Without VBA and with VBA
Very Informative tutorial.
No.Very usefull indeed.Thanks.
Dear Puneet,
Your posts are always appreciated.
What I want to do with the three way lookup is goto 3 separate woorkbooks , then pick the MARK SHEET from that workbook then pick which column then the students name. I am doing my chidren’s Excel marksheets for shool. There are three clases, graade 11A 11D and 11H.. Then I want to pick the final mark for term 1, 2, 3 or final. Then pick the student’s name.
Kind regards,
Andre.
How do we create a dependent dropdown list, with data reset in all entries on the LHS of the entry being changed?
I’d clear the dependent list cell upon a change of the first list cell with a macro in WorkSheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$A$5” Then Range(“B5”) = “Select Paper”
End Sub
My size dropdown only shows the product name I have selected in the first dropdown. The example file gives me an error (‘The source currently evaluates to an error’) when I try and work with that, despite using your cell references.
Please share a snapshot of cell reference you have in second drop down.
How many columns of dependable drop down menu, can a person create ? Can it be like 5, 10, 20, 100, and so on rows?
Yes you can. It’s may be bit complex but not really hard.
Tell me if you need any further help.
can you share the file with example….Please….
It’s there. Check out the link.
Thanks.Puneet………..i got it.
You’re Welcome.