Learn how to create dependent drop-down lists (also known as cascading validation) in Excel. This technique does NOT require named ranges.

Bottom line: Learn how to create cascading or dependent drop-down lists (also known as cascading validation) in Excel.  This technique does NOT require named ranges.  If you don't mind using named ranges then there are a few links at the bottom of the page with solutions that will be easier to implement.

Skill level: Intermediate

Functions used: OFFSET, MATCH, COUNTA, COUNTIF, INDIRECT

Dependent Drop-down Lists Example in Excel

Drop-Down (Data Validation) Lists

Before we dive into dependent lists, if you are unfamiliar with drop-down lists in general (also known as data-validation lists), I strongly suggest you check out my tutorial for creating drop-down lists and also this post for making drop-down lists dynamic.

What is a Dependent Drop-down List?

Leah asked the following question, “How do I create a drop-down list where the list of choices changes when I select an item in another list?”

You can see an example of this in the screencast below.  When I select “Coffee” from the validation list in cell B4, the list in cell E4 displays different types of Coffee.

Dependent Drop-down Lists with Excel Data Validation GIF

Click here to view the screencast animation in your browser.

When I select “Wine” from the list in cell B4, the list in cell E4 displays different types of Wine.

These are called dependent or cascading drop-down lists because the 2nd list depends on the choice made in the first list.  This technique requires the use of the OFFSET function to create the dependencies, and it’s a great function to learn.

Step-by-Step Guide to Dependent Drop-down Lists

The rest of this article will explain how to create these dependent lists in your own workbook.  You can also download the example file to follow along, or just copy the sheets into your workbook.

Dependent Dropdown Lists.xlsx (36.6 KB)

Dependent Dropdown Lists - 3 Lists.xlsx (37.6 KB)

Dependent Dropdown Lists Multiple Dependents.xlsx (37.3 KB)

Step 1: Prepare the Source Tables

Our first step is to create the source tables that we will use for the contents of the drop-down lists.

Source Data for Each Dependent Drop-down List Excel

In the image above, the ‘Lists’ sheet contains the lists for each drop-down.  The list in column B contains the Category items for the parent list.  The parent list is the list where we will make the first choice.

We then use the list in columns D:F on the ‘Lists’ sheet to populate the child list with items.  The child list is the second list of choices that will be dependent on the selection in the parent list.

The first column in this table (column D) contains the Categories.  These are the exact same names that are in column B.  The second column in the table (column E) contains the different types for each category.  This is where the relationship between the Category and Type are created.

It’s also important to note that the child list needs to be sorted for this technique to work.  In the image below you can see that I sorted column D so that all the Category items are grouped together in the list.

Setup Source Data Lists for Dependent Drop-downs Excel

How to Create the Lists

These lists originated from a much larger data table that contains transactional sales data.  To create the lists I needed to extract all the unique values for each list.

Original Data Containing List Items in Excel

Fortunately, Excel has some great built-in tools that allow you to remove the duplicates.

One way is to use the Remove Duplicates tool that is located on the Data tab in the ribbon.

Remove Duplicates Tool on the Data Tab Excel

You could also create a pivot table to quickly list the unique values in the Rows area of the pivot.  Then copy/paste the results to the ‘Lists’ sheet that contains the source tables.

Pivot Table to Create List of Unique Items (Remove Duplicates)

I won’t go into details on these techniques, but they will make it very fast to create and update you dependent lists.  If your lists change frequently with new items, then I would recommend the pivot table method because you can simply refresh the pivot table to generate your new list.

Step 2: Create the Parent Drop-down List

Ok, now that we have the source data lists setup we can create the drop-down lists.  We will do this with in-cell data validation lists.

Parent Dynamic Drop Down List Data Validation Excel

Dependent drop-down lists are not a built-in feature of Excel.  Therefore, we need to get creative with some functions and formulas to create the dynamic dependencies between the lists.

With cell B6 selected on the ‘Dropdowns’ sheet, click the Data Validation button on the Data tab of the ribbon.

Data Validation Button on Data Tab of Excel Ribbon

The Data Validation window will appear.

  1. First, choose “List” in the Allow drop-down list.
  2. Then enter the OFFSET formula in the Source box (see explanation below).
  3. Press OK.

Setup Data Validation for the Parent List in Excel

We could put the following reference in as the Source: =Lists!B2:B4

However, we want the source list to be dynamic.  Dynamic means that the list will automatically expand as we add more items to it.  We don’t want to have to change the source reference every time we add items to the list.

The OFFSET function helps create this dynamic range reference, and returns a reference to a range of cells based on the coordinates you specify.  It has the following arguments.

=OFFSET(reference, rows, columns, [height], [width])

Our Formula: =OFFSET(Lists!B1,1,0,COUNTA(Lists!B:B)-1,1)

The Dynamic Range Formula Explained using OFFSET

Here is a quick explanation of each argument in the formula:

  • Reference – Think of this as the cell that is the starting point for the range.  In this case we are starting in cell B1 on the ‘Lists’ sheet.
  • Rows – We actually want to move the starting point down to cell B2.  The rows argument allows us to specify how many rows down we should move (offset) the starting point. We put a “1” for this argument to move the starting point down to cell B2.
  • Columns – This argument would move the starting point to the right a specific number of columns.  In this case we want to stay in column B, so we leave this argument blank or put 0 (zero).
  • Height – This is the height of the range specified in number of rows.  In this case we are using the COUNTA function to count the number of rows that contain text in column B. =COUNTA(Lists!B:B).  This will return 4 since there are 4 rows with text in column B.  We subtract 1 because we don’t want to include the header row in our list.
  • Width – This is the width of the range specified by number of columns.  Our range is only 1 column wide (column B) so we put a 1 here.

