by The tutorial shows how to create an Excel drop down list depending on another cell by using new dynamic array functions.
Creating a simple drop down list in Excel is easy. Making a multi-level cascading drop-down has always been a challenge. The above linked tutorial describes four different approaches, each including a crazy number of steps, a bunch of different formulas, and a handful of limitations relating to multi-word entries, blank cells, etc.
That was the bad news. The good news is that those methods were designed for pre-dynamic versions of Excel. The introduction of dynamic arrays in Excel 365 has changed everything! With new dynamic array functions, creating a multiple dependent drop-down list is a matter of minutes, if not seconds. No tricks, no caveats, no nonsense. Only fast, straightforward and easy-to-follow solutions.
Notes:
- This new dynamic array way of making dropdown lists only works in Excel 365 and Excel 2021. In pre-dynamic Excel, you will have to do it the long old-fashioned way as described in Creating a dependent drop down in Excel 2019 - 2007.
- This solution is for a single row. If you want to copy your picklists down multiple rows, then follow the instructions in Dependent drop-down list for multiple rows.
How to make dynamic drop down list in Excel
This example demonstrates the general approach to creating a cascading drop down list in Excel by using the new dynamic array functions.
Supposing you have a list of fruit in column A and exporters in column B. An additional complication is that the fruit names are not grouped but scattered across the column. The goal is to put the unique fruit names in the first drop-down and depending on the user's selection show the relevant exporters in the second drop-down.
To create a dynamic dependent drop down list in Excel, carry out these steps:
1. Get items for the main drop down list
For starters, we shall extract all different fruit names from column A. This can be done by using the UNIQUE function in its simplest form - supply the fruit list for the first argument (array) and omit the remaining optional arguments as their defaults work just fine for us:
=UNIQUE(A3:A15)
The formula goes to G3, and after pressing the Enter key the results spill into the next cells automatically.
2. Create the main drop down
To make your primary drop-down list, configure an Excel Data Validation rule in this way:
- Select a cell in which you want the dropdown to appear (D3 in our case).
- On the Data tab, in the Data Tools group, click Data Validation.
- In the Data Validation dialog box, do the following:
- Under Allow, select List.
- In the Source box, enter the reference to the spill range output by the UNIQUE formula. For this, type the hash tag right after the cell reference, like this: =$G$3#
This is called a spill range reference, and this syntax refers to the entire range regardless of how much it expands or contracts.
- Click OK to close the dialog.
Your primary drop-down list is done!
3. Get items for the dependent drop down list
To get entries for the secondary dropdown menu, we'll filter the values in column B based on the value selected in the first dropdown. This can be done with the help of another dynamic array function called FILTER:
=FILTER(B3:B15, A3:A15=D3)
Where B3:B15 are the source data for your dependent drop down, A3:A15 are the source data for your main dropdown, and D3 is the main dropdown cell.
To make sure the formula works correctly, you can select some value in the first drop-down list and observe the results returned by FILTER. Perfect! :)
4. Make the dependent drop down
To create the second dropdown list, configure the data validation criteria exactly as you did for the first drop down at step 2. But this time, reference the spill range returned by the FILTER function: =$H$3#
That's it! Your Excel dependent dropdown list is ready for use.
Tips and notes:
- To have the new entries included in the drop-down list automatically, format your source data as an Excel table. Or you can include a few blank cells in your formulas as demonstrated in this example.
- If your original data contains any gaps, you can filter out blanks by using this solution.
- To alphabetically sort a dropdown's items, wrap your formulas in the SORT function as explained in this example.
How to create multiple dependent drop down list in Excel
In the previous example, we made a drop down list depending on another cell. But what if you need a multi-level hierarchy, i.e. a 3rd dropdown depending in the 2nd list, or even a 4th dropdown depending on the 3rd list. Is that possible? Yes, you can set up any number of dependent lists (a reasonable number, of course :).
For this example, we have placed states / provinces in column C, and are now looking to add a corresponding dropdown menu in G3:
To make a multiple dependent drop down list in Excel, this is what you need to do:
1. Set up the first drop down
The main dropdown list is created with exact the same steps as in the previous example (please see steps 1 and 2 above). The only difference is the spill range reference you enter in the Source box.
This time, the UNIQUE formula is in E8, and the main drop down list is going to be in E3. So, you select E3, click Data Validation, and supply this reference: =$E$8#
2. Configure the second drop down
As you may have noticed, now column B contains multiple occurrences of the same exporters. But you want only unique names in your dropdown list, right? To leave out all duplicate occurrences, wrap the UNIQUE function around your FILTER formula, and enter this updated formula in F8:
=UNIQUE(FILTER(B3:B15, A3:A15=E3))
Where B3:B15 are the source data for the second drop down, A3:A15 are the source data for the first dropdown, and E3 is the first dropdown cell.
After that, use the following spill range reference for the Data Validation criteria: =$F$8#
3. Set up the third drop down
To gather the items for the 3rd drop down list, make use of the FILTER formula with multiple criteria. The first criterion checks the entire fruit list against the value selected in the 1st dropdown (A3:A15=E3) while the second criterion tests the list of exporters against the selection in the 2nd dropdown (B3:B15=F3). The complete formula goes to G8:
=FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3))
If you are going to add more dependent dropdowns (4th, 5th, etc.), then most likely column C will contain multiple occurrences of the same item. To prevent duplicates from getting into the preparation table, and consequently in the 3rd dropdown, nest the FILTER formula in the UNIQUE function like we did in the previous step:
=UNIQUE(FILTER(C3:C15, (A3:A15=E3) * (B3:B15=F3)))
The last thing for you to do is to create one more Data Validation rule with this Source reference: =$G$8#
Your multiple dependent drop down list is good to go!
Tip. In a similar manner, you can get items for subsequent drop-downs. Assuming column D contains the source data for your 4th dropdown list, you can enter the following formula in H8 to retrieve the corresponding items:
=UNIQUE(FILTER(D3:D15, (A3:A15=E3) * (B3:B15=F3) * (C3:C15=G3)))
How to make an expandable drop down list in Excel
After creating a dropdown, your first concern may be as to what happens when you add new items to the source data. Will the dropdown list update automatically? If your original data is formatted as Excel table, then yes, a dynamic drop down list discussed in the previous examples will expand automatically without any effort on your side because Excel tables are expandable by their nature.
If for some reason using an Excel table is not an option, you can make your dropdown list expandable in this way:
- To include new data automatically as it is added to the source list, add a few extra cells to the arrays referenced in your formulas.
- To exclude blank cells, configure the formulas to ignore empty cells until they get filled.
Keeping these two points in mind, let's fine-tune the formulas in our data preparation table. The Data Validation rules do not require any adjustments at all.
Formula for the main dropdown
With the fruit names in A3:A15, we add 5 extra cells to the array to cater for possible new entries. Additionally, we embed the FILTER function into UNIQUE to extract unique values without blanks.
Given the above, the formula in G3 takes this shape:
=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Formula for the dependent dropdown
The formula in G3 does not need much tweaking - just extend the arrays with a few more cells:
=FILTER(B3:B20, A3:A20=D3)
The result is a fully dynamic expandable dependent drop down list:
How to sort drop down list alphabetically
Want to arrange your dropdown list alphabetically without resorting the source data? The new dynamic Excel has a special function for this too! In your data preparation table, simply wrap the SORT function around your existing formulas.
The data validation rules are configured exactly as described in the previous examples.
To sort from A to Z
Since the ascending sort order is the default option, you can just nest your existing formulas in the array argument of SORT, omitting all other arguments which are optional.
For the main dropdown (the formula in G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))
For the dependent dropdown (the formula in H3):
=SORT(FILTER(B3:B20, A3:A20=D3))
Done! Both drop down lists get sorted alphabetically A to Z.
To sort from Z to A
To sort in descending order, you need to set the 3rd argument (sort_order) of the SORT function to -1.
For the main dropdown (the formula in G3):
=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)
For the dependent dropdown (the formula in H3):
=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)
This will sort both the data in the preparation table and the items in the dropdown lists from Z to A:
Tip. Another fast and easy way to enter information in Excel spreadsheets is a data entry form.
That's how to create dynamic drop down list in Excel with the help of the new dynamic array functions. Unlike the traditional methods, this approach works perfectly for single and multi-word entries and takes care of any blank cells. Thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel dependent drop down list (.xlsx file)
157 comments
Hello!
Say I am tracking equipment on a large construction project that includes 4 different construction sites where equipment will be either stored or utilized. Each construction site will have its own area on an excel sheet (all 4 on the same sheet for better visualization when determining equipment needs). Using source data, I create 4 identical lists of available equipment for the project, one for each site. Is it possible to have parameters in each list such that when I select a piece of equipment that is currently in site #1, that same piece of equipment would not be show up in the same list in site #2 (or site #3 and #4, for that matter) for selection, until that piece of equipment is de-selected? I hope that makes sense! My main concern is eliminating the potential of showing the same piece of equipment in two different sites. Thank you!
Hi! You're asking beyond the advice this blog can provide. Solving this complex problem requires more than a single formula. If you have a specific question about how a function or a formula works, I'll do my best to help you.
Hi Sir,
I have three dependent values similar to Fruit, Exporters and State/Province. In the above example, there are 3 sections like the Source data, preparation table and the Dynamic drop-down area. in the Dynamic drop-down area, currently only one row and their drop-down results are shown. I have a scenario where the Dynamic drop-down is there in a separate sheet and I have to do it for multiple rows. i.e., In Row 1, User would select Orange, USA and the State list will be shown. in the Row 2 and subsequent rows, select the Fruit and Exporter, based on it show the third drop-down list for multiple rows.
Hi! If I understand the question correctly, you can create dependent dropdown lists as described in this guide. But without seeing your data, it is impossible to offer you formulas for this.
Is it possible to make the pulled data editable? When I change this data it disappears. Thank you!
Hi! If you select a new value from the drop-down list, the old value disappears and is replaced by the new value. If you want to select multiple values from the drop-down list, visit: How to make Excel drop down list with multiple selections.
Thanks, learned something new today :)
How do you make an indirect dynamic drop down for excel 2010? I managed to get the dynamic lists to work, the primary dropdown to work, but its dependent drop down doesn't work. Help most appreciated.
Hi! The following tutorial should help: Create a multiple dependent drop down list in Excel 365 - 2010.
Thanks for the insightful explainer.
I noticed that if a cell is already populated, changing the adjacent cell where the data would now not match does not depopulate the cell, meaning the cell now has erroneous info. How would one automate the cell to exclude non conforming input?
Thanks.
Hi! If I understand correctly, you can automatically remove data from a cell with a second dropdown list when the first dropdown list is modified using a VBA macro.
How can I achieve the same thing but with multiple rows in column D:E?
Like: I have several rows where the user selects a value from the first drop-down list and the cell right to it needs to be dynamically populated with corresponding values.
Your example works only if I have one row, because you will need one temporary table. How does it work with multiple rows of lists?
Hi! A drop-down list can only insert a value into one cell. You must create a drop-down list for every other cell. You can use dependent drop-down lists or use function VLOOKUP or INDEX MATCH to search for a matching value.
This worked perfectly. I had my spreadsheet with a list of businesses in different categories and those having their own sub-categories. Do the exercise on the page, don't try to immediately interpret it into your application. Just - fresh spreadsheet, apricots, oranges and Brazils. It makes complete sense when you do the exercise, and then easy to interpret into my application. Thanks, Alexander and Ablebits Team!
I have the same question as Dennis, does anyone have the answer?
Meaning: I want to perform the same cascading dynamic dropdown menu's multiple times, but this means that for each input row, I would have to make a unique temporary table. Can this be done easier? Can I put the UNIQUE and FILTER formula in the data validation list criteria?
Hi! If I understand your task correctly, this article above may be helpful. This article shows you how to use the Dynamic Array functions to create a cascading drop-down list in Excel.
Thanks Alexander! Unfortunately, this is not what I am looking for. I have a multiple dependent drop-down list as in your example, but I want to perform this exercise multiple times based on the same input table. Using the example your sent, this means that I want to be able to select fruit, then exporter, then state/province in cells E3:G3 (using the info from the preparation table), en then I want to perform this exercise again in cells E4:G4 and again and again. But this means taht I would have to build a new preparation table each time I want to perform this exercise right? Or is there a simpler way, eg by using the UNIQUE and FILTER formulas in the data validation list criteria?
Have you read the article you are commenting on? The article talks about how to use UNIQUE and FILTER formulas in the data validation list criteria. Or explain why these recommendations do not work for you.
Hi, yes I have read the article above. Probably I am not understanding it correctly, but as I read it, it explains how to use the UNIQUE and FILTER formulas to create the correct list in the preparation table. Then this list can be called in the data validation list. I have succeeded in doing so.
However, my case requires something different. Let me explain:
I have a table with headers called 'Supplier', 'Product Group', 'Product Name', 'Cost Price' and 'Discount'. I want users to be able to select their top 10-20 products in terms of procurement volume and see in one glance how much discount they can get on the total of these top products. This means that I want all of their top 10-20 products in an overview.
Since the list of products is enormous, I want them to be able to first filter by supplier, then by productgroup, and then select their top product. Then automatically cost price and discount will be shown. Afterwards, the user has to be able to select their second top product in the row below. Selecting the supplier is no problem, but then selecting the product group is, because the drop-down list still refers to the selected supplier form the first product. Do I need to create a preparation table for the total number of products I want my users to be able to select? Or can I include the combi UNIQUE and FILTER formula in the data validation criteria (so not in the preparation table)?
Hi! I can't see your data and can't give you a formula. I think it is better to create a separate dynamic table using the FILTER formula and get the data for the dropdown list from it.
Thank you for training
thanks, it as a great help
Sorry, new to validation, for some reason when I asked my last question, did not realize that Copy would work fine.
I would like to create many (dozens or more) drop down lists, for many separate cells in many rows of a spreadsheet. Each drop down list is dynamic and based on a formula that I can probably make identical (or nearly identical) to all the others. For example, the formula for the pulldown list might be this:
XLOOKUP($L$21, $Q$2:$Q$8,CHOOSECOLS($S$2:$AB$8,1,3,5,7,9),"ERR",0) where $L$21 (the variable entry) determines the entry I want to match to column Q, and the pulldown items are in some part of the columns in S-AB.
I am pretty sure I could make the formula identical in each drop down with a little more work, perhaps by using the row number of the item being searched but I'm trying to avoid volatile functions so haven't worked on this yet.
In any case, is there a way to "mass produce" drop down lists, either with an identical formula or better, a slightly variable one that can be copied down a column. It would save a lot of time if so. Thank you for the great work you do.
Hi! I recommend reading this guide: How to copy drop down list in Excel. I hope it’ll be helpful.
Svetlana, it is a terrific blog, really helped me a lot !
Thanks for the information! If I've created the dropdown lists, how would I automatically place the source formula for the remaining cells? It seems like if I copy and paste the =indirectC3 to the row the second set of data won't show up. I've been manually typing in =indirectc3, indirectc4, so on and so forth. How can I resolve that issue?
Hi! If I understand your task correctly, this article may be helpful: How to edit, copy and delete drop down list in Excel.
Thanks for the tutorial. Just what I was looking for. The formula works for the first two columns of data, when I move to the 3rd, 4th, and 5th columns I am only getting the very top row of data from my table. Any idea how to correct this? Thanks
I don't have your data, so I can't help you solve the problem.
Hi there,
Thanks for the tutorial, it is very helpful. How to do the same in two different Excel sheets, I mean having the main table in the first sheet and the data source in the second sheet?
Hi! I hope this instruction will help you solve your problem: Add dropdown from another worksheet.
Great article. Is there a way of concatenating all the functionality such that the "Preparation" columns are not necessary, and use it within a Data Validation dropdown menu?
Thanks
Hi! The Preparation columns are needed to create a drop-down list and have nothing to do with macros that allow you to select multiple values in that drop-down list. This is discussed in the first paragraph of the article.
My spreadsheet has the data dropdowns figured out. The problem I am having is that on one computer, I start typing in a name from my table and I don't have to press the drop down button to get the matching names, it just starts dynamically showing the names as I am typing. On my work computer, with the exact same spreadsheet, I have to type in a little bit of a name and then I have to go to the right of the name and manually hit the drop down button. I don't think this has to do with the spreadsheet itself, but maybe a setting inside excel.
Do you have experience with this?
Dan
Hi!
You may be working with different versions of Excel. The formula used to create the drop-down list may work differently.
Thank you for this tutorial! A follow-up question - is there a way to extend the number of rows for the dynamic drop-down section (ie have multiple rows with the dynamic drop-down list using the same source data)?
Did you get any answer for this ?
Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail.
Thanks for this tutorial. It saved me a lot of time 😀
This article was great, thanks.