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 *

  • It shouldn’t have been so hard to search Google for the correct answer, but I found you. Thank you, thank you, thank you!

  • Hi, Very nice to see this tutorial. This is the exact way how i use OFFSET, with a drop down menu. The only thing is, last week i read about Volatile Excel Functions and OFFSET is one of them. This explains my frustration about how slow my file is and why it starts calculating when inserting/changing data. I was wondering, is a dependent list, like in this tutorial, also possible with index, match, etc. without using offset and/or indirect (the volatile functions)?

    Ricardo

  • Hello Jon,

    First of all, Thanks a lot for your help regarding this issue.
    I just wanted to ask you, is it possible to use several columns as a conditional?, I mean, imagine if you have three columns, named A,B and C. The idea is that, if I choose an option in A and B, create a conditional list in C based on the options of A and B.

    Thanks in advance!

  • This was great, Was able to use a template and perform this really quickly.
    Formula
    =OFFSET(Data!$A$1,MATCH(E2,Data!$A:$A,0)-1,1,COUNTIF(Data!$A:$A,E2),1)
    worked perfectly.
    🙂
    Greatly Appreciated

  • Hi Jon,
    Can we create searchable drop down lists (data validation or Combobox) just like google? There are many studies on web but non of them is for multiple Comboxes or data validations in the same worksheet. Namely, I want to create a calculation sheet using multiple Comboxes or Data Validations using the same source list. Since the source list consists of 12000 items, therefore I need to have a google search, definitely. Can you assist me on this subject?

    Thanks in advance…

    Levent

  • Great! Thanks, clear explanation! by the way I’ve learned about excel tables and used the structured reference formulas with excel tables names instead of cell addresses.

  • Hi,

    This is very helpful, but I’m still a little stumped. There is more I’d like to do but I am not sure it is possible.
    I can get my first drop down to work based on the unique created list, and I can get the second list to pull in that which is attached to the first, but not working for the third.

    I also want to have the user have the option of selecting or deselecting a drop down list to filter the data. As an example drop down 1 = Department Name, Drop down 2 = Provider Name, Drop down 3 = Location. I would like to find a way to allow a user to click Department only, Department + Location, All three or any combination of and dynamically change the data, or if not possible be able to select the department only and the option of not selecting anything in the other two and having the data stay at that higher level.
    I also need the drop downs to pull in corresponding data by date for a number of dates (1/1/2016, 2/1/2016, 3/1/2016). For example I want users to be able to select # appts by month for just the department selected, and then the department and corresponding drop down for the providers that work under that department, and then for a particular provider if they work at more than one location – Department, John Doe, select one of the 5 locations, and then be able to see the appts for the selected location.

    I thought I might be able to create a fourth drop down for date and maybe this would help but I’m also making this dynamic so that it feeds the top of a dashboard and changes a number of metrics and graphs so trying to keep the file as small as is possible for as much data as I have. My first draft had one drop down at the top that was driven off a list of metrics which were indexing SUMIF array formulas lower and hidden in the dashboard which were being driven off of pivot tables and a raw data file. It worked well, but adding a second and a third dropdown to define name and location and have all the data change is very challenging. I seem to have exhausted all my google searches, I’m not sure if this makes any sense, but if so, please help! : )

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

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

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

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

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

  • 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

  • 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

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

  • 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

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

  • 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

  • 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

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

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

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

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

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

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

  • 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

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

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

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

    • 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! 🙂

  • 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

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

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

  • 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

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

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

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

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

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

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

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

  • 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

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

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

  • 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

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

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

    • 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

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

  • 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

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

  • 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

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

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

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

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

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

  • 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

    • 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

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