Create a dynamic dependent drop down list in Excel an easy way
Mar 31, 2025 pm 03:11 PMThis 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
- Select the cell for the dropdown (D3).
- Go to the Data tab, click Data Validation.
- Select List under Allow.
- In Source, enter
=$G$3#
(spill range reference). - 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!

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)
