How to create dependent cascading drop down list in Excel?
Dependent drop-down lists, also known as cascading drop-down lists, are a powerful feature in Excel that allows you to create dynamic lists where the options in one drop-down depend on the selection made in another. For example, the parent drop-down list is located in column E with values such as Coffee, Tea, and Wine. When selecting Coffee from the parent drop-down list, only coffee-related items will display in the second drop-down list. In this guide, we’ll walk you through the steps to create dependent drop-down lists, along with tips for optimization and advanced techniques.
Create dependent cascading drop down list with built-in features
Easily create dependent cascading drop down list with an amazing tool
More tutorials for drop down list...
Create dependent cascading drop down list with built-in features
Please do as follows to create a dependent cascading drop down list in Excel.
1. Select the cells that contain the values you want to display in the parent drop-down list, and then name the range in the Name Box (here I name the three column headers as drinkstuff in my case). See screenshot:
2. Next, name the corresponding Coffee, Tea and Wine items as Coffee, Tea and Wine separately in the Name Box as below screenshot:
3. Select a cell or multiple cells to locate the parent drop down list, click "Data" > "Data Validation" > "Data Validation". In this case, I select cell E2.
4. In the "Data Validation" dialog box, you need to:
- 4.1 Select "List" from the "Allow" drop down list;
- 4.2 Enter =drinksuff into the "Source" text box;
- 4.3 Click the OK button.
Note: Drinkstuff is the range name you specified in step 1. Please change it based on your needs.
5. Select a cell or multiple cells to create the second drop down lists and click "Data" > "Data Validation" > "Data Validation". Here I select cell F2.
6. In the "Data Validation" dialog box, please configure as follows.
- 6.1 Select "List" from the "Allow" drop down list;
- 6.2 Enter =INDIRECT(E2) into the "Source" text box;
- 6.3 Click the OK button.
Note: In this case, E2 is the first cell contains the parent drop down list.
Now the cascading drop down list is created. When selecting Coffee from the parent drop down list, only the coffee items display in the second drop down list. When Tea is selected in the parent drop down list, you can only select the tea items from the second drop down list. See screenshot:
- Use Tables for Dynamic Ranges: Convert your source data into an Excel table (select the range and press Ctrl + T). This ensures that your named ranges automatically expand when new data is added.
- Avoid Spaces in Named Ranges: Excel’s INDIRECT function doesn’t work well with spaces in named ranges. Use underscores (_) instead.
- Multi-Level Dependent Drop-Down Lists: You can create multi-level dependent drop-down lists (e.g., Category > Subcategory > Product) by extending the named ranges and using additional INDIRECT functions.
Create dependent cascading drop down list with an amazing tool
Struggling to manage hierarchical data in Excel? Kutools for Excel makes it incredibly easy to create dependent cascading drop-down lists that adapt dynamically to user selections. Whether you're working with categories, subcategories, or multi-level data, Kutools streamlines the process, saving you time and ensuring data accuracy.
1. Click "Kutools" > "Drop-down List" > "Dynamic Drop-down List" to enable the feature.
2. In the "Dependent Drop-down List" dialog box, please configure as follows.
- 2.1 Select "2 Levels dependent Drop-down list" option in the "Type" section;
- 2.2 Specify the whole table range in the "Data Range" box;
- 2.3 Select cells across two columns to output the list in the "Output Range" box;
- 2.4 Click the OK button. See screenshot:Tip: If you check the "Dynamic drop-down list horizontally" option, the dependent drop-down list will be displayed in the horizontal order.
Now a dependent cascading drop-down list is created in horizontal order.
Dependent drop-down lists are a game-changer for organizing and managing hierarchical data in Excel. By following this guide, you can create dynamic, cascading lists that adapt to user input, ensuring accurate and efficient data entry. Whether you’re a beginner or an advanced user, these techniques will help you work smarter and more effectively in Excel. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
Related articles:
Autocomplete when typing in Excel drop down list
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. This tutorial provides the method to solve the problem.
Create drop down list from another workbook in Excel
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the list data you need for the data validation locates in another workbook, what would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.
Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list. For making drop down list searchable, try the method in this tutorial.
Auto populate other cells when selecting values in Excel drop down list
Let’s say you have created a drop down list based on the values in cell range B8:B14. When you selecting any value in the drop down list, you want the corresponding values in cell range C8:C14 be automatically populated in a selected cell. For solving the problem, the methods in this tutorial will do you a favor.
Create drop down list with multiple selections or values in Excel
By default, you can only select one item per time from a data validation drop-down list in Excel. The methods in this article can help you make multiple selections from the drop-down list in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in