How to create a dependent drop-down list

Excel 365
The drop-down list is a handy feature provided by Excel data validation tools. Drop-down menus limit the possible choices and help to avoid mistakes. However, not so many Excel users know the Source field of the Data Validation dialog is a formula field. Formulas in the Source field should return the list of the values, but they are not necessarily a range or a list of options.

Using the conditional formula, you can switch between lists depending on the value of the other cell. However, conditions are hard to support if you have many different option sets. Another option is to use dynamic lookups to create dependent dropdowns.

For example, if you have a spreadsheet with the groups of expenses:

Example of spreadsheet in Excel 2016

In this spreadsheet, there are more than ten different groups of expenses, and each group contains different kinds of expenses. To create dependent lists of expenses, do the following:

   1.   Create a named range (see Creating and using named ranges), for each group of expenses, for example:

Example of named ranges in Excel 2016

Note: To simplify future customizations, the name of the expenses group for as a range name. If your group name has two or more words, use symbol "_" (as "Fees & Charges").

   2.   Create the drop-down list for groups (see Creating a drop-down list in a cell):

   2.1.   On the Data tab, in the Data Tools group, click Data Validation:

Data Validation button in Excel 2016

   2.2.   In the Data Validation dialog box, on the Settings tab:

Data Validation in Excel 2016
  • In the Allow drop-down list, select the List item.
  • In the Source box, specify the range that contains the items.
  • Make sure to check the In-Cell Dropdown option.

You will see the drop-down list:

First drop-down list in Excel 2016

   3.   Create the dependent drop-down list for selecting expense types:

Dependent drop-down list in Excel 2016

   3.1.   On the Data tab, in the Data Tools group, click Data Validation.

   3.2.   In the Data Validation dialog box, on the Settings tab:

Data Validation for dependent list in Excel 2016
  • In the Allow drop-down list, select the List item.
  • In the Source box, specify the range:

    = INDIRECT (<cell_with_name_of_range>).

    Note: If it is necessary to make any changes for the name, use the text manipulation formulas, for example:

    = INDIRECT (SUBSTITUTE (<cell_with_name_of_range>, <old_text>, <new_text>))

    where

    • SUBSTITUTE converts Fees & Charges to the proper range name Fees_Charges
    • INDIRECT returns named range values for the specified range name.
Data Validation with substitute in Excel 2016
  • Make sure to check the In-Cell Dropdown option.

   4.   Click OK:

Dependent drop-down lists in Excel 2016

See also this tip in French: Comment créer une liste déroulante dépendante.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.