Comments on: How to make a dynamic dependent dropdown list in Excel an easy way

Creating a simple drop down list in Excel is easy. Making a cascading drop-down has always been a problem. But not any more! The introduction of dynamic arrays in Excel 365 provided a fast and easy solution. Continue reading →

Comments page 2. Total comments: 96

  1. Great tutorial, as so many of your others, thank you! This tutorial touches on what I'm looking for, but I think I'm missing something. Here's my scenario; I have a list/table of Vendors in cells F5:F10, I have their corresponding Addresses in cells G5:G10. In the same spreadsheet in cell C33 I have a dropdown menu of Vendors (from cells F5:F10), and I would like the Address (from cells G5:G10), that corresponds to the selected Vendor, to auto populate in cell D33. Your help would be greatly appreciated!

  2. What if you had another sheet that had First Name, Last Name, Email, Fruit, Exporters, State. The first 3 columns are text, but the last 3 you want validation to get the drop-downs for your source data like you had in this article. This article based it on one drop-down for which the preparation table was based on the users selection for that one drop-down. In my example, each row would be a different drop-down, which results in way too many preparation table lists.

    FirstName | LastName | Email | Fruit | Exporters | State
    Bob Smith @ Apricot
    Lisa Swoth @ Mango
    Joe Szath @ Mango
    Jill Trast @ Orange

  3. Hi, I was wondering if is there a ways for the dropdown list expand when we add more items to the full list? Automatically of course! Thanks in advance

    1. Hi Dan,

      Please see the "How to make an expandable drop down list in Excel" section in this tutorial :)

  4. Hello! Is it possible to select more than one value from the dropdown? Using your example, if I want to indicate that I used Apricot Exporters from both Algeria and Pakistan, how could I select both countries from the same dropdown and have both countries appear in the cell? Note that in this instance, I would only want to select two of the four countries -- I would not want Iran or Turkey to be "selected" from the dropdown. Thanks!

  5. Hi There,

    Thank you so much for your guide, it is very easily to follow. How can I have multiple drop downs that rely on each other for results, instead of just from left to right? For example, your Country column is dependent on the Exporter column. and that depends on the Fruit column. Is there a formula where I can select Country or Exporter, and it would filter down the Exporter and/or Fruit columns?

    Thank you

      1. So they don't necessarily replace slicer/filters?

  6. I am trying to create a continuing drop down, where after you make a selection that selection is removed and it is not available in the next drop down. For example you have a list of Minnesota, Illinois, Iowa, Missouri in column A. In B you create the drop down containing those states. I C i want whatever state chosen in B to not be available in that drop down and so on.

  7. Hi there, I have managed to get this working but my aim is to allow dropdowns on every row for people to use with data collection. When I drag the data validation down and I go to the dropdown in next row down it still references first row selection. How can I make a data entry drop down list for hundreds of rows that each are independent per row?

  8. Thank you for the great instructions! Do you have a solution that would clear, refresh or otherwise revert a drop-down back to a specific cell (i.e. Choose from Dropdown) when one of the drop-downs are selected that changes the filtered criteria?

    For example, if the current drop-downs selected are: Orange > USA > California then the user changes the first drop-down to Apricot I would like the two subsequent drop-downs to clear, refresh or show a specific message (i.e Choose from Dropdown) instead of still showing USA and California.

    Thank you,

    1. Hello!
      If you create a drop-down list using regular Excel tools, it will not automatically change.
      To solve your problem, you need to use a VBA macro.

      1. Thank you for the reply!

  9. Thank you so much for putting this explanation on here.
    I have a question though:
    I have a table in Sheet 1 which has order details from a vendor
    I have a separate sheet for a list of products which has a column for the name of the vendor from whom they are sourced. So I can make a dependant list based on the instructions given above.
    The problem I run into, is that each row in the table on Sheet 1 could have a different vendor so its not one instance of the data validation / list. So unless I can enter the filter function into the range field of the data validation where it would be sorting for each row based on the vendor selected in that row (for that purchase order), how would this work?

    To explain it in another way, I am making a table with all the purchase orders for this month. As I create a new purchase order in a new row, I select a vendor and would like to see a filtered list of products which I buy from this vendor only. Each row could have a different vendor, so a static single instance of using filter and referencing that range for the drop down might not work right?

    1. Hello!
      To list only products from the selected vendor, use the dependent dropdown list as described in this article above.

  10. Hii need some help. Suppose I create a column lke below:
    Fruits
    Vegeis
    Snacks
    Milk
    Eggs
    Now I"ll assign these to a dropdown list(1). But I want to make further more lists linked to some of thse... like a dropdown list for fruits(2) with options say apple mango papaya; same for vegies(3) with spinach crrot potato and last for snacks()4 with chips, cold drinks.
    Meaning to say that in list 1, when I select fruit so I shall be able to select one of the items from list 2, if I select vegies so an item from list 3. So bit confused, how can I achievethis!? Pls guide me. thank you in advance.

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

  11. Hi, how can i create a formula that arranges automatically, the initial date and final date that a numerical value ( a rate) is present on a list.

    something li ke this
    Date: Rate: From: To:
    01/01/2021 300 01/01/2021 06/01/2021 300
    02/01/2021 300 07/01/2021 07/01/2021 400
    03/01/2021 300 08/01/2021 10/01/2021 120
    04/01/2021 300 11/01/2021 17/01/2021 700
    05/01/2021 300
    06/01/2021 300
    07/01/2021 400
    08/01/2021 120
    09/01/2021 120
    10/01/2021 120
    11/01/2021 700
    12/01/2021 700
    13/01/2021 700
    14/01/2021 700
    15/01/2021 700
    16/01/2021 700
    17/01/2021 700

  12. Thanks for this solution.

    A quick question ...

    Is it possible to refresh the second dropdown when the first is updated?

    For example, if you choose Apricot and Algeria; and then subsequently select 'Orange' the second box doesn't refresh until you click on the dropdown and select the new exporter.

    Is it possible to refresh the second box when the first is updated, either to select the first in a newly filtered list or to change this to a blank selection?

    Unfortunately, I'm thinking that the answer may need to be in VBA ....

    1. I am also looking for an answer to this scenario, can anybody help. For me the scenario is In column 1 I have a Project Code and in Column B I have a list of task that are part of each project code. If I go ahead with the dropdown, it only get updated in the referred cell ie A13.
      =FILTER(Table01[Project Task],Table01[Project Code]='TS_APR 22_JC'!A13)

      So A13 I select the Project Code and in B13 I can see the relevant task in the dropdown
      BUT
      When I go to A14 and select another Project Code B14 does not change, it still shows the list that is found in B13

  13. Hello, this was just the information I was looking for! Very helpful instructions and easy to follow. What if you have a 4th dependent drop down, or 5th, 6th, etc.? Could you explain how to construct the formula for the 4th column in the preparation table?

    1. Hi Tim.

      Simply, add more criteria to the FILTER function. Also, use the UNIQUE function for all the previous drop-downs, so they contain only one instance of each item.

      In the "Create a multiple dependent drop down list" example:

      The formula to get the items for 3rd dropdown (G8 in the preparation table) would be:

      =UNIQUE(FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3)))

      The formula to get the items for 4rd dropdown (H8 in the preparation table) would be:

      =UNIQUE(FILTER(D3:D15, (A3:A15=E3) * (B3:B15=F3) * (C3:C15=G3)))

      Where D3:D15 are the source data for the 4th drop-down.

  14. Your instructions are clear, very well written and quite easy to follow. Thank you! The was exactly what I've been searching for this afternoon. It took me all of a few minutes to set this up on my spreadsheet.

    However I, like several commenter here, are wondering how to get this to work for multiple rows. Dropdown lists like these are most often used in spreadsheets with dozens or hundreds of rows to facilitate data entry.

    This only seems to work for one dropdown list in D3 and the dependent list works only in E3.

    How do you make this work for cells D4/E4, D5/E5, D6/E6 etc? Adding the List in Data Validation to the succeeding rows is easy. Making the list change in column H to reflect the value selected in D4 just seems to have no answer.

    =FILTER(B3:B20, A3:A20=D3) the sticking point in the formula is the D3 designation at the end. Nothing I've tried so\r far works.

    Any insight you could give would be greatly appreciated.

    Thank you!

    2. Hi Julie,

      Unfortunately, there is no easy way to get this FILTER formula to work for multiple rows. However, a solution does exist and we will describe it in a separate article after the holidays.

      In the meantime, you can create a dynamic cascading drop-down list in the old-fashioned way by using Excel tables. Please pay special attention to the tip that explains the importance of using appropriate cell references to allow drop-downs to copy to other cells correctly.

  15. Excellent post and super easy to follow. Works great when only needing one row of dynamic dropdown data

    I think my question has been asked above, but let me restate it a different way:

    How do I make the last argument =D3 portion of =FILTER(B3:B20,A3:A20=D3) dynamic??

    I’d like to drag or copy the picklists for Fruit and Exporter, down multiple rows.
    I.e. Mango chosen in D3 shows the picklist of Mango Exporters in E3;
    Apricot chosen in D4, should show picklist of Apricot Exporters in E4;
    Orange chosen in D5, should show picklist of Orange Exporters in E5;
    etc….on down for 10+ rows.

    The Exporters (col. H) on the Preparation table continues to look to D3 for the Fruit filter of Mango, rather than updating to the next row's pick (D4) of Apricot.
    Is there a way to make the FILTER function dynamic?

    Thank you for sharing this great post!

    2. Very well stated @Lori. Same question!!! Please help.

  16. Hello
    When I have the table and the primary & dependent drop-downs on the same sheet, it works fine
    However, when I have the table on sheet-2 and the primary & dependent drop-downs on sheet-1, I get a source error on dependent dropdown validation cell (the dropdown works fine)
    So, can the dependent dropdown list be on separate sheet from the table?
    Thank you

    1. Hello!
      the dependent dropdown list can be on another sheet. The data link must contain the sheet name:

      =Sheet1!B1:B10

      I hope my advice will help you solve your task.

      1. Yes the sheet name is there
        Okay, thank you

  17. Does excel 2016 has Unique function?? If not, how would I get unique values in dropdown

    1. Hello!
      This function is currently available to Microsoft 365 subscribers in Current Channel. It will be available to Microsoft 365 subscribers in Semi-Annual Enterprise Channel starting in July 2020. We have a special article that shows how to find uniques.

  18. Hi Svetlana. I really enjoyed the post.

    Currently, when you select another top-level item (fruit) the sub-lists may display invalid options - ie, where the exporter does not deal in that fruit category. Is there a simple way to clear the sub-list selections or is this a job for vba?

    1. I would like to know this as well!

  19. Hi, what if i have multiple rows in for the dynamic drop-down? all based on the same set of source table and dependent dropdown? how should i expand to dynamic drop down?

    1. Hello!

      The solution described on this page only works for a single row. But because many people here asked for a multiple rows dropdown, we slightly changed the approach (a bit more complex formulas for the preparation table and a dependent drop-down are needed), and published the solution in a separate tutorial:
      Create a dependent drop-down list for multiple rows

  20. Good post. But hod do you do in case of multi level dependendant drop down menu in case you want to have more that one entry row? I mean. The example shows only three cells where to use the drop down menus. If you want to have a matrix type 3*3? Best

    1. I second this.
      I need to create a user generated table from a controlled data source.
      In the example above, if the table (D3:E3) is extended for a second input (D3:E4) and the second row Fruit is set to Apricot, how can the second dropdown be dynamically recalculated without having to create another helper column? This would grow to an enormous lookup table for larger data sets!
      Thanks

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)