Skip to main content

Make a Dynamic Dependent Drop-Down List in Excel (Step by Step)

Author: Sun Last Modified: 2023-08-15

Here in this tutorial, we will step by step introduce how to create a dependent drop-down list that displays choices depending on the values selected in the first dropdown list. In other words, we will make an Excel data validation list based on the value of another list.

Make a dynamic dependent drop-down list
10s to make a dependent drop-down list with a handy tool
Make a dynamic dependent drop-down list in Excel 2021 or Excel 365
Some questions you may ask about this tutorial

doc dependent drop down list 1 1 1

Free download the sample file doc sample


Video: Make an Excel dependent drop-down list

 


Make a dynamic dependent drop-down list

 

Step 1: Type The Entries For The Drop-Down Lists

1. Firstly, type the entries you want to appear in the drop-down lists, each list in a column separately.

Notice that the items in the first column (Product) will be as Excel names for the dependent lists later. For example, here Fruit and Vegetable will be the Names for column B2:B5 and C2:C6 separately.

See screenshot:

doc dependent drop down list 1 2

2. Then create tables for each data list.

Select column range A1:A3, click Insert > Table, then in the Create Table dialog, tick My table has headers checkbox. Click OK.

doc dependent drop down list 1 3

Then repeat this step to create tables for the other two lists.

You can view all tables and the reference to ranges in Name Manager (press Ctrl + F3 to open it).

doc dependent drop down list 1 4

Step 2: Create Range Names

In this step, you need to create Names for the main list and each dependent list.

1. Select the items that appear in the main list (A2:A3).

2. Then go to the Name box which beside Formula bar.

3. Type the name in to it, here names it as Product.

4. Press Enter key to complete.

doc dependent drop down list 1 5

Then repeat the above steps to separately create Names for each dependent lists.

Here names the second column (B2:B5) as Fruit, and the third column (C2:C6) as Vegetable.

doc dependent drop down list 1 15

doc dependent drop down list 1 6

You can view all range names in Name Manager (press Ctrl + F3 to open it).

doc dependent drop down list 1 7

Step 3: Add The Main Drop Down List

Next, add the main drop down list (Product), which is a normal data validation drop-down list, not a dependent drop down list.

1. Firstly, create a table.

Select a cell (E1), and type the first column header (Product), and move to the next column cell (F1), type the second column header (Item). You will add the drop-down list to this table.

Then select these two headers (E1 and F1), click Insert tab, and select Table in the Tables group.

In the Create Table dialog, tick My table has headers box, and click OK.

doc dependent drop down list 1 8

2. Select cell E2 to which you want to insert the main drop down list, click Data tab and go to Data Tools group to click Data Validation > Data Validation.

doc dependent drop down list 1 9

3. In the Data Validation dialog,

  • Choose List in the Allow section,
  • Type below formula into Source bar, Product is Name of the main list,
  • Click OK.
=Product

doc dependent drop down list 1 10

You can see the main drop-down list has been created.

doc dependent drop down list 1 11

Step 4: Add Dependent Drop Down List

1. Select cell F2 to which you want to add the dependent drop-down list, click Data tab, and go to Data Tools group to click Data Validation > Data Validation.

2. In the Data Validation dialog,

  • Choose List in the Allow section,
  • Type the below formula into Source bar, E2 is the cell that contains the main drop-down list.
  • Click OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))

doc dependent drop down list 1 12

If the E2 is empty (you do not select any one item in the main drop-down list), you will see a message pops out as below, click Yes to continue.

doc dependent drop down list 1 13

Now the dependent drop-down list has been made.

doc dependent drop down list 1 14

Step 5: Test The Dependent Drop-Down List.

1. Select Fruit in the main drop-down list (E2), then go to the dependent drop-down list (F2) to click the arrow icon, see if the fruit items are in the list, then select one item from the dependent drop-down list.

2. Press Tab key to start a new row in the data entry table, select Vegetable, and move to the next cell to the right, see if the vegetable items are in the list, then select one item from the dependent drop-down list.

gif 1

Notes:

10s to make a dependent drop-down list with a handy tool

 

Kutools for Excel provides a powerful tool to make a dependent drop-down list easier and faster, let’s see:

kte gif 1

Before following below steps, please click to download Kutools for Excel for 30-day free trial firstly.

Step 1: Type The Entries For The Drop Down List

Firstly, arrange your data as below screenshot shown:

doc kutools dynamic drop down list 1

Step 2: Applying Kutools tool

1. Select the data you have created, click Kutools tab, and click Drop-down list to display the sub menu, click Dynamic Drop-down List.

doc kutools dynamic drop down list 2

2. In the Dependent Drop-down List

  • Check the Mode B that matches your data mode,
  • Select the output range, the output range column must be equal to the data range column,
  • Click Ok.

doc kutools dynamic drop down list 3

Now the dependent drop down list has been created.

doc kutools dynamic drop down list 4

Tips:
  • Mode B supports to create a third or more levels drop down list:
    doc kutools dynamic drop down list 5 1
  • If your data is arranged as the below screenshot shows, you need to use Mode A, Mode A only supports to create a 2-level dependent drop down list.
    doc kutools dynamic drop down list 6
  • More details about how to use Kutools to create a dependent drop down list,  please visit this tutorial .

Kutools for Excel

Full feature free trial 30-day, no credit card required.

More than 300 powerful advanced features and functions for Excel.

Don't need any special skills, saving hours of time every day.

Make a dynamic dependent drop-down list in Excel 2021 or Excel 365

 

If you are in Excel 2021 or Excel 365, there is another way can quickly create a dynamic dependent drop down list by using new functions UNIQUE and FILTER.

Supposing your source data is arranged as screenshot shown, please follow below steps to create the dynamic drop down list.

Step 1: Using formula to get items for the main drop-down list

Select a cell, for example, cell G3, and using UNIQUE and FILTER functions to extract the unique values from the Product list which will be the source of the main drop-down list, and press Enter key.

=UNIQUE(FILTER(A3:A20, A3:A20<>""))
Note: With the products are in A3:A12, we add 8 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.

Step 2: Create the main drop-down list

1. Select a cell that you want to place the main drop-down list, for example, cell D3, click Data tab, and go to Data Tools group to click Data Validation > Data Validation.

2. In the Data Validation dialog,

  • Choose List in the Allow section,
  • Type below formula into Source bar,
  • Click OK.
=$G$3#
Note: This is called a spill range reference, and this syntax refers to the entire range regardless of how much it expands or contracts.

Now the main drop-down list is created.

Step 3: Using formula to get items for the dependent drop-down list

Select a cell, for example, cell H3, using FILTER function to filter the items based on the value in cell D3 (the selected item in main drop down list), press Enter key.

=FILTER(B3:B20, A3:A20=D3)
Note: If there is blank in the main drop-down list, the formula will return to zeros.

Step 4: Create the dependent drop-down list

1. Select a cell which will place the dependent drop-down list, for example, cell E3, click Data tab, and go to Data Tools group to click Data Validation > Data Validation.

2. In the Data Validation dialog,

  • Choose List in the Allow section,
  • Type below formula into Source bar,
  • Click OK.
=$H$3#
Note: This is called a spill range reference, and this syntax refers to the entire range regardless of how much it expands or contracts.

Now the dependent drop-down list is created successfully.

When you add new items or do some changes in A3:A20, the drop-down list will be updated automatically.

Tips:

Sort drop down list alphabetically

If you want to arranged the items in the drop-down list alphabetically, you can use below formula to the preparation table.

For the main dropdown (the formula in cell G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

For the dependent dropdown (the formula in cell H3):

=SORT(FILTER(B3:B20, A3:A20=D3))

Now both of drop-down lists get sorted alphabetically A to Z.

doc dependent drop down 365 8

For get sorted alphabetically Z to A, please use below formula:

For the main dropdown (the formula in cell G3):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

For the dependent dropdown (the formula in cell H3):

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)

Some questions you may ask:

1. Why insert a table for each data list?

Inserting a table for the data list will help you auto update the drop down list based on the changes in the data list. For example, adding ‘Others’ in the first data list, then the main drop down list will be auto added with ‘Others’.

doc dependent drop down list update

2. Why use a table to place drop-down lists?

When you press Tab key to add new line to the table, the drop down lists will be auto added in the new line as well.

3. How INDIRECT function work?

INDIRECT function is used to convert a text string to a valid reference.

4. How formula INDIRECT(SUBSTITUTE(E2&F2," ","")) work?

Firstly, SUBSTITUTE function replaces text with another text. Here it used to remove the spaces from the combined names (E2 and F2). Then INDIRECT function converts the text string (the combined contents by E2 and F2) to a valid reference.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations