Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

How to Create a Dependent Drop Down List in Excel

A drop down list is an awesome tool. You can pre-define some values and user can enter data by selecting one of those values.

One of its advanced features is a “Dependent Drop Down List”.

A dependent drop down is all about showing values in a drop down list according to the selection of the value in another drop down.

Today, in this post, I’d like to share with you a Simple 7-Steps Process to create this drop down.

But first of all, let me tell you why it is important.

In the below example, you have two drop-down lists. Size drop down is dependent on product drop down.

two level dependent drop down list

If you select the white paper in product cell then in size drop down will show small and medium.

But, if you select gray paper then its size will be medium and large.

So here the basic idea to create a dependent drop down list is to get correct size as per product name.

So let’s get started.

Steps to Create a Dependent Drop Down List in Excel

For creating a dependent drop down list we need to use named ranges and indirect function. And, please download this sample file from here to follow along.

  • First of all, you have to create named ranges for drop down lists. For this, select product list. Go to -> Formulas -> Defined Names -> Create from selection.
how to create a dependent drop down list in excel create a named range
  • You’ll get a pop-up. Tick mark “Top Row” & click OK.
how to create a dependent drop down list in excel create a named range click ok
  • By using same steps, create two more named ranges for sizes. One is for white paper and second for the gray paper.
how to create a dependent drop down list in excel three named range

Quick Tip: By using this method to create a named range, the value in the first cell will be considered as the name and rest of the values as the range. You can also use dynamic named range for this.

  • Now select the cell where you want to add product drop down and Go to -> Data -> Data Tools -> Data Validation.
how to create a dependent drop down list in excel create drop downlist for products
  • In data validation window, select “List” and in “Source” enter below formula and then click OK.

=Indirect(“Product”)

how to create a dependent drop down list in excel drop down window
  • Select the cell where you want to add size drop down list. Go to -> Data -> Data Tools -> Data Validation.
  • In data validation window, select “List” and in “Source” enter below formula and click OK.

=Indirect(“A5”)

how to create a dependent drop down list in excel drop down window

Finally, your dependent drop down list is ready. You can download this sample file from here to try it yourself.

two level dependent drop down list

How does it work

First, you have created three named ranges. Then we have used one named range to create product drop down.

After that, for the second drop down list, you have used indirect function & refer to the value in product cell.

If you notice, our size named ranges has name equals to the values we have in product drop down.

When we select “WhitePaperSheet” in product cell, then in size cell indirect function refer to the named range “WhitePaperSheet” and when you select “GreyPaperSheet” it will refer to named range “GreyPaperSheet”.

Related ➤ Dynamic Drop Down List in Excel

Three Level Dependent Drop Down List

In above example, you have created a two level dependent drop down list. But sometimes, we need to create a list with three level dependency.

For this, all you have to do, create a third drop down list which is dependent on the selection of the second drop down list.

Let’s say if we want to add a drop down list with “Length x Width” of the sizes for paper sheets.

how to create a dependent drop down list in excel with three level data

And, for this, you have to create a third drop down list which will show the “Length X Width” as per size selection.

Here are the steps:

  • Create three more named ranges using the same method which we have used above.
how to create a dependent drop down list in excel with three level data new named ranges
  • Select the cell where you want to insert your third drop down.
  • Open drop down options & insert the following formula in the source.

=Indirect(“C5”)

how to create a dependent drop down list in excel with three level add drop down list
  • Click OK.

Now, your three level drop down list is ready

three level dependent  drop down list in excel ready

You can download this sample file from here to learn more.

Conclusion

A dependent drop down list is really a useful tool. And, It’s easy to create it. I still remember the first time when I used it in an invoice template.

As @Wadie Seruge asked, you can create n number of dependent drop down lists. But, it will become complex with increasing numbers.

I hope this tip with help you get better at Excel.

😃

Now tell me one thing. Have you used a dependent drop down list? Please share your views with me in the comment section, I would love to hear from you.

And, please don’t forget to share this tip with your friends.

  • vishesh

    can you share the file with example….Please….

    • Puneet Gogia

      It’s there. Check out the link.

      • vishesh

        Thanks.Puneet………..i got it.

        • Puneet Gogia

          You’re Welcome.

  • Wadie Seruge

    How many columns of dependable drop down menu, can a person create ? Can it be like 5, 10, 20, 100, and so on rows?

    • Puneet Gogia

      Yes you can. It’s may be bit complex but not really hard.

      Tell me if you need any further help.

  • The Bishop

    My size dropdown only shows the product name I have selected in the first dropdown. The example file gives me an error (‘The source currently evaluates to an error’) when I try and work with that, despite using your cell references.

    • Puneet Gogia

      Please share a snapshot of cell reference you have in second drop down.