Set up dependent drop-down list for multiple rows in Excel

On the web, there are many questions about how to create an expandable drop-down list that can be copied across multiple rows and very few answers. Here's a working solution.

The introduction of dynamic arrays has made our lives as Excel users a lot easier. Among many other things, they significantly simplify the creation of dynamic drop down lists. The above-linked tutorial shows how to quickly set up a multiple drop-down with dynamic arrays and make it expandable to automatically include new entries.

I felt very proud of the work we'd done until we got a few comments like this: "That works great for one row, but how to get this to work for the entire column?" A fair question. Indeed, Excel picklists are most often used in worksheets with hundreds of rows to facilitate data entry. So, we reconsidered the whole approach, worked out a formula that automatically adjusts for each row, and I'm thrilled to share this new solution with you!

Important note! Because this solution relies on the dynamic array feature, it is only applicable in Excel for Microsoft 365 and Excel 2021. In pre-dynamic versions of Excel, please use the traditional approach to creating multiple dependent drop down lists.

Source data

For starters, let's get some source data to work on. In our main table on the left, we want to have two picklists in each row, so that selecting a Dept. in the first list only displays Managers for that selected department in the second list. If you change the selection in the main list, the names in the dependent drop-down will update accordingly. Source data for multiple dropdown lists

Step 1. Structure source data

The source data for drop-down lists often come from different sources and are organized differently. So, our first step is to structure the original data according to our needs. For this, we are going to create some sort of preparation table that will list all different department names in the header row, and under each dept. name there will be a list of employees working in that specific department. To automate the work and prevent human errors, we will be using the following formulas.

To get the departments, enter this formula in H2.

=TRANSPOSE(SORT(UNIQUE(E3:E15)))

Here, the UNIQUE function extracts all the different departments from E3:E15. The SORT function arranges the results in alphabetical order so that the items of your main list will be sorted from A to Z (if you don't want that, you can remove SORT from the formula, and the dept. names will appear in the same order as in your source table). Finally, TRANSPOSE changes the output orientation from vertical to horizontal.

Please notice that the formula needs to be entered just in one cell, and the results spill into neighboring cells automatically (this feature is called a spill range).

This way, we've got the items for our main drop-down list: Getting the items for the main drop-down list

To pull the manager names, the formula in H3 is:

=SORT(FILTER($F$3:$F$15, $E$3:$E$15=H$2))

Here, we utilize the FILTER function to filter the employees that belong to a particular department ($E$3:$E$15=H$2). The SORT function arranges the names in alphabetical order (if you wish to preserve the existing order, remove it from the formula).

Like the previous formula, this one is also dynamic, and the whole spill rage is filled with the results at once. All you need to do is to drag the formula to the right to get a list of managers for each dept.

And this gives us the items for the dependent drop-down list: Getting the data for the dependent drop-down list

Tips and notes:

  • In this example, we have all the data on the same sheet to make things easier for you to follow. In your real spreadsheets, you'd better place a preparation table on a separate sheet to make sure there are enough empty cells down and to the right to accommodate all the data. As already mentioned, dynamic arrays formulas are entered only in one cell, and you cannot know how many cells the results will spill into.
  • If your goal is to make expandable drop-down lists, then use a slightly different method for the preparation table, which is discussed in this example.

Step 2. Make a primary drop-down list

With the source data properly arranged, create the first drop-down list in the usual way with the help of Excel Data Validation:

  1. Select the topmost cell in which you want the primary dropdown list to appear (B3 in our case).
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. On the Settings tab of the Data Validation dialog box, configure the rule:
    • Under Allow, select List.
    • In the Source box, enter the spill range reference pointing to the departments names in the preparation table: =$H$2#

      This syntax (cell address followed by a hash tag) refers to the entire spill range no matter how many cells it actually contains.

    • Click OK to close the dialog. Making a primary drop-down list

The primary drop-down list for the first cell is done: Primary drop-down list

Step 3. Create a dependent drop-down list

Technically, setting up a successive picklist seems pretty easy - you just need to create one more data validation rule for another column. However, there's a tricky part - the formula for the Source box. This time, you cannot use a normal spill range reference because the second drop-down list needs to take into account the selection in the first drop-down. To solve this need, we'll be referring to a relevant spill range indirectly with this formula:

INDIRECT(ADDRESS(row_num, COLUMN(preceding_col) + MATCH(dropdown_cell, dropdown_spill_range, 0), 4) & "#")

Where:

  • row_num - the number of the row in the preparation table in which the formula returning the items for the dependent dropdown resides. Our formula is in row 3 (H3:K3), so it's 3. Instead of hardcoding the row number, you can use the ROW() function, but we won't complicate things more than they already are.
  • preceding_col - a cell reference to the column preceding the leftmost column of the preparation table. In this example, the preparation table begins in H2. The preceding column is G, so we use COLUMN(G1). Here, only the column coordinate matters, the row number can be any.
  • dropdown_cell - is the address of the topmost cell containing the primary drop down list, B3 in our case. Please be sure to use a relative cell reference without the $ sign, so that the formula adjusts correctly for each row where you'll copy your drop-down.
  • dropdown_spill_range - the reference to the spill range in the preparation table containing the items for the primary drop down list. In our example, it's the departments returned by the dynamic array formula in H2, so we use this spill range reference: $H$2#

Putting all the above pieces together, we get the following formula for the dependent drop down validation rule:

=INDIRECT(ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4) & "#")

Now, simply select the topmost cell for the secondary drop-down list (C3), go to the Data tab > Data Validation, and configure the rule like this: Setting up Data Validation for the dependent drop-down list

That's it! The dependent drop-down list is good to go: The dependent drop-down list in Excel

Tip. If your source data and the preparation table are on another sheet, then include the sheet name in the lookup_array argument of MATCH, and also concatenate it with the cell reference within INDIRECT like this:

=INDIRECT("Sheet1!"&ADDRESS(3, COLUMN(G1) + MATCH(B3, Sheet1!$H$2#, 0), 4) & "#")

In the above formula, simply replace Sheet1! with the name of the worksheet where your source data and the preparation table are kept.

How this formula works:

From my experience, many of our blog visitors are eager to know not only how to apply this or that formula but also understand its internal logic. For such curious and thoughtful users, I'm providing the detailed break-down of the formula:

=INDIRECT(ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4) & "#")

On a high level, we utilize the INDIRECT function to "dynamically" refer to the spill range of managers corresponding to the department selected in the primary dropdown.

Suppose you picked Planning from the drop-down in B3. The employees of the Planning dept. are listed in column J beginning in J3. That means, we need to somehow supply the J3# reference to INDIRECT, and here's how we do it:

To get the column letter, the following 3 functions are used together:

ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4)

The COLUMN function returns the column number of G1, which is 7.

The MATCH function searches for the B3 value in list of departments beginning in H2 and return its relative position, which is 3 (Analysis, Design, Planning, …).

Adding up these two numbers gives us 10, so the ADDRESS function takes this form:

ADDRESS(3, 10, 4)

Where 3 is the row number, 10 is the column number and 4 is the relative cell reference type. The result is the "J3" reference that goes directly to INDIRECT :) and reduces our tricky formula to an easy understandable one:

INDIRECT("J3"&"#")

Concatenating a cell reference with a hash tag produces a spill range reference, so that INDIRECT returns an entire array rather than a single cell value. Done!

The beauty of this formula is that it works perfectly for a single-letter, two-letter and even tree-letter column (thank you Jonathan for pointing us in the right direction!).

Step 4. Copy drop down lists across multiple rows

This is the easiest part. For the picklists to appear in multiple cells, you can simply copy them like any other cell content by using the Copy / Paste feature or by dragging through the adjacent cells. For practice, we will try both techniques.

