How to Create Dependent Drop-down Lists | Excel Campus
79

How to Create Dependent Drop-down Lists

Bottom line: Learn how to create 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

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 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

The 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)

Please leave a comment below with any questions or suggestions.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 79 comments
Bryan - June 21, 2017

This has been very helpful. Do you know of a way to set up a 3rd drop down list which is dependent purely on what happens in both the 1st drop down and the 2nd drop down?

I’m using the offset option specified in the tutorial but, I do no know of a way to make my list simultaneously dependent upon what happens in both columns.

Reply
mandy - May 27, 2017

HI, i tried to make another example like, region 1, region 2….region 4. and on every regions, i put different places. i applied the same formula on your example and it works, but the main problem is when im selecting region 3, it also get the places on region 4.

Reply
Manisha - March 6, 2017

Hello Jon,

Thank you for the solution that you have provided, I have implemented the same but in the child drop-down I am unable to see any values. Do you know what can be the possible reason for this?

Regards,
Manisha.

Reply
Ash - January 19, 2017

Do you have an example of the cascading drop down list (3 levels or so) using Pivot Tables?

Reply
Francis - January 1, 2017

Hi Jon, thanks for your articles they are very helpful, your videos are also amazingly easy to follow and understand!

1. Do you mind explaining the “#headers” in the formula example below that you showed for when your using the data table?
This is the only entry I do not understand.

