How to Create Dependent Drop Down List in Excel

Most of us are familiar with drop-down lists in Excel. They make data entry error free and consistent. However, in some cases we require a second drop-down list which depends on the drop down value selected in the first list. This is called creating dependent drop-down list in Excel. 

📌 What is a Dependent Drop Down List in Excel?

As explained above, a dependent drop-down list in Excel is a dynamic list that changes based on the selection made in another list.

Example:

For e.g., imagine a cafe that sells cookies and drinks. Under cookie we have items like chocolate chip, oatmeal raisin etc and under drinks we have 4-5 options. Here is the menu –

Now we want that if someone selects cookie in the first drop-down list, they should only get chocolate chip, oatmeal raisin etc in the second list. They should not see drink items. Similarly, when someone selects drinks, they should only see plain milk, chocolate milk etc in the second list. If they select coffee in the second list, they should only see regular, decaf and iced options in the third list.

This is when we need to create dependent drop-down list in Excel. In this guide, we will demonstrate how to do it using named ranges and INDIRECT formula.

🧱 Step-by-Step Guide to Create Dependent Drop Down List in Excel

✅ Step 1: Prepare Your Source Data

We will use the same cafe example discussed above.First we have to organize the data into a clean structure like this –

Please ensure that spelling matches and use underscore instead of spaces, for e.g., chocolate_milk instead of chocolate milk.

✅ Step 2: Assign Names to All 5 Lists

1. Select the first range – Item.
2. Go to Formulas > Name Manager > Create from Selection
3. Check ‘Top Row’ cell and hit ok
4. Do it for the other 4 ranges too – cookie, drink, coffee, fruit_smoothie
5. We can cross check the assigned names by clicking on Name Manager

✅ Step 3: Create the Primary Drop Down (Main Item)

1. Select the cell where you want the first drop-down (e.g., A1).

2. Then go to Data → Data Validation.

3. In the Data Validation dialog box:

   • Allow: List

   • Source: ‘=item’. Excel will know you are referring to the named range called item.

4. Click OK.

Our first drop down is created, cross check it. You should see cookie and drink options while clicking on it.

✅ Step 4: Create the Dependent Drop Down

1. First select the cell for the second drop-down (e.g., B1).

2. Then go to Data → Data Validation.

3. Now choose List and enter this formula in the source:

   =INDIRECT(A1)

4. Click OK.

Now, when you select “cookie” in cell A1, B1 will only show cookie options like chocolate chip, oatmeal raisin etc. If you choose “Drink”, it’ll show drink options like plain milk, chocolate milk etc.

We can repeat this process for creating further dependent lists like options under coffee, options under fruit smoothie.

💡 Why Named Ranges?

We used INDIRECT() function which can locate named ranges. That’s why we need to name the ranges first else the formula will not work.

🔁 Optional: Make It More Dynamic Using Tables

We can convert the menu data into dynamic Excel tables. What it will do is that when we add new items in menu, the list will update automatically. Here are the steps –

1. Select each list of food items.

2. Then press Ctrl + T to convert to Table.

3. Now we can create dynamic named ranges using =INDEX() and MATCH() or helper formulas.

🧪 Practice Exercise on Dependent Drop Down List in Excel

Let’s try to solve this to see if we understood the concept –

Scenario:

We need to create a dependent drop down list in Excel for the following data –

• Department: Sales, HR, IT

• Roles (based on department):

  • Sales → Executive, Manager

  • HR → Recruiter, Trainer

  • IT → Developer, Support

Task:

1. Create a drop-down for Department in cell D1.

2. Based on the selected Department, show related Roles in E1.

Drop your queries in comments if you get stuck.

❓Common Issues & Troubleshooting

✅ Final Thoughts on Dependent Drop Down List in Excel

Dependent drop-down list in Excel is certainly an excellent method for enforcing data consistency and streamlining data entry. You can greatly improve your Excel skills with a few simple steps and functions like INDIRECT.

Do you have any questions or would like more tutorials like this one? Leave a comment or look at other codeandexcel.com guides.

Also check out our other articles on –

Excel Power Pivot for Beginners: Create Data Models Easily

Advanced Filter vs AutoFilter in Excel: What’s the Difference?

Working with Structured Tables in Excel: A Complete Guide

Google Sheet’s QnA on Dependent Drop Down Lists