You should now have a drop-down list in cell B6 that contains all the Category items in the list on the Lists tab.

Excel Tables and the Structured Reference Alternative

If you are storing your lists in Excel Tables (which I highly recommend), then you could use the following formula instead.

=INDIRECT(“tblCategory[Category]”)

Since Table references expand and collapse automatically when rows are added/deleted, you do not need to use the OFFSET function.

Step 3: Create the Child (Dependent) Drop-down List

We now need to create the child list, which is dependent on the selection of the parent list.

Child Dynamic Drop Down List Data Validation Excel

The OFFSET function can be used for this list as well.  We just need to make a few adjustments to make it dependent on the selection in the parent list.

Here is the formula for the child list:

=OFFSET(Lists!$D$1,MATCH(B6,Lists!$D:$D,0)-1,1,COUNTIF(Lists!$D:$D,B6),1)

Dynamic Range Formula for Dependent Child List

Here is an explanation of each argument in the OFFSET formula:

  • Reference – Start in cell D1 on the Lists tab.  This is the top left cell of the table that lists the child items.
  • Rows – Use the MATCH function to find the first row that matches the item selected in the parent list.  Cell B6 is the reference to the cell that contains the parent drop-down list.  This is basically a lookup function that returns the row number of the first matching item.  Subtract one to account for the header row.
  • Columns – Specify a “1” to move one column to the right.  The formula is going to find the matching range in column D, then we have to move over one column to the right to return the range of matching values in column E.
  • Height – Use the COUNTIF function to count the number of occurrences of the parent item in Cell B6, “Coffee”.  This returns a 4, which means the range will be 4 rows high.
  • Width – The range is one column wide (column E).

The child drop-down list in cell E6 should now be dependent on the selection made in cell B6 on the ‘Dropdowns’ sheet (parent list).

Excel Tables and the Structured Reference Alternative

As an alternative, you could use the structured reference formulas with the Excel Table names instead of cell addresses.  The Table formula would look like the following.

=OFFSET(INDIRECT(“tblType[#Headers]”), MATCH(B11,INDIRECT(“tblType[Category]”),0), 1, COUNTIF(INDIRECT(“tblType[Category]”),B11),1)

It’s not as pretty because you have to add the INDIRECT function for each table reference.

Dependent Drop-downs WITHOUT Named Ranges

There are a few different ways to create dependent drop down lists in Excel.  The main advantage of this method is that it does NOT required the use of named ranges.

Named ranges are a great feature, but can be very confusing for some users.  Therefore, I recommend not using named ranges unless you are confident that all future users of your model will understand them.

If you don’t mind using named ranges then the following articles provide some awesome tutorials on other ways to create the drop-downs.

Download the Sample Workbook

You can download the sample workbook below to follow along, or simply copy the sheets into your own workbook.

Dependent Dropdown Lists.xlsx (36.6 KB)

Dependent Dropdown Lists - 3 Lists.xlsx (37.6 KB)

Dependent Dropdown Lists Multiple Dependents.xlsx (37.3 KB)

Dependent Dropdown Lists - Multiple Precedents.xlsx (35.3 KB)

How to Search a Drop-down List

Excel doesn't have a built-in option to search drop-down lists for a particular item, but I've created an add-in that gives you that option.  It's called List Search and you can access that add-in here:

List Search Add-in Free Download 640x360

Click here to download the List Search Add-in

I hope this post is useful for you! Please leave a comment below with any questions or suggestions.

97 comments

Your email address will not be published. Required fields are marked *

  • Thank you for providing good add in for faster work, I hope you make another add in like List Search which depends two are more columns data

  • I love using the List Search! I would like to be able to click on a name and populate in another column if they are a VAN rider. I am not sure how to do that. Can you help? Thank you for all the work you are doing to make Excel user friendly.

    Sister Mary Jane McDonnell

  • Hi,

    How would this be adapted if the third drop down were dependent on the combination of the first two drop downs? IE, if size were dependent on CoffeeDecaf or CoffeeRegular, etc.

  • Hi,

    I use this formula =OFFSET(Lists!$D$1,MATCH(B6,Lists!$D:$D,0)-1,1,COUNTIF(Lists!$D:$D,B6),1) but I have a lot of duplicate names… Please help?

  • Hi, Jon. Very helpful article. I am creating a form with multiple questions. Each would use a separate dependent dropdown like this. I created the first question and all worked fine. I created the second question, but when I select my category, I get the Q1 dropdown, not the Q2.

    Is is possible to have multiple cascading dropdowns on the same worksheet?

  • Hi ,

    Thanks a lot as the dependent data validation solution you provided is extremely wonderful and it helped me a lot in designing one of my excel sheet .

    Is there any way around i can send my queries/concerns directly because the profession i am in i need solution in a very short span of time otherwise it is useless to me.

    Once again thanks a lot for all your valuable support.

    Regards ,

    Sonu Tyagi

  • Great article thank you. is it possible to extract data dependant on the drop down selection and summarize it in a new table bellow?

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly