Log in
x
or
x
x
Register
x

or

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.

Create dependent cascading drop down list with indirect function
Easily create dependent cascading drop down list with an amazing tool

More tutorial for drop down list...


Quickly create dependent cascading drop down list in Excel:

The Dynamic Drop-down List utility of Kutools for Excel can help you quickly create dependent cascading drop-down list in Excel with only several clicks as the below demo shown.
Download the full feature 30-day free trail of Kutools for Excel now!


Create dependent cascading drop down list with indirect function

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:


Create dependent cascading drop down list with an amazing tool

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 Select cells across two columns to output the list in the Output Range box;
  • 2.4 Click the OK button. See screenshot:

Now a dependent cascading drop-down list is created.

  If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


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.

More tutorial for drop down list...


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Francisco Castro · 8 months ago
    Excelente !!! ya he hecho este ejercicio varias veces, pero ahora me surge uno donde una de las opciones que debe desencadenar la validación como "Coffe, Tea o Wine"... ahora debo incluir una opción para que el usuario escriba cualquier texto!... no sé como hacerle... estoy atorado ahí... ojalá me puedan ayudar!Me refiero a que el usuario aparte de Café, Te y Vino puede escoger una opción más que se llame "Notas" y que en vez de desplegar alguna lista... le deje escribir cualquier texto... cómo ven? creen poder ayudarme?
    Mil Gracias!!
  • To post as a guest, your comment is unpublished.
    Paco Castro · 8 months ago
    Excelente !!! ya he hecho este ejercicio varias veces, pero ahora me surge una variante donde una de las opciones que debe desencadenar la validación como "Coffe, Tea o Wine"... ahora debo incluir una opción para que el usuario escriba cualquier texto! por ejemplo, aparte de Cafe, Te y Vino, habría una nueva que se llame "Nota" y en la siguiente columna, en vez de desplegar una lista, debo permitir que el usuario capture cualquier texto.

    ... no sé como hacerle... estoy atorado ahí... ojalá me puedan ayudar!
  • To post as a guest, your comment is unpublished.
    Luciene · 10 months ago
    Tenho uma planilha onde os dados da minha lista principal está em linhas e não em colunas como no exemplo. Neste caso minhas opções para a lista dependente está organizado em colunas. Já tentei várias manobras e não consigo êxito. Será que poderia me ajudar? Desde já agradeço