How to Create a Dynamic Dependent Drop-Down List in Excel (2 Methods)

What Is Dynamic Dependent Drop-Down List?

A drop-down list is a set of predefined values from which you can choose a specific value for a cell. A dynamic dependent drop-down list consists of two parts:

  1. Primary Drop-Down List (1st List): This is the initial drop-down list where you select a category or type. For example, you might choose “Poetry” or “Science Fiction.”
  2. Secondary Drop-Down List (2nd List): Based on your selection from the primary drop-down list, the secondary drop-down list automatically updates to show relevant options. For instance, if you choose “Poetry,” the second list will display book names related to poetry.

dynamic dependent drop down list

 


Method 1 – Using Formulas to Create Dynamic Dependent Drop-Down List:

We’ll assume you have a dataset containing two columns: Book Type and Book Name.

Use Formulas to Creat Dynamic Dependent Drop Down List


STEP 1: Creating the Primary Drop-Down List

  • In Cell E4, enter the following formula to create the list of unique book types:
=UNIQUE(OFFSET(B5,0,0,COUNTA(B:B)-2,1))

Press Enter, and an array of book types will be generated.

Create Primary Drop Down List

  • Select the cell where you want to create the primary drop-down list (e.g., Cell E12).
  • Go to the Data tab and choose Data Validation.

  • In the Data Validation window:
    • Select List in the Allow section.
    • In the Source box, type $E$4#.
  • Press OK. You’ll now see a drop-down list in Cell E12, allowing you to select different book types.

  • We will see a drop-down list in Cell E12. We can select different Book Types from there.


STEP 2: Inserting Second Drop-Down List

  • In Cell F4, enter the following formula to create the list of Book Names based on the selected book type:
=FILTER(C:C,B:B=E12)
  • Press Enter, and the Book Names related to the chosen book type will appear.

Insert Second Drop Down List

  • Select Cell F12 and go to the Data tab.
  • Choose Data Validation.

  • In the Data Validation window:
    • Select List in the Allow section.
    • In the Source box, type $F$4#.
  • Press OK.

  • Now you have a dynamic dependent drop-down list.

  • If you change the selection in the first drop-down list, the content of the second drop-down list will automatically update to match the chosen book type.

dynamic dependent drop down list result

Read More: How to Create Dependent Drop-Down List with Multiple Words in Excel


Method 2 – Using Excel Table to Create Dynamic Dependent Drop-Down List:

In this method, we’ll create a dynamic dependent drop-down list using an Excel table. For demonstration purposes, I’ve used different book names categorized under columns such as Novel, Autobiography, Science Fiction, and Poetry.

Utlize Table to Create Dynamic Dependent Drop Down List

 


STEP 1: Creating the Primary Drop-Down List

  • Create the Table:
    • Select the entire dataset (including the book names and categories).
    • Press Ctrl + L to create an Excel table.
    • In the Create Table window, press OK.

  • Generate the Primary List:
    • Write the following formula in Cell G4:
=TRANSPOSE(Table3[#Headers])

Replace Table3 with the actual name of your table if it’s different.

    • Instantly, the primary list (book categories) will be created in Cell G4.

Make Primary Drop Down List

  • Data Validation for Primary List:
    • Select Cell G10.
    • Go to the Data tab.
    • Choose Data Validation.

  • In the Data Validation window:
    • Select List in the Allow section.
    • In the Source section, type $G$4#.
  • Press OK.

  • Now you can select different book types from the primary drop-down list.


STEP 2: Generating the Second Drop-Down List

  • Create the Dynamic-Dependent List:
    • Write the following formula in Cell I4:
=INDIRECT("Table3["&G10&"]")

Press Enter, and the second list (book names related to the selected category) will appear in Cell I4.

  • Data Validation for Second List:
    • Select Cell G13.
    • Go to the Data tab.
    • Choose Data Validation.

    • In the Data Validation window:
      • Select List in the Allow section.
      • In the Source section, select Cell $I$4 with a # sign at the end.
    • Press OK.

  • You will see the drop-down list in Cell G13.

  • Now you have a dynamic dependent drop-down list. If you change the selection in the first drop-down list, the content of the second drop-down list will automatically update to match the chosen book category.

dynamic dependent drop down list resultRead More: Create Excel Drop Down List from Table


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Excel Dependent Drop Down ListExcel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

6 Comments
  1. Hi, I’m curious if you have to leave the full list of items used for the drop down menus visible? I’m trying to use this feature on an order form to eliminate typos and errors. Is it possible to create an order form with the drop down menu and not have the information visible at all times? If not, what would be the best way to accomplish this?

  2. Hi, is there a way to make the results of these lists editable?

    not the list itself but the results, e.g, if i click novel and then it populates with A Tale of Two Cities, can I then edit the column with the book title in it?

    So that every time it filters, we can have most of the same info, but a few things need to change, such as sizes etc, as we have parts but they aren’t standard, they all vary, and there are hundreds, too many to make drop down list out of. can this be done?

    Thanks

    • Hello Missy, If you change the list it will change the results, both are the same. Your question “can I then edit the column with the book title in it?” is not clear to us. It will be better for us, if you create a sample Excel file of what you want and share it with us. Thanks.

  3. Hi

    how do i add a sample workbook for you to see?

    Thanks

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo