Note: The other languages of the website are Google-translated. Back to English

Quickly create multiple levels drop-down list in Excel

In general, it’s easy to create a drop-down list with Data Validation feature in Excel. But how about creating a dynamic drop-down list with multiple levels, says 3 levels in Excel? Here, with the Dynamic Drop-down List feature of Kutools for Excel, you can easily create a 2-5 levels drop down list in Excel.


 Create a 2 levels drop-down list horizontally in Excel

Supposing you have a menu with 4 categories as below screenshot shown. Now you can apply the Dynamic Drop-down List feature to create a 2-level drop-down list in horizontal direction as follows:

1. Click Kutools > Drop-down List > Dynamic Drop-down List to enable this feature.

2. In the Dependent Drop-down List dialog, please configure as follows:

(1) Check the ModeA: 2 Levels dependent Drop-down list option in the Mode section;
(2) In the Data Range box, select the source data you used for the dependent drop-down list;
(3) Tick the Dynamic drop-down list horizontally option;
(4) Specify the destination range in the Output Range box;
(5) Click the OK button.

Tips: In the Output Range box, please select two columns to output the dynamic drop-down.

Now you have created a 2-level drop-down list in the horizontal direction. See screenshot:

 Create a 2 levels drop-down list vertically in Excel

You can also create a 2-level drop-down lists in the vertical direction easily with the Dynamic Drop-down List feature in Excel.

1. Click Kutools > Drop-down List > Dynamic Drop-down List to enable this feature.

2. In the Dependent Drop-down List dialog, please configure as follows:
(1) Check the ModeA: 2 Levels dependent Drop-down list option in the Mode section;
(2) In the Data Range box, select the source data you used for the dependent drop-down list;
(3) Specify the destination range in the Output Range box;
(4) Click the OK btoon.

Tips:
(1) In the Output Range box, please select two rows to output the dynamic drop-down.
(2) Do not tick the Dynamic drop-down list horizontally option.

Now you have created a 2-level drop-down list in the vertical direction. See screenshot:

shot dynamic drop down list 03


 Create a 2-5 levels drop-down list in Excel

For example, I have a table with continents, countries, and cities as below screenshot shown. And I will use the Dynamic Drop-down List feature to create a 3-level drop-down list in Excel.

1. Click Kutools > Drop-down List > Dynamic Drop-down List to enable this feature.

2. In the Dependent Drop-down List dialog, please configure as follows:
(1) Check the ModeB: 2-5 Levels dependent Drop-down list option in the Mode section;
(2) In the Data Range box, select the source data you used for the dependent drop-down list;
(3) Specify the destination range in the Output Range box;
(4) Click the OK button.

Notes:
1) If your data contains headers, please check the My data has headers option.
2) In the Output Range box, you must select a number of columns equal to the number of columns of the source data. Otherwise, it will pop up a prompt box as following screenshot shown after clicking the Ok button in the Dependent Drop-down List dialog.

shot dynamic drop down list 6

3) By default, the values in the created dynamic drop-down list are sorted by the original order of the source data. If you want to sort values alphabetically, click the Advanced settings button in the lower left corner of the dialog, and then select Sort alphabetically from the menu.

You can see the result as the following screenshot shown.

4) There is also an option in the Advanced Settings menu "Format all cells as text". If you encounter the following two error alerts when creating dynamic drop-down list. Here it is recommended that you enable this option.

Error alert 1   Error alert 2
     
5) If dynamic drop-down list already exists in the output range, the following dialog box will pop up, asking if you want to override it.

So far, I have created a 3-level drop-down list. See screenshot:

Tips: If your data contains 4 (or 5) columns, this feature will create a 4 (or 5) levels drop-down list accordingly.


 Note

1. In the Dependent Drop-down List dialog, you can click the Example button to open the example sheet. However, this operation will close the current dialog.

2. You can apply the Clear Data Validation Restrictions feature of Kutools for Excel to clear the data validation of drop-down lists from selections.


 Demo: Create multiple levels drop-down list in Excel


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL