by A while ago we started to explore the capabilities of Excel Data Validation and learned how to create a simple drop-down list in Excel based on a comma-separated list, range of cells or a named range.
Today, we are going to investigate this feature in-depth and learn how to create cascading drop down lists that display choices depending on the value selected in first dropdown. To put it differently, we will make an Excel data validation list based on the value of another list.
How to create multiple dependent dropdown in Excel
Making a multi-level dependent drop-down lists in Excel is easy. All you need is a few named ranges and the INDIRECT formula. This method works with all versions of Excel 365 - 2010 and earlier.
1. Type the entries for the drop-down lists
First off, type the entries you want to appear in the drop-down lists, each list in a separate column. For example, I'm creating a cascading dropdown of fruit exporters and column A of my source sheet (Fruit) includes the items of the first dropdown and 3 other columns list the items for the dependent dropdowns.
2. Create named ranges
Now you need to create names for your main list and for each of the dependent lists. You can do this either by adding a new name in the Name Manager window (Formulas tab > Name Manager > New) or typing the name directly in the Name Box.
Note. Please pay attention that if your first row is sort of column header like you see in the screenshot above, you shall not include it in the named range.
For the detailed step-by-step instructions please see How to define a name in Excel.
Things to remember:
- The items to appear in the first drop-down list must be one-word entries, e.g. Apricot, Mango, Oranges. If you have items consisting of two, three or more words, please see How to create a cascading dropdown with multi-word entries.
- The names of the dependent lists must be exactly the same as the matching entry in main list. For example, the dependent list to be displayed when "Mango" is selected from the first drop-down list should be named Mango.
When done, you may want to press Ctrl+F3 to open the Name Manager window and check if all of the lists have correct names and references.
3. Make the first (main) drop-down list
- In the same or in another spreadsheet, select a cell or several cells in which you want your primary drop-down list to appear.
- Go to the Data tab, click Data Validation and set up a drop-down list based on a named range in the usual way by selecting List under Allow and entering the range name in the Source box.
For the detailed steps, please see Making a drop down list based on a named range.
As the result, you will have a drop-down menu in your worksheet similar to this:
4. Create the dependent drop-down list
Select a cell(s) for your dependent drop-down menu and apply Excel Data Validation again as described in the previous step. But this time, instead of the range's name, you enter the following formula in the Source field:
=INDIRECT(A2)
Where A2 is the cell with your first (primary) drop-down list.
If cell A2 is currently empty, you will get the error message "The Source currently evaluates to an error. Do you want to continue?"
Safely click Yes, and as soon as you select an item from the first drop-down menu, you will see the entries corresponding to it in the second, dependent, drop-down list.
5. Add a third dependent drop-down list (optional)
If needed, you could add a 3rd cascading drop-down list that depends either on the selection in the 2nd drop-down menu or on the selections in the first two dropdowns.
Set up 3rd dropdown that depends on 2nd list
You can make the drop-down list of this type in the same fashion as we've just made a second dependent drop-down menu. Just remember the 2 important things discussed above, which are essential for the correct work of your cascading drop-down lists.
For instance, if you want to display a list of regions in column C depending on which country is selected in column B, you create a list of regions for each country and name it after the country's name, exactly as the country appears in second dropdown lists. For instance, a list of Indian regions should be named "India", a list of Chines regions - "China", and so on.
After that, you select a cell for the 3rd dropdown (C2 in our case) and apply Excel Data Validation with the following formula (B2 is the cell with the second drop-down menu that contains a list of countries):
=INDIRECT(B2)
Now, each time you select India under the list of countries in column B, you will have the following choices in the third drop-down:
Note. The displayed list of regions is unique for each country but it does not depend on the selection in the first drop-down list.
Create a third dropdown dependent on the first two lists
If you need to create a cascading drop down menu that depends on the selections both in the first and second drop-down lists, then proceed in this way:
- Create additional sets of named ranges, and name them for the word combinations in your first two dropdowns. For example, you have Mango, Oranges, etc. in the 1st list and India, Brazil, etc. in the 2nd. Then you create named ranges MangoIndia, MangoBrazil, OrangesIndia, OrangesBrazil, etc. These names should not contain underscores or any other additional characters.
- Apply Excel Data Validation with the INDIRECT SUBSTITUTE formula that concatenates the names of the entries in the first two columns, and removes the spaces from the names. For example, in cell C2, the data validation formula would be:
=INDIRECT(SUBSTITUTE(A2&B2," ",""))Where A2 and B2 contain the first and second dropdowns, respectively.
As the result, your 3rd drop-down list will display the regions corresponding to the Fruit and Country selected in the first 2 drop-down lists.
This is the easiest way to create cascading drop-down boxes in Excel. However, this method has a number of limitations.
Limitations of this approach:
- The items in your primary drop-down list must be one-word entries. See how to create cascading drop-down lists with multi-word entries.
- This method won't work if the entries in your main drop-down list contain characters not allowed in range names, such as the hyphen (-), ampersand (&), etc. The solution is to create a dynamic cascading dropdown that does not have this restriction.
- Drop-down menus created in this way are not updated automatically i.e. you will have to change the named ranges' references every time you add or remove items in the source lists. To get over this limitation, try making a dynamic cascading drop down list.
Create cascading drop-down lists with multi-word entries
The INDIRECT formulas that we used in the example above can handle one-word items only. For example, the formula =INDIRECT(A2) indirectly references cell A2 and displays the named range exactly with the same name as is in the referenced cell. However, spaces are not allowed in Excel names, which is why this formula won't work with multi-word names.
The solution is to use the INDIRECT function in combination with SUBSTITUTE like we did when creating a 3rd dropdown.
Suppose you have Water melon among the products. In this case, you name a list of water melon exporters with one word without spaces - Watermelon.
Then, for the second dropdown, apply Excel Data Validation with the following formula that removes the spaces from the name in cell A2:
=INDIRECT(SUBSTITUTE(A2," ",""))
How to prevent changes in the primary drop down list
Imagine the following scenario. Your user has made the selections in all of the drop-down lists, then they changed their mind, went back to the first list, and chose another item. As the result, the 1st and 2nd selections are mismatched. To prevent this from happening, you may want to block any changes in the first drop-down list as soon as a selection is made in the second list.
To do this, when creating the first dropdown, use a special formula that will check whether any entry is selected in the second drop down menu:
=IF(B2="", Fruit, INDIRECT("FakeList"))
Where B2 contains the second dropdown, "Fruit" is the name of the list that appears in the first drop-down menu, and "FakeList" is any fake name that does not exist.
Now, if any item is selected in the 2nd drop-down list, no choices will be available when the user clicks on the arrow next to the first list.
Creating dynamic cascading drop-down lists in Excel
The main advantage of a dynamic Excel dependent drop-down list is that you are free to edit the source lists and your drop-down boxes will get updated on the fly. Of course, creating dynamic dropdowns requires a bit more time and more complex formulas, but I believe this is a worthy investment because once set up, such drop-down menus are real pleasure to work with.
As with almost anything in Excel, you can achieve the same result in several ways. In particular, you can create a dynamic dropdown using a combination of OFFSET, INDIRECT and COUNTA functions or a more resilient INDEX MATCH formula. The latter is my preferred way because it provides numerous advantages, the most essential of which are:
- You have to create 3 named ranges only, no matter how many items there are in the main and dependent lists.
- Your lists may contain multi-word items and any special chars.
- The number of entries can vary in each column.
- The entries' sort order does not matter.
- Finally, it's very easy to maintain and modify the source lists.
Okay, enough theory, let's get to practice.
1. Organize your source data in a table
As usual, the first thing for you to do is to write down all the choices for your drop-down lists in a worksheet. This time, you will have store the source data in an Excel table. For this, once you have entered the data, select all of the entries and press Ctrl + T or click Insert tab > Table. Then type a name of your table in the Table Name box.
The most convenient and visual approach is to store the items for the first drop-down as table headers, and the items for the dependent dropdown as table data. The screenshot below illustrates the structure of my table, named exporters_tbl - the fruit names are table headers and a list of exporting countries is added under the corresponding fruit name.
2. Create Excel names
Now that your source data is ready, it's time to set up named references that will dynamically retrieve the correct list from your table.
2.1. Add a name for the table's header row (main dropdown)
To create a new name that references the table header, select it and then either click Formulas > Name Manager > New or press Ctrl + F3.
Microsoft Excel will use the built-in table reference system to create the name of the table_name[#Headers] pattern.
Give it some meaningful and easy to remember name, e.g. fruit_list, and click OK.
2.2. Create a name for the cell containing the first drop-down list
I know that you don't have any dropdown yet :) But you have to choose the cell to host your first dropdown and create a name for that cell now because you will need to include this name in the third name's reference.
For example, my first drop-down box is reside in cell B1 on Sheet 2, so I create a name for it, something simple and self-explanatory like fruit:
Tip. Use appropriate cell references to copy drop-down lists across the worksheet.
Please be sure to read the following few paragraphs carefully because this a very useful tip you that don't want to miss. Thanks a lot to Karen for posting it!
If you plan to copy your drop-down lists to other cells, then use mixed cell references when creating the name for the cell(s) with your first drop-down list.
For the drop-downs to copy correctly to other columns (i.e. to the right), use relative column (without the $ sign) and absolute row (with $) references like = Sheet2!B$1.
As the result, B1's dependent drop down list will appear in cell B2; C1's dependent drop-down will display in C2, and so on.
And if you plan to copy the dropdowns to other rows (i.e. down the column), then use absolute column (with $) and relative row (without $) coordinates like = Sheet2!$B1.
To copy a drop-down cell in any direction, use a relative reference (without the $ sign) like = Sheet2!B1.
2.3. Create a name to retrieve the dependent menu's entries
Instead of setting up unique names for each of the dependent lists like we did in the previous example, we are going to create one named formula that is not assigned to any particular cell or a range of cells. It will retrieve the correct list of entries for the second dropdown depending on which selection is made in the first drop-down list. The main benefit of using this formula is that you won't have to create new names as you add new entries to the first drop-down list - one named formula covers them all.
You create a new Excel name with this formula:
=INDEX(exporters_tbl,,MATCH(fruit,fruit_list,0))
Where:
exporters_tbl- the name of the table (created in step 1);fruit- the name of the cell containing the first drop-down list (created in step 2.2);fruit_list- the name referencing the table's header row (created in step 2.1).
I gave it a name exporters_list, as you see in the screenshot below.
Well, you have already done the major part of the work! Before getting to the final step, it may be a good idea to open the Name Manager (Ctrl + F3) and verify the names and references:
3. Set up Excel Data Validation
This is actually the easiest part. With the two named formulas in place, you set up Data Validation in the usual way (Data tab > Data validation).
- For the first drop-down list, in the Source box, enter =fruit_list (the name created in step 2.1).
- For the dependent drop-down list, enter =exporters_list (the name created in step 2.3).
Done! Your dynamic cascading drop-down menu is accomplished and will update automatically reflecting the changes you've made to the source table.
This dynamic Excel dropdown, perfect in all other respects, has one shortcoming - if the columns of your source table contain a different number of items, the blank rows will appear in your menu like this:
Exclude blank rows from the dynamic cascading dropdown
If you want to clean any blank lines in your drop-down boxes, you will have to take a step further and improve the INDEX / MATCH formula used to create the dependent dynamic drop-down list.
The idea is to use 2 INDEX functions, where the first gets the upper-left cell and the second returns the lower-right cell of the range, or the OFFSET function with nested INDEX and COUNTA. The detailed steps follow below:
1. Create two additional names
Not to make the formula too bulky, create a couple of helper names with the following simple formulas first:
- A name called col_num to reference the selected column number:
=MATCH(fruit,fruit_list,0) - A name called entire_col to reference the selected column (not the column's number, but the entire column):
=INDEX(exporters_tbl,,col_num)
In the above formulas, exporters_tbl is your source table's name, fruit is the name of the cell containing the first dropdown, and fruit_list is the name referencing the table's header row.
2. Create the named reference for the dependent dropdown
Next, utilize either of the below formulas to create a new name (let's call it exporters_list2) to be used with the dependent drop-down list:
=INDEX(exporters_tbl,1,col_num) : INDEX(exporters_tbl, COUNTA(entire_col), col_num)
=OFFSET(INDEX(exporters_tbl,1,col_num),0,0,COUNTA(entire_col))
3. Apply Data Validation
Finally, select the cell containing the dependent dropdown and apply Data Validation by entering = exporters_list2 (the name created in the previous step)in the Source box.
The screenshot below shows the resulting dynamic drop-down menu in Excel where all blank lines are gone!
Note. When working with dynamic cascading drop down lists created with the above formulas, nothing prevents the user from changing the value in the first dropdown after making the selection in the second menu, as a result, the choices in the primary and secondary dropdowns may mismatch. You can block changes in the first box after a selection is made in the second one by using either VBA or complex formulas suggested in this tutorial.
This is how you create an Excel data validation list based on the values of another list. Please feel free to download our sample workbooks to see the cascading drop-down lists in action. Thank you for reading!
Practice workbook for download
Cascading Dropdown Sample 1- easy version
Cascading Dropdown Sample 2 - advanced version without blanks
329 comments
Hello. I'm trying to create 3 dependent lists. The first is the item type, then the manufacturer, then the part number. I've got the first two. The third is going to be dependent on both the item type and the manufacturer. To complicate this a little the some manufacturers will make several different item types. I'm going to have several groups and some of the manufacturers will be in multiple groups as well. Is there a formula I can use to point to a table, then a column in that table, based on the first two criteria? Then I can use that formula to create a named range and use that named range as a source for my third dropdown?
Hi! All the necessary information is in the article above. To create multiple dependent drop down list, you can also find useful information in this article: Create a dynamic dependent drop down list in Excel an easy way.
Hello,
Excelent blog. Thank you for this.
Hi,
I notice from the example above during the preparation the columns for dependent dropdown are filled manually.
Is there a way to make it filled automatically? Because I have a really long data as the main drop down, and all the data are filtered from another table.
Hi! If I understand your task correctly, this article may be helpful: How to create drop down list in Excel: dynamic, editable, searchable.
Hi, I have a table listing foods in a column and each food has a row of nutritional values associated with the food listed in that row. I am trying to select several foods that are eaten for a meal and have the corresponding row of data populated on a sheet where all of the selected values in each column are summed up for daily nutritional totals for the day. Any hint on how to select from a list of foods and capture the nutritional data for summing?
Thanks!
Bill
Hi! You can choose each product in a separate cell using the drop-down list. In the next column, get the nutritional value data using VLOOKUP. I recommend reading this guide: Excel VLOOKUP function tutorial with formula examples.
This was very helpful, thank you for that.
Is it possible to have a primary table with multiple dependent tables?
For example, I have a table with 30 Organizations. Each of those organizations have a unique list of Areas, Departments, and accounts.
Hi! In each dependent drop down list, you can select the same main drop down list if you need to. But when you change the main drop down list, you must change the 30 other dependent drop down lists.
Hello, thanks for this very helpful tutorial! Is it possible to use this method to have the following set up: column A is the main list, column B shows lists depending on the choice in column A, and then having column C showing lists depending on the choices in columns A and B combined? If yes could you please point me out in the right direction to do this? Many thanks
Hi! It is this task that is described in detail in the article above.
Hi I have two workbooks - my source workbook is named "Backend Data" and my destination workbook is named as "Work Instruction".
In my source workbook there is a sheet named R50 with column D named Products and has different Products in this column. I want use this column D as a reference to get values in a single cell in my destination workbook. this single cell should then act as a dropdown list with the contents of the column D from my source workbook. I have tried everything such as named range, index, vlookup but still stuck please help
Hi! If I understand your task correctly, the following tutorial should help: Make drop-down menu from a range of cells. I hope itâll be helpful.
I get the first drop down to work, but the second one has nothing. I tried it with my data and nothing. Then I created a new document and used the data in this tutorial and get nothing. Ideas?
Hi! Try using the sample file linked at the end of this article. Or carefully follow all the recommendations described above. If you do everything correctly, the multiple selection macro will work.
hi,
i got a dropdown menu in the first column -> thats ok
i got a dropdown menu in the second column that depends on the answer in the first column -> thats also ok
i want a dropdown menu in the tird column that also just depends on the answer of the first column but with different choices
how do i make this work?
example
column 1 are different spiecies of wood
column 2 are the lengths that are possible for that type of wood
column 3 are the cross sections that are possible for that type of wood
thanx for helping
ads
Hi! You can create a dropdown list in column 3 the same way you created the dropdown list in column 2. Both your dropdown lists in columns 2 and 3 will depend on column 1. All the instructions are in the article above.
yes but i want the tird column to be dependent of the first column and not the second
The advice I already gave you is to create a third dropdown list that depends on the first dropdown list. This fits the description of your problem.
I am preparing students report and I need a formula that will lookup values of a specific student based on the selected term.
I used vlookup for only one term and it is working but how do I fetch their marks based on selected terms?
Hi! For the VLOOKUP formula, each term must be specified in a separate cell. To give more precise advice, please describe the problem in more detail.
Hi
I am new to this and trying to find out how once a user selects a choice in the first dropdown menu and then goes to a second dropdown menu using the same data, be able to select from the second dropdown menu everything else except the first choice that was made. So the list now reflects everything minus their first selection. Is that possible?
Hi,
Is there a way to sort the data in the "Source Table"?
When I sort the data in the 1st column, the other columns are also reordered.
Your example has short lists of data that are easy to sort by hand, but for longer lists (and less experienced Excel users) it becomes more difficult to do.
Hi! Before creating named ranges and converting a range of values to a Source table, sort the values in each data column. This will work if no new data is added.
This was a great tutorial, thanks! I've gotten mine to work, but I've found that the Data Validation > Error no longer works and I can enter text that is not an option in the dropdown. Did I miss a step where it's possible to stop users from typing in their own data?
Hi! Your problem has no relation to the macros in this manual. Check how your drop-down list works. You can use these instructions: How to create drop down list in Excel: dynamic, editable, searchable.
Hi, This article really helped me out. However, I am still stuck in solving my problem.
My drop down lists must be dynamic, because users may add or remove items on the list. It is not desirable to change the ranges on every update. That is why I am using a table.
In A1 I have the first drop down list, in B1 I have the second, cascading drop down list. I got this working thanks to this article. Yeey!.
However, I need to have the same values/conditions to work on the subsequent rows as well. Therefore I don't want to name every new cell on every new row. I tried to work with data validation. But that did not work out.
Any help or ideas how to get this working for many rows instead of just one?
Thanks
Hi! If I understand your task correctly, the following tutorial should help: Create a dynamic dependent drop down list in Excel an easy way.
Thanks
It works and very benefit
đ
Hi
Let suppose that I have Two Dropdown lists.
A = Continent (Africa--America--Asia--Europa--Oceania)
B = Country (Countries related to each continent)
Both A and B dropdown list are multiple choice selected
If in A dropdown list I select like 'Africa, Asia' , in B dropdown list, there's no possibility for me to use INDIRECT function on A to have Africa and Asia countries as filter.
How can I solve this please.
Best
Hi! You can only use multiple selection in a drop-down list that does not have a dependent drop-down list. In your case, it is in drop-down list B.
Trying to create a cascading drop-down list in my Excel spreadsheet.
While the first drop-down list (category) works, the cascading part (sub-category) is not working. It's blank.
Here's my set-up:
On the first worksheet, I want to have a category drop-down list in column C (cells C4 thru C57). The column is titled "Select Category" (in cell C3).
Column D would contain the sub-category based upon the selected category in column C
On the second worksheet, I've entered all the categories and sub-categories as follows:
Column A contains the names of the categories. I have entered the word Category in cell A1. Cells A2 thru A15 contain the various categories. Some categories are a single word; others are 2 words.
Then in columns B thru O are the categories with cells B1 thru O1 having the same name as the categories in cells A2 thru A15.
In columns B thru O - starting in the second row (cells B2 thru O2) are the sub categories. Some categories do not have sub-categories so the second row in that column is blank.
I have followed the instructions in the tutorial, but it's not working. The sub-category (column D) is blank even though I select a category in column C.
Unfortunately, I cannot create a drop-down list in your Excel spreadsheet for you. Perhaps you did not use named ranges as described in the instructions above.
Hi all,
My requirement is a bit different from what you describe here (or any other example I could find on the web).
Essentially, I have a file with 3 columns:
Child, Parent, Additional data.
a, , ...
b, a, ...
c, a, ...
d, b, ...
So, b and c are a child of a. d is a child of b.
My 1st dropdown picks a (as that is on the highest level). My 2nd dropdown list should show b and c (as they belong to a).
The 3rd dropdown should show d if for the 2nd b was chosen. If c was chosen in the 2nd dropdown, the 3rd should have an empty list ( as c does not have children).
Do you have any clever ideas how to solve this?
Hello;
I run into something I could not figure out.
When I setup a dependent dropdown list with data validation in a TABLE, adding new rows to that table results in a data validation error in the primary dropdown in the row above when the dependent dropdown value in the row above has been filled!
When you add a new row to that table when the dependent dropdown value in the row above has NOT been filled, it gives no error.
WHY is that!
WHAT causes the error or does not cause the error.
I run out of ideas!
The formulas that I use:
Primary dropdown (C11) > =IF($D11="";lstRegions;"")
Dependent dropdown (D11) > =IF($C11="";"";INDIRECT($C11))
lstRegions is a defined name for the list of regions.
PS: I can send an example workbook if needed.
Hello, I'm trying to to create dynamic cascading drop-down lists in Excel using 2 levels. I've created the table "Families", the top row "L1_hierarchy", and want to place this onto a separate worksheet (same workbook) into cell $A2 (will fill downwards), which I call "L1_name".
I then create the "L2_hierarchy" using the formula: "=INDEX(Families,,MATCH(L1_name,L1_hierarchy,0))"
But when I try to create the cascaded drop down in cell $B2 (next to L1_name), which on the data validation is "=L2_hierarchy", the only drop down option I get is "L2_hierarchy" and not the specific cascaded drop-down list.
Any ideas why?
Hi!
Unfortunately, without seeing your data it is difficult to give you any advice. You may find the practice workbook useful, which you can download from the link at the end of the article.