Drop down lists can be created in Excel to assist with data entry and to prevent potential typos and misspellings. The Data Validation tool in Excel can be used to easily create lists.
However if you have many entries, you may need to break the list into multiple dependent drop down lists. By creating a cascading set of lists, the entry in one is dependent upon the selection made in another.
Jump to the Video
Naming the Ranges
The first step in creating the multiple drop down lists is to name each range of cells containing the entries for each list.
Each range that contains the entries for a list will need to be named. This is vital as it will establish the relationship between the lists.
For this to work, the name given to a range will need to match the wording of the item from the previous list.
The image below shows the items being used for some of the lists. Each range will be named the same as the header for each list. For example, range C2:C5 is named Films, which precisely matches the wording of Films in the Category list.
To create a named range;
- Select the range of cells you want to name.
- Click in the Name Box and enter the name you wish to use.
Creating the Dependent Drop Down Lists
Now that each range is named we can get on with creating the lists. The first list is created normally, the other dependent lists will use the INDIRECT function to reference the previous list entry.
Creating the Initial Drop Down List
- Select the cells that you want to apply the drop down list to.
- Click the Data tab on the Ribbon and then Data Validation.
- Select List from the Allow menu.
- In the Source field, enter = followed by the named range for the initial list entries. In this example, I would enter =Category.
Creating the Dependent Lists
The INDIRECT function will be used for the cascading lists to reference the selection from the previous list. This function converts the text (selected item) to a reference (named range).
- Select the cells that you want to apply the drop down list to.
- Click the Data tab on the Ribbon and then Data Validation.
- Select List from the Allow menu.
- In the Source field, enter =INDIRECT(A2). In this formula A2 is the first cell of the previous list. In this example, the reference must be entered without $ so that it is not fixed.
- This needs repeating for each list.
And that is that. You now have multiple dependent drop down lists to make the entering of large sets of data easier.
Hi
This is a great function. However, when I practised I got a #REF error in the dependent cell until a value had been chosen in the first. I know that this is because it was referencing a blank but how can I leave the second cell blank until the first selection is made?
You could add a IFERROR function around the formula in the dependent cell. For example =IFERROR(the formula producing #REF!,””)
The “” will show a blank until the first list is used.
i need third thing to depend on 1st and 2nd selection
This can all be set up using the same technique for as many lists as you need. 2nd list dependent upon first, 3rd dependent upon 2nd and therefore 1st as well.
Hi,thanks for tutorial.
The template I created by adding dependent drop-down lists on the userform may be useful for users.
There are 3 dependent drop-down lists and a textbox on the userform. In the first drop-down list, suppliers are listed, categories according to the selected supplier are listed, products according to the selected category are listed. Finally, the price of the selected product is shown in the textbox.
Thank you for sharing, Kadr.
Hi,
What you have shown (and is mostly shared in other examples on the net) are 1-to-many relations of the dropdowns. However, I have a need of many-to-1 relation in 2 dropdown cols.
Ex –
1. Skills dropdown of any of (1, 3, 7, 11, 12, 14) – will generate Track – 4A in the dependent dropdown
2. Skills any of (2, 4, 5) will generate Track – 3A in the dep dropdown… and so on…
Do you think this can be achieved ? Additionally, I also want that Track column to remain editable, even after the generated value shows in. I hope I could explain my ask…
Hi you would repeat Track – 3A in each of the lists for skills 2, 4 and 5 as it is an option for each of them.
In the range name, it won’t accept any space between words. Is there any way to overcome such, as spaces would be a must?
Thank you.
Absolutely. I have a video here, Ali – https://youtu.be/aidOWOsMdD8
Hi Expert,
i am looking a way to create dependent drop down list from the cell which consist of multiple selection from drop down list.
Example:
Cell A1: Consist of Fruit, Vegetables
Cell B1: Consist the formula of INDIRECT(A1) on the Data Validation.
When the multiple value was selected on Cell A1, the dependent drop down selection is not working.
Please help.
The example shown here cannot handle two selections. Sorry.