This tutorial demonstrates how to create dynamic, cascading dropdown lists in Excel 365 and Excel 2021 using new dynamic array functions. Traditional methods are complex and limited, but dynamic arrays offer a streamlined solution.

Creating a simple dropdown is easy, but multi-level cascading dropdowns have historically been challenging. This tutorial provides a fast, straightforward approach, eliminating the complexities of older methods.

Key Features:

• Dynamic dropdowns in Excel
• Multiple dependent dropdowns
• Expandable dropdowns (excluding blank cells)
• Alphabetical sorting of dropdown items

Note: This method requires Excel 365 or Excel 2021. Older versions require more complex techniques. This solution is for single rows; for multiple rows, see the linked instructions.

Creating a Dynamic Dependent Dropdown List:

This example uses a list of fruits (Column A) and their exporters (Column B). The goal is to create a dropdown for unique fruits and a second dropdown displaying the relevant exporters based on the fruit selection.

Step 1: Main Dropdown List Items

Extract unique fruit names from Column A using the UNIQUE function:

=UNIQUE(A3:A15)

This formula (in G3) automatically spills the results.

Step 2: Create the Main Dropdown

1. Select the cell for the dropdown (D3).
2. Go to the Data tab, click Data Validation.
3. Select List under Allow.
4. In Source, enter =$G$3# (spill range reference).
5. Click OK.

Your primary dropdown is complete.

Step 3: Dependent Dropdown List Items

Use the FILTER function to get exporters based on the fruit selection:

=FILTER(B3:B15, A3:A15=D3)

(B3:B15 is exporter data, A3:A15 is fruit data, D3 is the main dropdown cell).

Step 4: Create the Dependent Dropdown

Create data validation for the second dropdown (similar to Step 2), using =$H$3# as the Source.

Your dependent dropdown is ready.

Tips:

• Format source data as an Excel table for automatic updates.
• Add extra cells to formulas to accommodate new entries.
• Use FILTER to exclude blank cells.
• Use SORT to alphabetize dropdown items (see below).

Creating Multiple Dependent Dropdowns:

Extend this process to create more levels. For example, add states/provinces (Column C) as a third dropdown.

Use UNIQUE and FILTER with multiple criteria to link the dropdowns. For the third dropdown, a formula like this might be used:

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

Expandable and Sorted Dropdowns:

To make dropdowns automatically include new entries, add extra cells to your formulas. To sort alphabetically, wrap your formulas in the SORT function (ascending: =SORT(...), descending: =SORT(...,1,-1)).

This dynamic array approach simplifies creating complex dropdown lists in Excel, offering a significant improvement over older methods.

The above is the detailed content of Create a dynamic dependent drop down list in Excel an easy way. For more information, please follow other related articles on the PHP Chinese website!