To copy the primary drop down list, select the cell containing it (B3) and drag the fill handle (a small square at the lower right-hand corner of the cell) through as many cells as needed: Drag the primary drop down across multiple cells

To copy the dependent drop down list, let's do a regular copy/pasting:

  1. Select the cell with the secondary drop-down (C3) and press Ctrl + C to copy it.
  2. Select all other cells where you want the dependent list to appear (C4:C12) and press Ctrl + V to paste the copied contents.
Copy the dependent drop down across multiple rows

Now, both picklists appear in every row letting you select a department and manager for each project.

Please keep in mind that the above methods copy all the contents of a cell including Data Validation and the current selection. So, they are best to be used when no item has been selected in the list yet.

If you've already selected the items, then you can use the Paste Special feature to copy the Data Validation rule.

Result: multiple rows dependent drop down list

Though our dynamic arrays solution is not a one-click setup, it's a way faster than it looks and definitely much faster than the old-fashioned approach with named ranges. Anyway, the result is worth the effort, agree?

Here it is - a multi-level drop down for multiple rows where selecting an item from the first list determines which items will appear in a secondary drop-down. Dependent drop down list for multiple rows

How to make multiple drop-down list expandable

If more data is likely to be added to your source table in the future and you wish to have new entries included in the drop-down lists automatically, then you will have to use a bit more complex versions of the formulas for the preparation table. There are 2 possible approaches here, a regular range and a full-fledged Excel table, each having its own caveats.

Approach 1. Organize source data in a table

One of the key advantages of Excel tables is automatic expansion to accommodate new data, and we are going to leverage this ability.

The first thing you do is convert source data to a table. The quickest way is to select the range E2:F15 and press Ctrl + T. For convenience, we name the table Source_data.

Next, build the formulas for the preparation table by using structured references. If you are not familiar with this syntax, it's not a problem! Just start typing a formula in a cell, select the required range in your table, and Excel will create an appropriate structured reference for you automatically based on the column names.

To extract the departments, the formula in H2 is:

=TRANSPOSE(SORT(UNIQUE(Source_data[Dept.])))

To get the manager names, enter the below formula in H3 and drag it to the right through a few more cells than there currently departments (say, through P3):

=IFERROR(SORT(FILTER(Source_data[[Manager]:[Manager]], (Source_data[[Dept.]:[Dept.]]=H$2))),"")

Play pay attention that absolute structured references should be used for the Dept. and Manager columns so the references won't shift when the formula is copied horizontally. The default in Excel tables is a relative column reference such as table[column]. To make the reference absolute, you need to repeat the column name like this: table[[column]:[column]].

The IFERROR function is used to prevent errors when the formula is copied to extra columns to the right.

If all done correctly, the preparation table expands automatically to incorporate new records: Making a preparation table expandable

Approach 2. Use a range but improve formulas

If the table references look too complex to you, you can do with a normal range too. In this case, a couple of improvements need to be made to the formulas:

  • Include some empty rows in the ranges supplied to both formulas.
  • Filter out blank cells so they won't get into your picklists.

To extract the department names, the formula in H2 is:

=TRANSPOSE(SORT(UNIQUE(FILTER(E3:E30, E3:E30<>""))))

Please pay attention that we use the range E3:E30, though currently there are far fewer records in our source table.

To get the manager names, enter the below formula in H3 and drag it through a few more columns than there are currently entries in the header row:

=IFERROR(SORT(FILTER($F$3:$F$30, ($E$3:$E$30=H$2)*($E$3:$E$30<>""))),"")

In this case, we lock the range references with the $ sign to prevent them from changing while copying the formula.

As with the previous solution, the new records appear in the preparation table once they are added to the source table. Making a multiple drop-down list expandable

Result: expandable multiple drop-down list

Whichever approach you opted for, configure and copy two Data Validation rules like explained in Steps 2 - 4 above, and the newly added items will be displayed in the drop-down lists without any extra effort on your side! Expandable multiple drop-down list in Excel

That is my go-to for making a multi-level dropdown for every row in Excel. Hopefully, this solution will be useful for you too. Anyway, I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Multiple rows dependent drop-down (.xlsx file)

Table of contents

139 comments

  1. I have over 70 cells that need to have that type of function. What would the code look like?

  2. Thank you so much! This worked perfectly. I had been struggling with this for some time. I wish I had found your page sooner.

  3. Is there any way to habe a drop down list in say cell a1. then each time u click on one of the names in the drop down list row b1 to say h1 comes up with different values. For example i'm measuring breads. so in a1 is the drop down list and each time i click on a certain type of bread the row from B to H show different values.

  4. Hello, there is a problem on the Excel online version. Take your sample file and delete some of the Dept. then the manager dropdown disappears and doesn't work after you select a Dept.

    Any fixes for this?

  5. Excellent tutorial, works wonderfully!

  6. Hey guys, great tutorial! But I'm still having some problems:

    1- I've tried replicating this formula referencing values in another sheet. In the end, I came up with the following:

    '=INDIRECT(Source!ADDRESS(4, COLUMN(D1) + MATCH(F6, Source!$E$3#, 0), 4) & "#")

    The sheet where the values are located is called "Source", the value of the 'Dept.' is located at E3:M3 ($E$3#), and the value of the 'Manager' is right underneath each 'Dept' cell. That's all good. But every time I try to add this formula (above) to a Data Validation list in another sheet, and reference it to the drop-down value of the cell F6, I get an error message telling me that this formula will result in an error.

    2- With the constant errors regarding my formula, I tried just editing and messing around with the file provided and, for some reason, the formula that you guys are using WORKS, but if I go to 'Data Validation > List > Open up that same formula [=INDIRECT(ADDRESS(3, COLUMN(G9) + MATCH(B11, $H$2#, 0), 4) & "#")] and try to save it, Excel gives me the same error as before... So no matter if I try to use this on a different sheet, if I try to use the file provided here, or if I recreate exactly the same file using the formulas provided, step by step... I still get an error.

    Any possible fixes?

    • Hello!
      Formula p.1 - I can't check the formula that contains unique references to your workbook worksheets, sorry.
      Formula p.2 - Couldn't reproduce your issue. These formulas are working fine for me.

      • The argument to the INDIRECT call has to be a text. Encase the worksheet name in quotation marks and concatenate with the ADDRESS function.

  7. Thank you very much for the wonderful magic help! I really appreciate your contribution!!!

    It works like amazing!!!

  8. i am getting this formula results in error

    =INDIRECT("Dropdown reference"&ADDRESS(2, COLUMN(D1) + MATCH(I4, 'Dropdown reference'!$E$2#, 0), 4) & "#")

    • im pretty sure I understand how the formula works from a high level as i got the first two steps to work even with them on a different table but i cannot get the dependent dropdown one to work

    • I had this same problem and I also had a space in the worksheet name that I was referencing. When I renamed the worksheet to remove the space and adjusted the formula to match, it eliminated the error.

    • In my version I had to have " and ' to get it to work in another tab.
      =INDIRECT("'ART CAT'!"&ADDRESS(3, COLUMN(L1) + MATCH(D5, 'ART CAT'!$M$2#, 0), 4) & "#")

  9. Thank you very much. This helped a lot.

  10. I'm able to get the formula provided to work well, however, I am receiving an error "#SPILL" midway through my data that is equivalent of the Manager names in the example provided. My "Departments" populate correctly in columns B:Q, and my "Managers" populate below in all the columns except N. This is where the SPILL error is. Thoughts?

  11. What if I wanted to go one more deep with Dept, Manager, Room? How would I get Room from Dept & Manager, especially if there are hundreds of rooms in the building? Would I have to make a dynamic list for each Dept & Manager combination? Would it be time to switch to VBA at this point?

  12. I can not seem to implement this formula to an Excel online file even after diagnosing all errors. Although, it did work when I downloaded the file and edited it with desktop Excel. Does anybody encounter the same problem? Is there a solution to this?

    PS: The error occurs only in the data validation formula bar. It works just fine in a regular cell.

  13. Hi,

    I am trying to make 4 simple drop downs into a drop down tree dependant on the previous drop down and also works across multiple rows.

    I.e. Site - Department - Area - Machine

    Can anyone help?

    Thanks

  14. I can't get step 2 to work. The spill range =$H$2# entered in the data validation box is looking for a vertical spill range, not the horizontal one. How do I get it to look horizontally for the list?

  15. Fantastic solution! Exactly what I was looking for. Many thanks for sharing it. Cheers,

  16. This is what I was looking for. I made a few changes so that each row's second drop-down draws from a spilled array on the same row. Essentially the same as the above solution, but it works for multiple dynamic validations of varying sizes.

  17. I am struggling with this formula once I modify it to use a Preparation Table on a different tab.

    I am using your example document and am getting the same errors as with my own data.
    - To test the formula using a Prep Table, I copied the "Source Data" and "Preparation Table" data to a new sheet called "DATASET" and made sure "Source Data" started in Column E.

    Your original formula: =INDIRECT(ADDRESS(3, COLUMN(G1) + MATCH(B3, $H$2#, 0), 4) & "#")

    Your recommended change to use the Prep Table on a different tab:
    =INDIRECT("Sheet1!"&ADDRESS(3, COLUMN(G1) + MATCH(B3, Sheet1!$H$2#, 0), 4) & "#")

    Modified to use a Prep Table on another sheet called "DATASET":
    =INDIRECT("DATASET!"&ADDRESS(3, COLUMN(G1) + MATCH(B3, DATASET!$H$2#, 0), 4) & "#")

    This reports back: 0

    Any idea why this is not working as expected?

    • It took some time, but eventually I fully understood how the formula is working which helped me get the variables corrected. The instructions detailed are accurate, I just had to get my brain wrapped around it all.

      Any advice on if I have three columns of source data??? :)

  18. Mark Seely says:
    March 6, 2021 at 1:27 am

    Hi Alexander,

    The article you reference is one that shows how to do multiple dependant drop downs for one row, and this article shows you how to create one dependant drop down for multiple rows. Now, how do create multiple dependant drop downs for multiple rows?

    I have the same problem is there a solution. I need to create multiple dependent drop down lists for multiple rows - possible surely.

    Exellent articles btw.

    Regards,

    Ivor

  19. Hi!

    Great tutorial! I am now trying to add another dependent column to this. Any suggestions on how to proceed?

    Thanks!

  20. hi guys, thank you very much for this, hoping you can help me to expand on the concept please?

    what i'm looking for is multiple dependent drop downs with a look up (i'll comma separate here just to show values):

    region, tower, practice, role, rate
    au, tower1, practice1, senior role, $100
    au, tower1, practice1, mid role $75
    au, tower1, practice1, role $60

    au, tower1, practice2, senior role, $100
    au, tower1, practice2, mid role $75
    au, tower1, practice2, role $60

    au, tower2, practice1, senior role, $100
    au, tower2, practice1, mid role $75
    au, tower1, practice1, role $60

    au, tower2, practice2, senior role, $100
    au, tower2, practice2, mid role $75
    au, tower2, practice2, role $60

    us, tower1, practice1, senior role, $100
    us, tower1, practice1, mid role $75
    us, tower1, practice1, role $60

    us, tower1, practice2, senior role, $100
    us, tower1, practice2, mid role $75
    us, tower1, practice2, role $60

    us, tower2, practice1, senior role, $100
    us, tower2, practice1, mid role $75
    us, tower1, practice1, role $60

    us, tower2, practice2, senior role, $100
    us, tower2, practice2, mid role $75
    us, tower2, practice2, role $60

    thanks in advance!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)