In Microsoft Excel, a drop-down list is a helpful feature that allows users to select an option from a predefined list. However, what if you create a drop-down list with multiple levels of hierarchy or categories? In such cases, a multiple-level drop-down list can be very useful.
A multiple-level drop-down list is a type of drop-down list that contains sub-levels or categories within it. It allows users to choose an option from a hierarchical list structure with multiple levels of nested sub-categories.
For example, you may have a drop-down list for countries, followed by a list of states or provinces, then cities, and so on.
In this article, we will guide you through creating a multiple-level drop-down list in Excel using the Data Validation feature and named ranges. With this technique, you can easily create complex drop-down lists with multiple levels of hierarchy to suit your needs.
To create a multiple-level drop-down list in Excel
To create a multiple-level drop-down list in Excel, follow these steps:
Step 1: Set up your data structure:
- Create a table with your hierarchical data structure, with each hierarchy level in a separate column.
- For example, you might have a table with columns for Country, State/Province, and City.
Step 2: Define named ranges for each column:
- Select the data in each column and go to Formulas > Define Name.
- Give the range a meaningful name, such as “CityList” for the list of cities.
Step 3: Set up the first drop-down list:
- Select the cell where you want the first drop-down list to appear.
- Go to Data > Data Validation > Data Validation.
- In the Settings tab, select List from the Allow drop-down list.
- In the Source field, enter the named range for the first column (e.g., “CityList”).
Step 4: Set up the dependent drop-down lists:
- Select the cell where you want the second drop-down list to appear.
- Go to Data > Data Validation > Data Validation.
- In the Settings tab, select List from the Allow drop-down list.
- In the Source field, enter a formula that references the named range for the second column, using the INDIRECT function to refer to the value in the first drop-down list.
- For example, if your second column is for States/Provinces and the first drop-down list is in cell A2, the formula would be: =INDIRECT(A2&”List”).
- Repeat this step for each subsequent drop-down list, using the INDIRECT function and referencing the previous drop-down list cell.
Step 5: Test your drop-down lists:
- Check that your drop-down lists work correctly by selecting values from each list and confirming that the dependent lists update accordingly.
With these steps, you can create a multiple-level drop-down list in Excel using named ranges and data validation, allowing you to easily organize and analyze data with multiple hierarchy levels.
Kutools
Kutools is a Microsoft Excel add-in that offers a range of time-saving tools and utilities to help users perform various tasks more efficiently. Kutools simplifies various tasks by providing features such as merging cells without losing data, converting tables to lists, splitting text into columns, creating custom lists, and many more. Kutools includes various productivity tools like a batch export utility, an advanced find and replaces tool, and a duplicate remover. Overall, Kutools is a useful add-in for Excel users who want to save time and increase productivity.
To Create the 2 Levels Drop-down list horizontal in Excel
- If you have a menu with four categories, as in the below image, you can apply the Dynamic Drop-down list feature to create the 2 levels drop-down list in a horizontal direction.
- On the Kutools tab, Select the Drop-down list option.
- Choose the Dynamic Drop-down list from the menu. It will open the Dynamic Drop-down List dialog box.
- Select the Mode A: 2 Levels Dependent Drop-down list option under the mode section.
- Specify the Data Range by selecting the cells.
- Check the Dynamic Drop-down list Horizontal option.
- Select the Destination Range from the Output Range box.
- Now, hit the OK button. You have created the 2-Level Drop-down List in the horizontal direction.
To Create the 2 Levels Drop-down list Vertical in Excel
- It is similar to the above method, but the only difference is you do not tick the Dynamic Drop-down list Horizontal box.
- Follow the above steps to give inputs to the Mode, Data Range, and Output Range boxes.
- Now, hit the OK button. The output is placed in the Selected region.
To Create the 2-5 Levels Drop-down list in Excel
- The Following Table has 3 columns. Using that, we will create a three-level drop-down list using by Dynamic Drop-down List feature in Kutools.
- On the Kutools tab, Select the Drop-down list option and choose the Dynamic Drop-down list from the menu.
- It will open the Dependent Drop-down List dialog Box.
- Check the Mode B: 2-5 Levels Dependent Drop-down List option under the Mode section.
- Select the cells to give as input to the Data Range box.
- Select the cells to give as input to the Output Range box.
- Now, click the OK button to see the result.
Note
- In the Output Range box, you need to select the number of columns that are equal to the data range column. Otherwise, it will show the notification like the below image.
- In the Dependent Drop-down list dialog box, you can use the Sample button to show the example sheet.
Conclusion
In this article, creating a multiple-level drop-down list in Excel can be a powerful way to organize and manage data with multiple hierarchy levels or categories. Using the Data Validation feature and named ranges, Kutools, you can create a drop-down list with multiple levels of nested sub-categories that are easy to use and maintain. For more articles, you can visit our homepage.
Video Tutorial
Here is the video tutorial to creating a multiple-level drop-down list in Excel for your better understanding.
FAQ
A multiple-level drop-down list is a type of drop-down list that contains sub-levels or categories within it. It allows users to choose an option from a hierarchical list structure with multiple levels of nested sub-categories.
A multiple-level drop-down list can be helpful when you have data with multiple hierarchy levels or categories. It allows you to organize and manage the data in a structured way and makes it easier to filter and sort large amounts of data based on multiple criteria.
Yes, you can create a multiple-level drop-down list using Excel’s Data Validation feature. Using named ranges and dependent drop-down lists, you can create a hierarchical structure allowing users to select options from multiple levels of nested sub-categories.
You must update the underlying data structure and named ranges to update the data in your drop-down list. Once you have made the updates, the drop-down lists should automatically update to reflect the changes.
Hajira is a skilled financial analyst (Microsoft Certifications Completed) with more than 10 years of expertise in Excel. I love using Excel to assist individuals and organisations in enhancing their financial procedures. I like hiking and exploring outdoors when she’s not crunching mathematics. Follow me on my Mastodon account.