=OFFSET(INDIRECT(“tblType[#Headers]”)

After reviewing the article again and your downloadable file, without named ranges is not quite as intimidating to use as I first thought especially when using the data table method.

2. Do you have any suggestions for me if I am trying to make a dependant drop down structure based on multiple selections

For example, a drop down in column 6 will display choices based upon the values you have chosen in drop downs located in columns 1 through 5. Is this possible? (more than 3- is there a limit to the amount of dependant choices that can be linked?)

Could I use your methods shown here to make something like this work? Would you recommend another link or another method entirely to accomplish this?

I did ask this question a few moments ago in other post but couldn’t find my way back to edit it for some reason so you can just ignore a response to that one, sorry for the duplicate.

Hope you can help in some way 🙂

Reply
    Jon Acampora - January 13, 2017

    Hey Francis,
    Sorry to not respond sooner. Here are my answers.

    1. The [#Headers] references the the header row of the Table. So this is telling the OFFSET function to start in the Header row (first row) of the Table. It will basically return the reference to cell D1, like the non-table version does.

    2. I added a file to the Downloads section above named Dependent Dropdown Lists – Multiple Precedents.xlsx. This file contains an example of how you can display choices in a drop-down based on multiple inputs or selections from other cells. This example file uses two inputs. For this to work you will see that I concatenated (joined) the lookup values in the Type table. Column D on the Lists sheet now contains the Category and Size values joined together in one cell. Then the return value for the drop-down list is in column E.

    This allows the user to choose the Category and Size from drop-downs, then see which types are available in the type drop-down. In this case regular and decaf are only available in the small and medium size coffees.

    The concatenated values in column D will get more complex with 5 input values. There will be a lot of combinations to list. If you already have these combinations in a list, then it should be pretty easy to setup. I hope that helps get you started. Thanks!

    Reply
      Francis - February 7, 2017

      Thanks for the reply Jon. Appreciate your help!

      Reply
Matteo - December 30, 2016

Dear Jon,

I am wondering how to create the dependant drop down list, if I have separate type tables (of two or three columns), one for each category.

Thank you for your time and your posting.

Matteo

Reply
Ian - November 17, 2016

Jon,

Thank you for posting. Having some issues. Can I send you my spreadsheet so that you can point out where I am going wrong?

Thanks,
Ian

Reply
    Jon Acampora - December 1, 2016

    Hi Ian,
    I’m sorry to hear that. Due to the overwhelming number of requests, I’m not able to look at individual files at this time. I’m sorry about that. You might want to try posting your question on a forum. Thanks!

    Reply
Alan - November 15, 2016

Jon a fantastic tutorial. Thanks!

Is there an option to display all the data in the child drop-down without it having to be filtered by the parent?

Thanks
Alan

Reply
    Jon Acampora - November 21, 2016

    Hey Alan,
    Great question! You can put an asterisk * in the parent drop-down to see the full list in the child drop-down. The asterisk is a wildcard character and the COUNTIF function will return the total number of cells in the child list, as long as those cells are not blank.

    I hope that helps. Thank you!

    Reply
Deona - October 24, 2016

Thanks for sharing this useful informative article. It enhances my knowledge.

Reply
Marek - October 17, 2016

Hi,
I would like to ask if something like following is possible:
at the begining i would like to have just one combo box with 3 options to choose from(tea, coffee, water). in case first option has been chosen from first combo box – tea then new second combo box would be created with 2 options – fruit, black.
in case i choose second option from first combo box – coffee , new second combo box gets created with 2 options – lattee, capucino.
in case i choose 3 option – water from first combo box, new text box will be created with label “enter water temperature”

many thanks

br,
Marek

Reply
Sarah - September 23, 2016

Hi
I have a perfectly function set of three cascading dropdowns using your guide, which…thankyou very much this is brilliant!
However I need in the same work book a second set of cascading dependant drop downs. These do not directly link to the first set, so should ‘locically’ be set up in the same way?
Apparently not, as I am completely unable to get them to work beyond my parentlist. I did think it may have been because I had my reference tables on the same work sheet so tried creating a new work sheet for all the parent and children lists for the second set, but still nothing beyond the first parent drop down list. I then wondered if it was due to the child lists containing multiple words, but then when I returned to my first set, I had multiple word statements in this too.
I have looked at other cascading options but they do not work for me in the same way my first set does.
Is it possible to have multiple dependant drop down sets in one work book? I effectivey wil need 5 sets with a total of 14 drop downs. Some drops downs link some do not.

Please help as I am unable to find anything related to this anywhere on the internet

Reply
Melissa Farr - September 9, 2016

Hi Jon, this might be a little too naive for me. But I was trying to work on this task by creating a pivot table to list unique values for the category and type (I am using the data sample you have provided). My problem is my CATEGORY and TYPE lists all go in one column (column A), unlike in your example here where the CATEGORY goes to column A and TYPE goes to column B. Yours is easier to read while mine lists Coffee, types of coffee, then tea, then types of tea, then wine, etc. all in column A. What am I missing? Thanks!

Reply
    Jon Acampora - September 12, 2016

    Hey Melissa,

    Great question! I probably should have explained that in the post. The default layout for the pivot table is the Compact Form. We just need to change it to Tabular Form to view the columns side-by-side. This option is located on the Design tab of the ribbon in the Report Layout drop-down. You need to select a cell inside the pivot table for the Design tab to appear in the ribbon. Here is a screenshot.

    Pivot Table Tabular Form for Side-by-side columns

    I hope that helps. Thanks!

    Reply
      Melissa - September 12, 2016

      That helps indeed. You are a blessing, Jon!

      Reply
Stoyan - August 15, 2016

Hi I have a drop down list in A2 in which I can change values, and a fixed value in D2. So here is the formula in C3, =IF(A2=”NO CHANGE”,D2)
But every time I change values in A2, the D2 cell gets wiped and blank.
So how can I keep my formula working when I change values in A2, without getting D2 going blank?

Reply
Melissa - August 10, 2016

Hi Jon. I have been required to do a monthly report about student services for several years, using Excel. But what I want is have a special file where:

1. I have worksheets for each month putting all the number of students who availed of some services (ex. clinic’s medicines, clinic’s medical checkup, clinic’s dental checkup, counseling, free homework tutorial, etc. – per Class Section per year level).

2. Have a generic worksheet which summarizes the monthly availments in a simpler table (some data in the month’s worksheets should not be showing – example: name of student, name of nurse who administered, time and date).

3. When I click a drop down menu (to choose the month) in generic worksheet, the data in the table changes along with the month. For instance, when I click July in the drop down menu, the data in the table will reflect the totals of July (which I have edited in the July’s worksheet).

I don’t know how to work on No. 3. What I have been doing is change the month in the title, change the worksheet source all the time. It is tiring and can be cause of error when I have to work on changing manually.

Please help me. Thank you so much!!!

Reply
Ollie - July 7, 2016

Good article, is it possible to have 2 drop down lists with the same values, but when 1 value is selected in the first list the second list’s value updates as well?

Not sure if it’s completely clear but lets say I have a list of months as my range of data, I have a cell that is a drop down list of the months and another cell that is also a dropdown list of the months but on a different sheet, and I’m asking if one can be selected and affect the other and if it’s reversible. So I can choose the month in either cell and the other cell will update based on the other one.

Reply
    Jon Acampora - July 12, 2016

    Hi Ollie,
    That can be done with a very simple macro.

    Range("A4").Value = Range("A1").Value

    That line of code will need to be placed in the Worksheet_Change event macro for the sheet that the drop-downs are on.

    The Worksheet_Change event when run when any change is made in the worksheet, so you might want to limit it to only the cells with the drop-down by using the Target cell and Intersect method.

    I hope that helps. Thanks!

    Reply
      Ollie - August 4, 2016

      Thanks for the help, I’ve since learned how to use pivot tables to accomplish what I needed to do and using connected slicers it was much easier and simpler.

      Reply
Rodger - May 28, 2016

Jon,

I’ve looked at the additional workbooks for the 3 lists, but feel my example doesn’t fit.

Example.
Category: 1) Condo, 2) Apartment
Type 1: 1) Chicago, 2) NY, 3) LA
Type 2: 1) Company A, Company B, 3) Company C

In this particular example the 3 different companies may or may not have condos and apartments and the three different cities.

Comparing the above to your beverage selection format, if the user selects “Condo” and “Chicago”, how would the third selection know what company to go to? If searching for “Chicago” it may show companies that have apartments in Chicago too. Or no? I’d like to avoid creating multiple tables for each scenario.

I came up with the below formula for the third drop-down but it differently has interesting quirks: 1) when opening the workbook, the drop-down list only works after going into the data validation formula and 2) when it does work, it’s slow at generating the drop-down list.

=OFFSET(Sheet1!$G$1,MATCH(C3&C5,Sheet1!$G:$G&Sheet1!$H:$H,0)-1,2,COUNTIFS(Sheet1!$G:$G,C3,Sheet1!$H:$H,C5),1)
Thx

Reply
    Jon Acampora - May 30, 2016

    Hi Rodger,
    Great question! It looks like you are on the right path with a multiple criteria lookup with the Match function. Instead of concatenating the lookup values in the formula and using an array formula, you might want to try doing the concatenation in another helper cell and column in your tables. I will try to write up another solution on this in the future. It would make for a great example. Thanks again!

    Reply
      Brandy - November 9, 2016

      Have you written up the article for that? I could REALLY use it right now! When I have a list of clients, and then departments and then contacts, and consequently all their info – this gets really complicated to just try and concatenate all of it. 🙂 Thankfully, once I have contact name I can mostly just work it down – it’s those top 3 that are the hardest, and mainly that 3rd one. I’m going to try his method above, but I don’t like the idea of it slowing us down, so I’m VERY interested in your method. Please email me and I will keep an eye here as well.

      Reply
Paul - May 20, 2016

Hi Jon,

Fantastic article – this helped me out loads!

I have one question. I have a drop down box that returns either yes or no (C1), is it possible for the next cell down to return a dependent list if C1 is selected yes, to return text of “N/A” if C1 was selected as no and to be blank if nothing is selcted in C1?

Thanks again!

Reply
    Jon Acampora - May 21, 2016

    Thanks Paul! I think you would need a macro for that. You could have a formula that returned the N/A or blank automatically, but the formula would be overridden when the user made a selection from the drop-down. I will write up an article on the macro solution when I get some time. Let me know if you have questions. Thanks! 🙂

    Reply
Jeremy - May 4, 2016

Jon,

Thank you very much for a very clear, and very helpful article.

I wonder if you might be able to help adapt your formulas for a problem I’m having?

I’m keeping an accounting spreadsheet for my business. The first sheet keeps records of all bank transfers. Each transaction is detailed on a row, and the number of rows is of course ever increasing with new transactions.

On each row I want to create a parent drop-down list to select a type of transfer (Income, Overheads, Expenses, etc, etc), and then a child drop-down list to provide more detail for that (e.g. if it’s an Expense – travel, food, stationery, etc, etc).

Easy enough to do just the once, but essentially I’d like to be able to copy and paste this across each new row (or better yet, just set each of the columns with a single data validation formula), without having to amend the child drop-down formula each time, so that its referencing the correct, new parent drop-down list.

Hopefully that makes sense.. Can you think of a way to do this?

Thanks in advance!
Jez

Reply
    Jon Acampora - May 7, 2016

    Hi Jeremy,
    Great question! You could use Power Query to create a table with a list of unique values from the data table. Once it is setup all you have to do is refresh the query to repopulate the table with any new unique values. That table will be the source for the drop-downs listed above. I don’t have any tutorials on how to set that up yet, but will create something in the future. I think there could be a lot of uses for this. Thanks!

    Reply
    Jon Acampora - May 7, 2016

    Here is a video from my friend Mike Girvin on how to create the unique list for the drop down list with Power Query.
    https://www.youtube.com/watch?v=3ICk356kEZo

    Reply
Nate O - April 5, 2016

Another killer post! Just used this and it was perfect! I had been trying a method with defined ranges, but things got messy very quickly. The error checking feature was very helpful as well. Thanks!

Reply
Anders - March 31, 2016

Hi Jon
I have a range of courses that should be prioritized. How can I make a drop down list for 2nd prio etc. that excludes the already chosen courses?

Thanks for your advice
Anders

Reply
khushi - February 26, 2016

Hi. I need assistance to create 2 drop down lists with multiple choices selections on the same sheet. They will have their own lists.

Reply
Uma Manickam - February 4, 2016

Hi,

Its very useful for me. Thanks a lot…

Thanks,
Uma Manickam

Reply
Dan Rodriguez - January 29, 2016

THank you so much for sharing this tutorial! Made my day!!! I was looking everywhere and trying so many dead end things on my own… Thank you thank you!

Reply
Karl - January 7, 2016

Hi Jon,

Thanks for this. Just a quick question is it possible to create the drop down lists with a 1 to 4 relationship?

I.e. One parent list and 4 different child lists dependent on the parent?

How would i go about this if possible?

Reply
    Jon Acampora - January 22, 2016

    Hi Karl,

    Great question! I added a new file in the download section above for multiple dependents. The file includes a second drop-down that is dependent on the first.

    You will basically create additional tables in with the relationships. So in this case I created another drop-down for size. The size is dependent on the first drop-down (Category: Coffee, Tea, Wine). There are different sizes available for each drink type (Small, Medium, Large, etc.).

    Once you have the table setup it’s just a matter of changing the formula to return values from the new table. It’s in column G:H on the Lists tab.

    Let me know if you have any questions. Thanks!

    Reply
Jen - January 6, 2016

Is there a way to display multiple rows for each item in the 1st table. For example if coffee regular was selected it can list 2 sugars 2 milk in a single column?

Reply
    Jon Acampora - January 6, 2016

    Hi Jen,
    I’m not sure I fully understand your question. Do you want formulas that return multiple items in multiple cells based on what is selected in the drop downs?

    Reply
      jacob - March 8, 2016

      HI JON, NOT SURE IF THATS WHAT THEY WERE ASKING BUT THAT IS A QUESTION I HAVE. HOW CAN I INPUT A VARIABLE IN A CELL THAT CHANGES DEPENDENT ON WHAT IS SELECTED IN A DROP DOWN MENU THAT SAID FORMULA USES.

      FOR EXAMPLE I NEED A FORMULA THAT CHANGES A VARIABLE DEPENDENT ON IF A PERSON IS MALE OR FEMALE. (FOR NUTRITIONAL CALORIC INTAKE PURPOSES) SO A PERSON WOULD INPUT THEIR WEIGHT INTO CELL A, “MALE OR FEMALE” IN CELL B AND A GOAL (WEIGHT LOSS, MAINTENANCE, OR GAIN) IN CELL C AND THE OUT COME WOULD BE A TOTAL CALORIC NEED FOR SAID PERSON.

      Reply
Aryaman - December 4, 2015

Hi Jon,

You really made my life easy. Thanks a lot for your help. I have a problem here. I created a list and a dependent list successfully as you have explained in your article. I have the Parent List “Category” and the Child List “Sub-category”. I want that the field “Sub-Category” field should be reset or go blank when I change my selection in the “Category” dropdown list. Please revert as soon as possible.

