Skip to main content

How to create multi level dependent drop down list in Excel?

In Excel, you may create a dependent drop down list quickly and easily, but, have you ever tried to create multi-level dependent drop down list as following screenshot shown? This article, I will talk about how to create a multi-level dependent drop down list in Excel.


Create multi-level dependent drop down list in Excel

To create a multi-level dependent drop down list, please do with the following steps:

Firstly, create the data for the multi-level dependent drop down list.

1. First, create the first, second and third drop down list data as below screenshot shown:

Secondly, create range names for each drop down list values.

2. Then, select the first drop down list values (excluding the header cell), and then give a range name for them in the Name box which besides the formula bar, see screenshot:

3. And then, select the second drop down list data and then click Formulas > Create from Selection, see screenshot:

4. In the popped out Create Names from Selection dialog box, check only Top row option, see screenshot:

5. Click OK, And the range names have been created for each second drop down data at once, then, you should create range names for the third drop down list values, go on clicking Formulas > Create from Selection, in the Create Names from Selection dialog box, check only Top row option, see screenshot:

6. Then, click OK button, the third level drop down list values have been defined range names.

  • Tips: You can go the Name Manager dialog box to see all the created range names which have been located into the Name Manager dialog box as below screenshot shown:

Thirdly, create Data Validation drop down list.

7. And then, click a cell where you want to put the first dependent drop down list, for example, I will select cell I2, then, click Data > Data Validation > Data Validation, see screenshot:

8. In the Data Validation dialog box, under the Settings tab, choose List from the Allow drop down list, and then enter this formula: =Continents into the Source text box, see screenshot:

Note: In this formula, Continents is the range name of the first drop down values you created in step 2, please change it to your need.

9. Then, click OK button, the first drop down list has been created as below screenshot shown:

10. And then, you should create the second dependent drop down, please select a cell where you want to put the second drop down list, here, I will click J2, and then go on clicking Data > Data Validation > Data Validation, in the Data Validation dialog box, do the following operations:

  • (1.) Choose List from the Allow drop down list;
  • (2.) Then enter this formula: =INDIRECT(SUBSTITUTE(I2," ","_")) into the Source text box.

Note: In the above formula, I2 is the cell which contains the first drop down list value, please change it to your own.

11. Click OK, and the second dependent drop down list has been created at once, see screenshot:

12. This step, you should create the third dependent drop down list, click a cell to output the third drop down list value, here, I will select cell K2, and then click Data > Data Validation > Data Validation, in the Data Validation dialog box, do the following operations:

  • (1.) Choose List from the Allow drop down list;
  • (2.) Then enter this formula: =INDIRECT(SUBSTITUTE(J2," ","_")) into the Source text box.

Note: In the above formula, J2 is the cell which contains the second drop down list value, please change it to your own.

13. Then, click OK, and the three dependent drop down list has been created successfully, see the below demo:


Create multi-level dependent drop down list in Excel with an amazing feature

Maybe the above method is troublesome for most of the users, here, I will introduce an easy feature-Dynamic Drop-down List of Kutools for Excel, with this utility, you can quickly create 2-5 levels dependent drop down list with only several clicks. Click to download Kutools for Excel!

Tips:To apply this Dynamic Drop-down List feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. First, you should create the data format as below screenshot shown:

2. Then, click Kutools > Drop-down List > Dynamic Drop-down List, see screenshot:

3. In the Dependent Drop-down List dialog box, please do the following operations:

  • Check the 3-5 Levels dependent Drop-down list option in the Type section;
  • Specify data range and output range as you need.

4. Then, click Ok button, now, the 3-level drop down list has been created as the following demo shown:

Click to Download Kutools for Excel and free trial Now!


More relative drop down list articles:

  • 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 example, when you select Lucy in the drop down list, it will auto populate a score 88 in cell D16.
  • Create A Dependent Drop Down List In Google Sheet
  • Inserting normal drop down list in Google sheet may be an easy job for you, but, sometimes, you may need to insert a dependent drop down list which means the second drop down list depending on the choice of the first drop down list. How could you deal with this task in Google sheet?
  • Create Drop Down List With Images In Excel
  • In Excel, we can quickly and easily create a drop down list with cell values, but, have you ever tried to create a drop down list with images, that is to say, when you click one value from the drop down list, its relative image will be displayed at the same time. In this article, I will talk about how to insert a drop down list with images in Excel.
  • Select Multiple Items From Drop Down List Into A Cell In Excel
  • The drop-down list is frequently used in the Excel daily work. By default, only one item can be selected in a drop-down list. But in some times, you may need to select multiple items from the drop down list into one single cell as below screenshot shown. How can you handle it in Excel?
  • Create Drop Down List With Hyperlinks In Excel
  • In Excel, adding drop down list may help us to solve our work efficiently and easily, but, have you ever tried to create drop down list with hyperlinks, when you choose the URL address from the drop down list, it will be open the hyperlink automatically? This article, I will talk about how to create drop down list with activated hyperlinks in Excel.

  • 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 and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... 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...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • 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...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • 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
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Bonjour,

Tout d'abord merci pour ce partage. Toutefois, lorsque j'utilise la formule "indirect-subtitut" cela ne fonctionne pas. Le message erreur de formule apparait sans que je puisse comprendre d'où vient l'erreur. J4 pour moi étant bien la cellule référence de niveau 2 pour arriver au niveau 3.
Étant sur un poste au travail en réseau, je ne peux installer de logiciels complémentaires.

Merci par avance.
This comment was minimized by the moderator on the site
c'est normal il y a une erreur dans la formule la bonne formule est la suivante +INDIRECT(SUBSTITUT(J2;"";"_")). il ni a pas d'espace et ce sont des points virgules et non pas des virgules
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations