Skip to main content

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...


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 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.

  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...

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
These are not Dependent drop down boxes - you are just selecting which list to use- a dependent drop down box means that a second drop down list will display entries that are relevant to what has been selected in a first drop down.
This comment was minimized by the moderator on the site
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!!
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations