国产av日韩一区二区三区精品,成人性爱视频在线观看,国产,欧美,日韩,一区,www.成色av久久成人,2222eeee成人天堂

Home Topics excel Create a dynamic dependent drop down list in Excel an easy way

Create a dynamic dependent drop down list in Excel an easy way

Mar 31, 2025 pm 03:11 PM

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.

Create a dynamic dependent drop down list in Excel an easy way

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.

Create a dynamic dependent drop down list in Excel an easy way

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.

Create a dynamic dependent drop down list in Excel an easy way

Your primary dropdown is complete.

Create a dynamic dependent drop down list in Excel an easy way

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).

Create a dynamic dependent drop down list in Excel an easy way

Step 4: Create the Dependent Dropdown

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

Create a dynamic dependent drop down list in Excel an easy way

Your dependent dropdown is ready.

Create a dynamic dependent drop down list in Excel an easy way

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.

Create a dynamic dependent drop down list in Excel an easy way

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undress AI Tool

Undress AI Tool

Undress images for free

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)