How to Create a Dependent Drop Down List in ExcelFREE EXCEL TIPS EBOOK - Click here to get your copy Watch Video Creating a Dependent Drop Down List in Excel An Excel drop down list is a useful feature when youre creating data entry forms or Excel Dashboards. It shows a list of items as a drop down in a cell, and the user can make a selectionfrom the drop down. This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells. Below is an example of an Excel drop down list: ![Dependent Drop Down List in Excel - Simple list]()
In the above example, I have used the items in A2:A6 to create a drop-down in C3. Read: Here is a detailed guide on how to create an Excel Drop Down List. Sometimes, however, you may want to use more than one drop-down list in Excel such that the items available in a second drop-down list are dependent on the selection made in the first drop-down list. These are called dependent drop-down lists in Excel. Below is an example of what I mean by a dependent drop-down list in Excel: ![Dependent Drop Down List in Excel - Demo]()
You can see that the options in Drop Down 2 depend on the selection made in Drop Down 1. If I select Fruits in Drop Down 1, I am shown the fruit names, but if I select Vegetables in Drop Down 1, then I am shown the vegetable names in Drop Down 2. This is called a conditional or dependent drop down list in Excel. Creating a Dependent Drop Down List in ExcelHere are the steps to create a dependent drop down list in Excel: - Select the cell where you want the first (main) drop down list.
- Go to Data > Data Validation. This will open the data validation dialog box.
![Dependent Drop Down List in Excel - Conditional - Data Validation]() - In the data validation dialog box, within the settings tab, select List.
![Dependent Drop Down List in Excel - Conditional - List]() - In Source field, specify the range that contains the items that are to be shown in the first drop down list.
![Dependent Drop Down List in Excel - Conditional - DD1]() - Click OK. This will create the Drop Down 1.
![Dependent Drop Down List in Excel - DD1 Demo]() - Select the entire data set (A1:B6 in this example).
![Dependent Drop Down List in Excel - Select Entire Range]() - Go to Formulas > Defined Names > Create from Selection (or you can use the keyboard shortcut Control + Shift + F3).
![Dependent Drop Down List in Excel - Create from selection]() - In the Create Named from Selection dialog box, check the Top row option and uncheckall the others. Doing this creates 2 names ranges (Fruits and Vegetables). Fruits named range refers to all the fruits in the list and Vegetables named range refers to all the vegetables in the list.
![Dependent Drop Down List in Excel - Top row]() - Click OK.
- Select the cell where you want the Dependent/Conditional Drop Down list (E3 in this example).
- Go to Data > Data Validation.
![Dependent Drop Down List in Excel - Data Validation]() - In the Data Validation dialog box, within the setting tab, make sure List in selected.
![Dependent Drop Down List in Excel - Settings List]() - In the Source field, enter the formula =INDIRECT(D3). Here, D3 is the cell that contains the main drop down.
![Dependent Drop Down List in Excel - Indirect Function]() - Click OK.
Now, when you make the selection in Drop Down 1, the options listed in Drop Down List 2 would automatically update. Download the Example File![]() How does this work? The conditional drop down list (in cell E3) refers to =INDIRECT(D3). This means thatwhen you select Fruits in cell D3, the drop down list in E3 refers to the named range Fruits (through the INDIRECT function) and hence lists all the items in that category. Important Note: If the main category is more than one word (for example, Seasonal Fruits instead of Fruits), then you need to use the formula=INDIRECT(SUBSTITUTE(D3, ,_)), instead of the simple INDIRECT function shown above. - The reason for this is that Excel does not allow spaces in named ranges. So when you create a named range using more than one word, Excel automatically inserts an underscore in between words. For example, when you create a named range with Seasonal Fruits, it will be named Season_Fruits in the backend. Using the SUBSTITUTE function within the INDIRECT function makes sure that spacesare converted into underscores.
Reset/Clear Contents of Dependent Drop Down List AutomaticallyWhen you have made the selection and then you change the parent drop down, the dependent drop down list would not change and would, therefore, be a wrong entry. For example, if you select the Fruits as the category and then select Apple as the item, and then go back and change the category to Vegetables, the dependent drop down would continue to show Apple as the item. ![Dependent Drop Down List in Excel - Mismatch]()
You can use VBA to make sure the contents of the dependent drop down list resets whenever the main drop down list is changed. Here is the VBA code to clear the contents of a dependent drop down list: Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 4 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
End If
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub The credit for this code goes to thistutorial by Debra on clearing dependent drop down lists in Excel when the selection is changed. Here is how to make this code work: - Copy the VBA code.
- In the Excel workbook where you have the dependent drop down list, go to Developer tab, and within the Code group,click on Visual Basic (you can also use the keyboard shortcut ALT + F11).
![Dependent Drop Down List in Excel - developer]() - In the VB Editor Window, on the left in theproject explorer, you would see all the worksheet names. Double-click on the one that has the drop down list.
![Dependent Drop Down List in Excel - double click]() - Paste the code in the code window on the right.
![Dependent Drop Down List in Excel - code paste]() - Close the VB Editor.
Now, whenever you change the main drop down list, the VBA code would be fired and it would clear the content of the dependent drop down list (as shown below). ![Dependent Drop Down List in Excel - clear content demo]()
Download the Example File![]() If youre not a fan of VBA, you can also use a simple conditional formatting trick that will highlight the cell whenever there is a mismatch. This can help you visually see and correct the mismatch (as shown below). ![Dependent Drop Down List in Excel - highlight]()
Here are the steps t0 highlight mismatches in thedependent drop down lists: - Select the cell that has the dependent drop down list(s).
- Go to Home > Conditional Formatting > New Rule.
![Dependent Drop Down List in Excel - new rule]() - In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
![Dependent Drop Down List in Excel - use formula]() - In the formula field, enter the following formula:=ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))
![Dependent Drop Down List in Excel - formula]() - Set the format.
![Dependent Drop Down List in Excel - format]() - Click OK.
The formulauses the VLOOKUP function to check whether the item in the dependent drop down list is the one from the main category or not. If it isnt, the formula returns an error. This is used by the ISERROR function to return TRUE which tells conditional formatting to highlight the cell. Download the Example File![]() You May Also Like the Following Excel Tutorials: - Extract Data based on a drop-down list selection.
- Creating a drop-down list with search suggestions.
- Select multiple items from a drop-down list.
- Create multiple drop-down lists without repetition.
- Save Time with Data Entry Forms in Excel.
Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster Name Email YES - SEND ME THE EBOOK 74 thoughts on How to Create a Dependent Drop Down List in ExcelMiroslav September 2020 at 3:30 pm Is it also possible to rewrite the formula to apply on whole column ? E.g. in column A I select Vegetable and in column B I will get a drop-down with valid vegetable values. If I change Vegetable value in A to Fruit I would get a drop-down for fruit. The expanding the data validation pattern cell by cell in excel is not a solution for me as Im generating the excel file and not creating it manually. Cherine haddad August 2020 at 2:42 am Thank you very much for the video!! what I am trying to do is when you select India all the names automatically go down in the column without you selecting one name if you select US all the names in the list will be copies in the column. How can i do it. I have 3 supervisors each have 14 team members under their name and they have to fill out a log. I dont want them to click 14 times to get their team on the log. Can you please help me. thank you. BP September 2020 at 10:34 pm You should instead use the if command. Set each cell below to check if supervisor A is selected in that cell, then set the rest of the cells to fill a certain name. Each cell would be set to only one name per supervisor.
Trey July 2020 at 12:49 pm This is great, but I have a problem. My Column headers are 2 words and not a single word like Fruit or Vegetable but rather Fruit and Vegetable and Grains and Breads. I require a 2 word header value/name. The named range uses underscore (_) between each word, which is fine, but the secondary/dependent drop-down does not populate. thanks for any tips or advice on a workaround of this nature. Shekhar July 2020 at 12:15 pm Is it possible to put the data in another worksheet of the same excel and use the indirect function? Doesnt work. Says The Source currently evaluates to an error. anybody with a simpler way of doing this that actually works and doesnt require someone to know VBA code? Small formula correction for those looking to use a conditional color change as opposed to a VBA script to highlight mismatches. The original formula: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0)) Needs to have a 0 (zero) added to the MATCH functions third argument. The updated formula is:
=ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1,0)),1,0)) For MATCH, zero is an implied default, but some of you like me may have had formatting issues based on the original formula due to how it processes the range. This error for me was being caused by the MATCH function so forcing a 0 in the third argument makes for an exact match. Otherwise, you have to sort your menu and thats a pain. To the author excellent post and thank you! kamlesh kulkarni May 2020 at 12:00 pm I want to use 3 or more drop down lists but is showing error
Please help Pete April 2020 at 7:30 am Two teammates and I have been unsuccessful in building a spreadsheet with one drop down list and two dependent drop down lists. We can create a drop down list of five entities in column one. We can create a dependent drop down list of expense categories in column two which only shows the relevant expense categories of the entity selected in column one. We can not figure out how to create a second dependent drop down list of expense details in column three which only shows the relevant expense details of the expense category selected in column two. Any help would be greatly apreciated! I would be willing to pay for assistance or make a donation to the favorite charity of anyone who could help. Theron Smith January 2020 at 2:16 pm This method seem not to be working anymore with the latest version of Excel and Windows. I am running version Office 365 MSO (16.0.12325.20328) on Windows 10 Pro version 1909. This is extremely frustrating. I have been using this type of lists for years using indirect to construct range names, where the range name reference the list to be used. Can someone please tell me why indirect lost the functionality to construct name ranges in data validation to create lists? Sumit Bansal January 2020 at 2:30 pm Hello Theron.. I am on Office 365 ProPlus and this method is working for me. I am using Version 1908. Its possible that there could be an issue with a specific version. I have not heard anything about it but will search and see if I can find anything Theron Smith January 2020 at 3:02 pm Thank you Sumit, If not for your comment I would not have done the above to the letter. It works now. I can say why it did not worked initially. My name range was defined in the Refer to: section as =OFFSET(CatPr,0,0,COUNTA(Data!$L:$L)-2,1). This was to ensure we can add more items and not showing blank spaces. However, it seems that indirect does not like it when the name range has a formula in the Refer to: section. Which is silly, is it not? Regardless, Thanks Yama March 2020 at 7:39 pm Thank you so much for the clear instructive and useful information
Akhileshver Pratap Singh January 2020 at 10:26 pm What if we have a additional column of Groceries with Vegetable and Fruit ?..i am getting values of main column as Fru./Vege./Groc.. but in dependent column i am not getting values what i am doing wrong ? Valerie Kho January 2020 at 5:32 pm I have a 3 dependant drop down list (N dependant on M, M dependant on L). and for some reason my code below seem to work as such, hope it helps: On Error Resume Next
If Target.Column = $L Then
If Target.Validation.Type = $M Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents Valerie Kho January 2020 at 12:02 pm just realised that for some reason it affects other column not defined by my above codes. any idea how I can prevent that?
Mansour December 2019 at 10:38 pm hi
First of all, this is one of the best tutorial! Thanks. I had a problem with your guidance. when I used your formula =INDIRECT(SUBSTITUTE(E5, ,_)) Excel show me this error: (a named range you specified cannot be found)
Please change (E3, ,_) to (E5, ,_). Maybe someone like me is enough lazy to just copy the formula!!!
Thank you very much. Stephen December 2019 at 8:05 pm Works great, although when I open the dependent dropdown, it defaults to the last item on the list. Any way to make it default to the top? a December 2019 at 12:06 am Danielle October 2019 at 11:39 pm I have everything working correctly for the second drop down, until I get to the end and I hit OK then it tells me the Source currently evaluates to an error. Do you want to continue? Could this be because my first drop down has numbers? I dont know why else it would not work ?! any help would be great! Theron Smith January 2020 at 2:20 pm Hi Danielle, it is due to the fact this this method is not supported anymore. I would like someone to comment why the use of the indirect function is not working anymore.
gg October 2019 at 9:03 pm Elena August 2019 at 3:05 pm The VBA code only works in your sample worksheet, if i shift the dropdown columns to the right and change the vba code accordingly it does not work. How can i get hte code to work? David October 2019 at 11:45 pm I had a similar problem in that my drop down menus were in columns F and G rather than D and E. To get the VBA code to work, I changed the line If Target.Column = 4 Then to If Target.Column = 6 Then, since F is the sixth column. The second drop down menu would then clear. Hope this helps someone.
Radha August 2019 at 1:58 am I want to use the VBA code, but I would need to add it to code already existing for the worksheet. How would you make this work? K Chaitanya Vemula August 2019 at 6:33 pm Any thoughts how to select both fruits and vegies as multi select ( in A) followed by list of fruits and vegies in column B Cyrus Paul April 2020 at 4:11 am Been looking for this as well. Any luck?
Alvin July 2019 at 4:05 pm How about if fruits and vegetables have different number of items? Lets say fruits has 5 items and vegetables has 3 items, if vegetables is selected then in drop down 2 has 2 blank items. Is there a way to eliminate those 2 blanks in drop down 2? nate August 2019 at 10:39 pm Use ctrl-select only the cells you wish to use. This allows you to omit any blank cells during the Formulas -> Create from Selection step. Alvin August 2019 at 3:49 pm I tried that. But it created an error: This selection isnt valid. Make sure the copy and paste areas dont overlap unless they are the same size and shape.
Any ideas, or sample file? Alvin September 2019 at 8:09 am Owh, never mind. Solved it by defining name for each selection. Thanks.
Jose Alvaro July 2019 at 3:30 am If i need to create named range (dependent) for cars depending on the brand but they don´t have a fixed number of cars. Does this mean I have to define the range manually per brand instead of create for selection? is there any way we could use remove the blank spaces automatically? Doug Sandstrom July 2019 at 11:45 pm Im getting an error A named range ou specified cannot be found. siddeswar July 2019 at 10:14 am How can i link this drop down to dashboard or chart? please help me Patrick Mutale July 2019 at 2:49 am Very useful tutorial. Thanks Thank you. It was pretty clear and useful. Austin June 2019 at 5:53 pm Excellent content, thank you for your expertise and help! Reem April 2019 at 3:49 pm Thank you sooo much.. very useful Vishal Mulchandani April 2019 at 5:49 pm Very Nicely explained and very useful. Thanks Kyle April 2019 at 7:24 am Mine isnt working. I followed the steps exactly, but my second row for lists shows an arrow for a dropdown list, but doesnt actually create a dropdown list based on the choice made in the first column. Kyle April 2019 at 7:27 am For clarification, when I attempt the second part of the Data Validation, I get an error message saying The Source currently evaluates to an error. But the first selection works just fine. Can someone please help me? Josh R May 2019 at 12:19 am Hey Kyle this happen to me as well. You cannot have spaces anywhere you are making a drop down list. Your list may have been two words like yellow car. Instead you would have to put yellow_car. Put underscores for all of your spaces and it should work Yeah, that wasnt it. I was running only one word options, but I forget that this message happens, but wasnt related to the multi-multi-tiered hierarchy. I still cant do a three+ level hierarchy of dropdowns.
MIKE LEWANDOWSKI August 2019 at 6:23 pm I am having the same issue. I cant get the 2nd selection to create the drop down. any ideas?
Michael Lamb March 2019 at 1:33 am for this formula - what if I had three columns in my named ranged (not two) how would the match formula change?
formula: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0)) Alexandra June 2019 at 11:06 pm I have the same question!
Pietro February 2019 at 7:20 pm youre the best, thank you so much surya kanta February 2019 at 1:42 pm Sathish Kumar January 2019 at 12:03 pm I had one excel file with complete functions and formulas along with logic and examples. Unfortunately, I had missed that file. If you have these kind of file, please share the same with me. Thanks Dan January 2019 at 7:17 am hi, i have the same trouble as Abhishek Goyanka, was this solved? Raju Ranjan January 2019 at 3:05 pm Creating a Dependent Drop Down List in Excel
I have categories name with White spaces, drop down is not working in that case.
Any solution Loui December 2018 at 11:49 pm D SATHISH KUMAR November 2018 at 2:23 pm Qazzeem November 2018 at 5:38 am Very excellent job, thank you very much. Umesh Kumar Yadav September 2018 at 12:23 pm Liz F August 2018 at 7:56 pm Wow! This was so helpful and exactly what I was looking for. I knew the solution had to be much more simple than I was originally planning to try. Brad Stines October 2017 at 6:45 am Hi Could you possibly help me with a slightly bigger formula? Pravin Prasad September 2017 at 6:46 am Satish Singh September 2017 at 8:27 pm I have a issue with name box in excel, which does not accept hypen / for eg: AM/NAME , please give me suggestion for this issue.
i am trying to populate dependent drop down box HI, How can I make the drop down list works for multiple cells and not just in a unique cell? MRJONS January 2017 at 7:32 am thanks alot very usefull trick Abhishek Goyanka November 2016 at 11:07 am Hi Sumit,
How to update the dependent list when you change primary list after initially making some selection? Right now if you select the US and then Alaska, after that if you change it to India, the state still remains Alaska. Please help.
Thanks Sumit Bansal December 2016 at 3:54 pm Hello Abhishek.. This can be done using VBA. Will try and create it and share with you Abhishek Goyanka December 2016 at 8:55 pm Hi Sumit, Really appreciate the help. Please let me know if you are able to write VBA script which accomplishes the task. Thanks again.
Cindy April 2016 at 8:48 pm I want to create two cells dependent on the data entered into the first cell. So say I have a list of Company Branches listed by city. Then I have 6 multiple lists that list the Foremen that work in each city AND I have 6 lists of Superintendents that work in each city. I created the city list in cell B2. In the Superintendents cell E3 I used =Indirect(B2) and it lists all the Superintendents working in the city showing. Now in cell E2 I want to have the Foremen that work in each city. I tried =Indirect(B2) which gives me the same list in cell E3. How do I get E2 tied to B2???? maria November 2015 at 7:27 am i have my drop down all done, but when i to look at the list , there is nothing showing, can you help me please Pablo Carrillo July 2015 at 8:05 pm Hi there, is there any way you know to do this but with a list of all countries and regions of the world without having to create as many columns as countries exist? I have the list with two columns, each row per region/country When I select one of the countries, I need the drop down list to display all the regions of that country. Shanky Singh March 2015 at 11:43 pm Thanks Sumit for sharing this! However, I have find two errros: 1 While addition to the data set (As states) and consequently using indirect formula as Indirect(States Name) doesnt show any options in list. 2 Is there any way in which cells should appear empty for that particular row if we change any data set? Deepak February 2015 at 5:07 pm Hey Sumit, I have a doubt when you choose US in Cell -E2 then the other drop down list in cell:F2 shows US cities. Lets assume, we select Alaska as city in F2 but at the same time we change the country again US to India in E2, then F2 field still shows Alaska. Is there a way cell should appear empty if we change the country ? Andres N. October 2014 at 12:02 am How do i fix this? its not letting me do the data validation following the exact steps. Diego Cestac June 2015 at 1:51 am I also have the same mistakeanybody could fix it?
Thanks!!!
Tyler August 2014 at 2:25 am How would you this with a third drop down based on BOTH the previous drop downs? For instance, adding a City. Sumit Bansal August 2014 at 12:34 pm Hi Tyler.. Thanks for dropping in.. You can create the third drop down in the similar way. In this case. city would be dependent on selected state. You would need to create named ranges for all states, and the formula would be =INDIRECT(States Name)
Comments are closed. Creating a Drop Down List |
