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
Hi,
I have a master workbook containing a code list (B2:B2000) that gets updated throughout the year, currently with data only in cells B2:B15. I am setting up a template workbook with a data validation list referring to the code list, however because both the master workbook and template workbook need to be kept open for data validation list to work this is problematic. The template will be used by multiple users to create their personal workbook, hence why it is impractial to update the code list on all the user workbooks.
As a workaround, in the template workbook I have referenced the list from the master workbook, and then set up the drop down list in the template using data validation. However, the drop down list now contains zeros for all of the cells in the master workbook that currently don't contain any data yet (B16:B2000). How do I go about removing the zeros from the list, seeing as I cannot a dynamic data validation list in this instance?
Or there an better solution altogether in using a data validation list in a workbook that refers to a dynamic list in another workbook?
Hello!
I recommend reading this guide: Exclude blank rows from the dynamic cascading dropdown. I hope it’ll be helpful.
What if I have multiple rows of drop downs?
Hi Asif ,
Please follow the steps described in How to make a dependent dropdown for multiple rows.
I am trying to create a drop down list where the depending on what date is input the corresponding drop list will appear. For example if a user adds a date 01/06/2022 1 drop down list will appear however, if the user adds the date 01/06/2021 a different drop down list will appear. -
01/01/1900 to 31/03/2021 01/04/2021 to 24/11/2021 25/11/2021 to 31/03/2022 01/04/2022 to 31/03/2023 (Date range)
2020/2021 2021 DEC 2021/2022 2022/23 (Drop down list title)
£292.00 £293.00 £335.00 £344.00 Drop down list values)
£512.00 £515.00 £557.00 £573.00
£0.00 £0.00 £0.00 £0.00
I hope that the 'table' makes sense as to what I am trying to do.
This tutorial worked brilliantly. Thank you so much for sharing this information. I have been able to setup dependent drop down list for three data collection points. I did run into one problem. I think I may have missed something. While I was able to get it to work in the initial cells that I have setup I can no make the formula apply to the entire column despite special pasting the formula. I was wondering how to get the dependent drown down list to work for multiple rows?
Any help would be appreciated. Thank you so much.
Hello!
Maybe this guide will be helpful: How to copy a dropdown into multiple cells.
Hi Rosemary,
We have a separate tutorial that covers your case: How to create a dependent drop-down list for multiple rows.
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!
Hello!
You can get the address corresponding to the selected supplier using the VLOOKUP function.
For example,
=VLOOKUP(C33,F5:G10,2)
You can also use INDEX MATCH functions for this:
=INDEX(G5:G10,MATCH(C33,F5:F10,0))
Hi, I can’t seem to find a good solution for wanting to have my list for drop down list in another sheet AND be dynamic so the user can add or delete and it will automatically adjust the drop down. So, sheet 1 has a column, sheet 2 has a column with the valid names that the column in sheet 1 will display. If the user deletes a cell, and/or adds a cell in the sheet 2 list it will be reflected in the drop down on sheet 1.
I’ve tried making sheet 2 column a table, naming it, and data validation in sheet one but I can’t get it to refer to the table in another sheet. Or when I get close it won’t update.
Lastly, even manually I cannot just delete a cell in a table, or I can but it leaves a blank. The user will be confused by this. Any help at all appreciated.
Hello!
Maybe this article will be helpful: Make a dropdown list from another workbook.
You can also create a dynamic named range and use it in the formulas recommended in the article above.
I hope this will help, otherwise don't hesitate to ask.
Hey, thanks and WOW, quick response! I will check those out, thanks very much!
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
I found it in your other post (https://www.ablebits.com/office-addins-blog/dependent-dropdown-list-multiple-rows-excel/). I have been looking all over for this, but up until now all I could find was posts about the single dropdown. Thank you so much. My only issue now is that I need to go 3 deep with the drop-down, but I post that in the other article.
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
Hello!
You can learn more about how to create a dynamic (automatically updated) Excel dropdown in this article: Create drop down list in Excel: static, dynamic, editable, searchable.
Hi Dan,
Please see the "How to make an expandable drop down list in Excel" section in this tutorial :)
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!
Hello!
Only one value can be selected from the drop-down list. You can use two drop-down lists in adjacent columns.
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
Hi!
The dropdown list only works in one direction, like any other selection.
So they don't necessarily replace slicer/filters?
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.
Hello!
Try using the FILTER function to get the values for the new drop down list.
=FILTER($A$1:$A$6,($A$1:$A$6 < > B1)*($A$1:$A$6 < > C1))
Hope this is what you need.
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?
Hello!
If you want to copy your drop-down list down multiple rows, then read the instructions: Dependent drop-down list for multiple rows.
Hello,
I've tried the steps you've provided for a dependent drop down for my data and keep getting stuck at the second point with the filter= formula
I am trying to create three drop downs, dependent on three levels. For example:
Video Surveillance Accessories Illuminators
Video Surveillance Analog Cameras Panoramic Cameras
Video Surveillance Analog Cameras Body Cameras
Video Surveillance Analog Cameras Box Cameras
Video Surveillance Analog Cameras Bullet Cameras
Video Surveillance Analog Cameras Covert Cameras
Video Surveillance Analog Cameras Dome Cameras
Video Surveillance Analog Cameras PTZ Security Cameras
Video Surveillance Analog Cameras Thermal Imaging Cameras
Video Surveillance Analog Cameras Turret Cameras
Video Surveillance Analog Cameras Mobile & Vehicle Dash Cameras
Video Surveillance Analog Cameras Wireless Cameras
Video Surveillance Camera Lenses Fixed Lenses
Video Surveillance Camera Lenses Varifocal Lenses
Video Surveillance Camera Lenses Parts & Accessories
Video Surveillance Dummy Cameras
Video Surveillance HDoC Cameras Panoramic Cameras
Video Surveillance HDoC Cameras Body Cameras
Video Surveillance HDoC Cameras Box Cameras
Video Surveillance HDoC Cameras Bullet Cameras
Video Surveillance HDoC Cameras Covert Cameras
Video Surveillance HDoC Cameras Dome Cameras
Video Surveillance HDoC Cameras PTZ Security Cameras
Video Surveillance HDoC Cameras Thermal Imaging Cameras
Video Surveillance HDoC Cameras Turret Cameras
Video Surveillance HDoC Cameras Mobile & Vehicle Dash Cameras
Video Surveillance HDoC Cameras Wireless Cameras
So, if they select Video Surveillance, all of the options from the second column would be available.. and then once they select the second column, only the options that are associated with that level would appear. Can someone help me?
What code will i get if i have 2 dropdown list in the same table:
ex:
BUILDING AGE i (iii,ii) (iv-a,iv-b) (v-a,v-b,v-c)
3 Years 8.90% 6.90% 5.60% 4.20%
4 Years 11.70% 9.00% 7.40% 5.50%
5 Years 14.50% 11.10% 9.10% 6.70%
6 Years 17.20% 13.10% 10.70% 8.10%
Question:
if i have dropdown list (Building age) and (i * iii,ii * iv-a,iv-b * v-a,v-b,v-c)
what code for the target 5.60% if i choose 3yrs(building age) and (iv-a,iv-b) .
How to get your target for two dropdown in the same table
ex:
YEAR NAME % fruit
25 juan 5
3 maria 7
50 leo 9
IF i have dropdown list of (YEAR) and (NAME&PERCENT), what will be the code if i click *3 (year) and *
High,
I have a situation in hand that requires me to create a solution for the example below:
I nedd to prepare proposals for drywall jobs. I need to be able to alternate between various thickness of insulation, and each thickness have its price.
I would like to be able to have a dropdown list where the prices would adjust with each choide of thickness made.
Could you help?
Thanks
Hello!
Create a drop-down list to select the thickness. Then create a dependent dropdown list for the price as described in the article above. Also, instead of the second dependent drop-down list, you can use the search for the desired price using the VLOOKUP function.
XLOOKUP :)
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,
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.
Thank you for the reply!
Hi there,
Thank you for making this it is exactly what I am looking for. I am attempting to follow "How to create multiple dependent drop down list in Excel". When I get to point 2 "=UNIQUE(FILTER(B3:B15, A3:A15=E3))" I get the error code "empty array and #CALC!". Any ideas on how to fix this please?
Hello!
I can't see your data, but I'm assuming the FILTER function is returning an empty array. That is, there is no value that matches the filter criteria. Try changing your formula to something like this:
=UNIQUE(FILTER(B3:B15, A3:A15=E3,"No results"))
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi Alexander,
Thank you so much for your reply! This has fixed the issue. I really appreciate the help! I have spent weeks trying to sort this!
Thanks,
Lisa
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?
Hello!
To list only products from the selected vendor, use the dependent dropdown list as described in this article above.
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.
Hi!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question.