How to create dependent cascading drop down list in Excel?
Supposing you have 3 lists as below demo shown, and you need to create a cascading drop down list to limit the choices in the second drop down list based on the value in the parent one. For example, parent drop down lists locate in column E with values of Coffee, Tea and Wine. When selecting Coffee from the parent drop down list, only the coffee items display in the second drop down list. This article will show you methods to achieve it.
Please do as follows to create a dependent cascading drop down list in Excel.
1. Select the cells which containing the values you will display in the parent drop down lists, 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 locate the second drop down lists and click Data > Data Validation > Data Validation to open. 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:
If the above method is time-consuming for you. Here highly recommended the Dynamic Drop-down List feature of Kutools for Excel for you. With this feature, you can create a dependent cascading drop-down list with several clicks only.
Before applying Kutools for Excel, please download and install it firstly.
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 Check the Dynamic drop-down list horizontally box;
- 2.4 Select cells across two columns to output the list in the Output Range box;
- 2.5 Click the OK button. See screenshot:
Now a dependent cascading drop-down list is created.
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!