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). Finally, TRANSPOSE changes the output orientation from vertical to horizontal. names will appear in the same order as in your source table). 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.
Here, the UNIQUE function extracts all the different departments from E3:E15. To get the departments, enter this formula in H2. To automate the work and prevent human errors, we will be using the following formulas. name there will be a list of employees working in that specific department. 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. So, our first step is to structure the original data according to our needs.
The source data for drop-down lists often come from different sources and are organized differently.
If you change the selection in the main list, the names in the dependent drop-down will update accordingly. in the first list only displays Managers for that selected department in the second list. In our main table on the left, we want to have two picklists in each row, so that selecting a Dept. Source dataįor starters, let's get some source data to work on. In pre-dynamic versions of Excel, please use the traditional approach to creating multiple dependent drop down lists. Important note! Because this solution relies on the dynamic array feature, it is only applicable in Excel for Microsoft 365 and Excel 2021. Make an expandable multiple drop-down list.Result: dependent drop down in multiple rows.Copy drop down lists across multiple rows.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! Indeed, Excel picklists are most often used in worksheets with hundreds of rows to facilitate data entry.
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. 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. Among many other things, they significantly simplify the creation of dynamic drop down lists. The introduction of dynamic arrays has made our lives as Excel users a lot easier.
If you are looking to create an expandable multiple drop-down list that can be dragged or copied across multiple rows, you've landed on the right page and will have the solution in minutes.