Reply
hagit - October 30, 2015

excellent tuturial, thank you!!!

Reply
Sara - August 19, 2015

Hi – This is great 🙂 can you help – how would I do a 3rd dropdown depending on the selection of what you choose in the first 2 dropdowns – for example – 1st dropdown -coffee, tea, Wine – 2nd dropdown – Regular,Deaf,Black, White – 3rd dropdown – if you choose coffee then Black and then Sugar(3rd dropdown) if you choose coffee then White and then No Sugar( 3rd dropdown) Thanks

Reply
    Jon Acampora - August 22, 2015

    Hi Sara,
    Checkout the file named “3 Lists”. It has an example of how to do this. Let me know if you have any questions. Thanks!

    Reply
Randy - July 26, 2015

Thanks for this! This is the first one I’ve actually gotten to work. How do I now copy this formula down a set of rows? Do I have to manually change the row number for each drop down or is there another way to make it dynamic?

Thank you!

Reply
    Jon Acampora - August 3, 2015

    Hi Randy,
    You should be able to copy the dependent cell down. Just make sure the lookup_value in the Match function is NOT anchored. That means there should not be any dollar signs before the row number.

    In the following part of the formula make sure there are no dollar signs in front of the row number: MATCH(B6,

    The reference will then be relative, and you should be able to copy down the cell that contains the validation list.

    Please let me know if you have any questions. Thanks!

    Reply
Bastian - May 24, 2015

Thanks Jon!
This is working great for me. Is it possible to use above formulas also in userforms?

Reply
    Jon Acampora - May 29, 2015

    Hi Bastian,
    Are you referring to the combobox form control that you put on a worksheet? Or comboboxes in a userform? If it’s the form control then you will probably want to use one of the named range methods to create the dependent lists. Here is a tutorial by Debra Degleish at Contextrures on creating dependent comboboxes in userforms.

    Reply
Brandi - April 30, 2015

I am trying to create dependent drop down lists. I have tried a number of different tutorials, formulas and options and I am not sure where I am going wrong. I can get the first drop down list no problem, but the second drop down list will not populate. It doesn’t give me an error at all. I have tried tutorials with “their” information and it has worked just fine, but it does not with my information. So I am wondering if the data I am trying to use is the problem? A sample of my data is below. Any feed back would be appreciated.

Division (my first drop down list)
Division 4
Division 5

Section 4 (correlates with Division 4)
Unit Masonry
Cast Stone Masonry

Section 5 (correlates with Division 5)
Structural Steel
Structural Steel Erection

Reply
Hamid - February 17, 2015

Hi Jon,

Amazing post. Thanks very much! It seems to be working for me, but what I’m wondering about, is what I need to do to make the above work for a third list which depends on the first and second one? i.e. an additional level down the hierarchy.

Thanks!

Reply
    Jon Acampora - February 18, 2015

    Hi Hamid,

    Thank you! I’m glad you found it useful. I added a file to the downloads section above that contains the 3rd list.

    You basically just need to create another table that contains all your “types” from table 2. These become your “categories” for table 3, and you can then add “types” for table 3 that will be the options in your third drop-down list. The formula remains the same, it just references the new table/list that contains the third set of options. Checkout the file and let me know if you have any questions.

    Thanks again!

    Reply
      Hamid - February 19, 2015

      Thanks Jon! I had actually already created the same function, but it’s showing me duplicates and not showing all values. Although correctly specified, the COUNTIF isn’t picking up entire range. Would I be able to send file to you?

      Thanks!

      Reply
laci - February 2, 2015

Sorry I don’t get it. What’s the use of it. You choose category ( coffee ,tee, etc) and the type, and then what ?

Reply
    Jon Acampora - February 3, 2015

    Hi Laci,

    This technique can be used when you want your user to make selections from two or more lists. The selections might control what is displayed on a dashboard, run scenarios for a sensitivity model, or simply do a vlookup based on multiple criteria.

    The dependent drop down just means that the choices in the second list (drop down) depend on what is selected in the first drop down. If the user chooses coffee in the first drop down, then only types of coffee are going to be shown in the second drop down.

    I hope that helps. Please let me know if you have any other questions. Thanks

    Reply
Jeff Weir - November 20, 2014

Further to Bob’s comment, I show how to set up dynamic cascading dropdowns that don’t require named ranges at the following links:
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

The first link has an optional bit of VBA to clear downstream dropdowns if the upstream one changes.

The 2nd link accomplishes the same kind of thing, without VBA, by making it impossible to clear upstream dropdowns until all downstream ones are cleared.

I have another approach up my sleeve that uses only formulas, and allows you to have non-unique subcategories. Just need to write it up.

Reply
Livio - November 19, 2014

Following my previous, I am afraid to confirm you that in the web page of Chandoo there is an example on the same thema more clear, easy and quick to apply than yours. Any case, thanks for your effort. Regards, Livio

Reply
    Jon Acampora - November 20, 2014

    Hi Livio,

    I’m sorry you had trouble following the explanation. Thank you for letting me know. The formulas are located behind the scenes as the source of the validation. Select cell E6, then select the Data tab on the menu and click the Data Validation button. You will see the formula in the source box.

    If you don’t mind using named ranges then I agree that there are easier solutions to this problem.

    Thanks for your comment!

    Reply
Livio - November 19, 2014

interesting application, but I am not able to reply in my spreadsheet. Not easy understand application of OFFSET formula. The sample workbook to not show formulas in the cells; it was a help to look the formula in a different excel language than english and check the formula into the cartel. Please
Thanks

Reply
Frank - November 19, 2014

This technique does NOT require named ranges.

Maybe, there is even a technique that does NOT require Excel.

However, IMO using Excel tables with named ranges for dynamic data validation is superior to producing Frankenformulas, and easier as well.

Anyway, your faked dropdowns are gorgious!

Reply
    Jon Acampora - November 20, 2014

    Hi Frank,

    Yes, I completely agree. I will make it more clear that this technique is for those that do not want to use named ranges. Depending on the user of the model, I tend to avoid named ranges because they can be confusing for users that have not learned them yet.

    My next post will explain how to create the fake drop-downs. I’m sure you’ve already figured it out… 🙂

    Thanks again!

    Reply
Jeff Weir - November 19, 2014

…which you’ll find at these links:
http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/

Whoever wrote those is a freakin’ genius.

Reply
    Jon Acampora - November 20, 2014

    Hi Jeff,

    I’m guessing you left another comment and it did not come through. I’ve been having issues with comments in the latest version of wordpress…

    Thank you for the links. I will add those to the links in the article. This solution was meant for those that do not want to use named ranges or VBA.

    Using named ranges definitely makes this process much easier. I tend to avoid using named ranges because most users are not familiar with them, and they can be confusing for some.

    Thanks again Jeff!

    Reply
Leonid Koyfman - November 18, 2014

Another option to deal with the problem that Bob described (and it’s a compromise, not a real solution) is to conditionally format fill for Decaf to red based on the formula using COUNTIF of selected value in the list of Wine sub-data. If it’s 0 make fill Red and it will tell the user to make a valid selection.

Reply
    Jon Acampora - November 20, 2014

    Hi Leonid,

    That is a great suggestion! I updated the file to include that check. It basically uses the same OFFSET formula as the child list, with a COUNTIF to check if the parent and child items are related.

    Dependent Drop-down Selection Error

    I agree that it is a compromise. This example is meant for those that can’t use macros and want to avoid named ranges. Thanks again for the tip!

    Reply
Bob Umlas - November 18, 2014

Once you choose Coffee, for example, then choose Decaf, if you then change the Coffee choice to Wine, the Decaf still shows in the sub-data validation. I suggest this be an xlsm and whenever the 1st choice changes, the sub-choice should be cleared.
Bob Umlas, Excel MVP

Reply
    Jon Acampora - November 20, 2014

    Hi Bob,

    Sorry to not get back to you all sooner. For some reason I was not receiving comment notifications.

    This is a great point! This solution is meant for those that can’t use macros, and want to avoid named ranges. I’ll be the first to admit that there are much better ways to do this if you can use either VBA or named ranges. I will make that more clear in the opening paragraph, as I don’t want to waste anyone’s time.

    Leonid, has a great suggestion and I will update the file with a formula to validate the second entry.

    Thanks for stopping by Bob!
    Jon

    Reply

Leave a Reply:

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!
x