How to Create Multiple Linked Dropdown Lists in Excel

So previously I wrote about how you can create a simple dropdown list in Excel, which is perfect for any kind of data validation. If you have a range of values that people are required to enter repeatedly, it’s best to create a dropdown list so that no invalid data can be entered.

That’s all great, but what if you need a linked dropdown list? What do I mean? For example, someone choose a value in dropdown list A and you want the values to be updated in dropdown list B.

Let’s start with an example to better understand how we can use linked lists in Excel. Let’s say I have a table that has three columns that represent the brands of a car: Toyota, Honda and Nissan. The rows represent the models:

linked dropdown excel

So what we could do is have one dropdown that contained the brands, such as Toyota, Honda and Nissan and the second dropdown would have the models. If we were to select Toyota, the second dropdown would show Prius, Camry, and Solara.

So in order to do this, setup the data in a table like show above. Now select the categories (Row 1) and give the range a name in the upper left text box above column A.

excel dropdown list

Now that you named the range for the categories, you need to name the range for each of the options as shown below:

linked list excel

linked list in excel

excel tutorial

Now to create the first dropdown list, you need to click on any blank cell and then click on the Data ribbon and click on Data Validation.

data validation

Now choose List for the Allow box and type in “=carbrands” into the Source box. Note that you would type in whatever you named your categories, not necessarily “carbrands”.

dropdown list excel

Click OK and your first drop down list will appear. Now make sure to note down which cell you put the first dropdown list in because you will need it later on, i.e. E2, etc.

Now, click on another blank cell and go to Data Validation again. Choose List again, but this time type in “=indirect(E2)” into the Source. Instead of E2, you will put in the cell location for the first dropdown list you created.

excel indirect

The indirect function will basically take the choice you made in the first dropdown and use it for the second one. Note that if you get a message like “The source currently has an error, do you want to continue”, it means that you have not yet chosen a value for the first dropdown (the NULL value cannot be selected).

linked lists

That’s it! Pretty cool eh!? You can continue going deeper if you like, just use the second dropdown as the Source of the third dropdown. Enjoy!

  1. ph says:

    Thanks for the great suggestion.

    Are you able to explain how you would add a third level of categorisation e.g. If I select Honda, then select Civic how could I then select e.g. Colour Red, Blue, Green?

  2. anonymous says:

    if you're using dynamic ranges instead of named ranges it won't work. tested on Excel 2003. what a piece of crap!

  3. hB says:

    this is exactly what i needed help with – so thank you!

    However i keep getting the "“The source currently has an error, do you want to continue” box

    i followed all your steps exactly so i dont understand why i keep getting this error..

    help please!

  4. budikster says:

    this is a great post.. thanks..

    however im trying to create a drop down list with a category and sub-category with symbols like, / & and space in between the words, the range name wont accept the words with special characters.. i could use some help with this if you have any other way of doing this..

    e.g.

    Category= Animals/Pets | Arts & Entertainment

    Sub-Category for Animals/Pets= Bird Keeping/Ornithology, Horses/Equitation

    Sub-Category for Arts & Entertainment= Arts, Entertainment

    thanks hope for a feedback.. thanks..

  5. powersrps says:

    My problem is that I need to select multiple entries for car brands, which in turn would provide multiple car name entries. Is this possible?

  6. silver_rs says:

    Great link that answers most of the questions on linked Excel dropdowns:
    http://www.contextures.com/xlDataVal02.html

  7. Lolita says:

    Thank You! You have really helped me. You were the best at explaining.

  8. Latif Ahmed says:

    Excellent!

    God Bless you!

  9. D2QUINN says:

    Can the indirect cell be a reference on another worksheet? The problem I am getting is that the drop-down is locked. I can't enter anything and nothing drops down on the indirect drop-down.

    Help!

  10. anonymous says:

    I think this link is what you need
    http://www.contextures.com/xlDataVal02.html

  11. excel geek wannabe says:

    Need your help desperately. I have been trying to figure out how to have different drop downs on multiple columns on the same worksheet with NO luck whatsoever. Please help! I am about to pull my hair out.

  12. Kumuyi Omotayo says:

    I'm having problem linking my report to the main analysis sheet.

    I'm a procurement Officer dealing with several request from various departments, each request must have minimum of (3) three quotation which will be analysed showing the Description, Qty, Rate and Amount for all vendors that participated in the bid. It is simply impossible to present all this as report to your boss when you have as many as 100 request monthly with minimum of 10 request on every analysis sheet.

    what i wanna do is type eg.
    1. procurement of oil filter for Land cruiser jeep, etc and when the report is clicked it will take you to the main source where you'll see the price at which it was procured, qutities and the winner of the bid.

  13. Charlotte White says:

    When I create the table, I don't get the text box above Column A. Is there something I'm not doing? Or is it because I'm on a very old version of Excel (2004 for Mac – 11.2)

  14. Ryan says:

    Hey ,

    Hows it going ?

    Thanks for this but Im trying to do something a little different. I do sales with shirts that are imported.

    So I have lets say 3 dropdowns . Type, Size, Price.

    Type : Basic, Premium, Womens

    Size: S,M,L

    Price: $9.95.$10.00 ect

    But I would like to link those for example:

    If I click on Basic, and then I choose Medium I want the dropdown cell to say 9.95 and so on.

    Next line its a Basic, Large but theres a different price of $10 so the price automatically changes according to size.

    I suppose I dont need 3 as I can always say Basic Small, Basic Medium ect so it would change to 2 rows .

    But after that I still want the other rows to work out calculations automatically .

    Once thats set up and it automatically goes to 9.95 and then the next row adds 35% markup to whatever is in the cell next to it.

    Hope im making sense.

    Thanks

    Ryan

  15. David says:

    You don’t want linked drop-down menus here. You want a data lookup.

    In the example given on this page, Toyota makes the Prius, not Honda or Nissan. So, If you select Toyota, the next column shows you *only* the models that Toyota makes.

    In your example, there is no correlation between Type and Size. They are independent. But the price depends on both.

    What you need is a table, type and size being the columns and rows. The cells that these two columns/rows define, put the prices. For instance, Basic/S = $10, Basic/M = $12, Basic/L = $14. Premium/S = $20, Premium/M = $22, Premium/L = $24, and so on.

    Once you have your table defined, you have *independent* drop-down menus for Type and Size (or you can enter the data yourself) but in the Price column, you have a lookup function which use the table you defined.

  16. Des says:

    Perfect, perfect. And so clear. Thanx.

  17. Mayank says:

    I tried the steps but got this message ” The named range you specified can not be found” ???

  18. Ilya says:

    Everything works! Thanks! Even a 3rd and 4th drop down list using the same method works well!

    However I have a question:

    Let say you picked in first cell “Toyota”, in next “Prius”, in 3rd one “red”.

    Than you go back to first cell and pick “Honda instead of “Toyota”, but lower cells: “Prius” and “Red” will stay – How to make them clear?

    Otherwise you may get confused if you using data, other then commonly known cars.

    One more time: If I have selected items from few drop down lists, and then I go back to the first one and change that to “Honda”, I want data below not show “prius” and “red”. I want those cells to be blank again after changing value in first cell.

    Please help.

  19. Frank says:

    I’ve followed these instructions and can get the two levels of drop down to work. My problem is I now want to reproduce the drop downs on another worksheet (Sheet 1 holds the competition entry form while sheet 2 has the Club and Team names data). The Club Names work on the entry form, but I can’t find a way of getting the =INDIRECT that finds the team names associated with the select club to work on the entry form in Sheet 1.

    Any ideas please??

  20. SamMan says:

    I was wondering?

    I have a drop down list for cable size.

    In the next column, I have cable diameter.

    Is there any way to send the information to more than one cell?

    Please Help!

  21. Mauricio says:

    Thank you so much! Works great!

  22. Rick Schuknecht says:

    Your tutorial on multiple linked drop down lists was very helpful.

    I am trying to figure out how to use two drop down menus and have them set up so that when I select the value I want in the first cell from the first list then in the second cell it will only show a single pre-determined value. Example: I have 50 procedures listed by name and 50 corresponding procedure numbers. When I select procedure A from the first drop down menu, I want it’s corresponding number to appear in the second drop down cell. Can you advise on how to make that work?

  23. Somesh says:

    Kudos to you for publishing this. Found this after long search. It solved my purpose. Many thanks..

  24. Nabil Alahdal says:

    Thank you very much, that help me a lot.

Leave a Reply