How to create dependent cascading drop down lists in Excel?
Supposing you have three lists as below screenshot 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 a method to achieve this operation.
Recommended Productivity Tools for Excel/Office
Please do as follows to create a dependent cascading drop down list in Excel.
1. Select the cells which 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, give names to the corresponding Coffee, Tea and Wine items as Coffee, Tea and Wine separately in the Name Box as below screenshot:
3. Select the cells you want to create parent drop down lists, and then click Data > Data Validation > Data Validation. See screenshot:
4. In the Data Validation dialog box and under the Settings tab, select List from the Allow drop down list, enter =drinksuff into the Source text box, and then click the OK button.
Note: drinkstuff is the range name specified in step 1. Please change it based on your needs.
5. Select the cells you will create the second drop down lists, and then click Data > Data Validation > Data Validation to open the Data Validation dialog box.
6. In the Data Validation drop down list, select List from the Allow drop down list, enter =INDIRECT(E2) into the Source text box, and then click the OK button.
Note: In this case, E2 is the first cell contains the parent drop down lists.
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:
- How to clear dependent drop down list cell after selecting changed in Excel?
- How to create dynamic cascading list boxes in Excel?
- How to auto populate other cells when selecting values in Excel drop down list?
- How to autocomplete when typing in Excel drop down list?
- How to create a drop down list calendar in Excel?
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...