How to create a dependent drop-down list

Excel 2016
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 for Microsoft 365

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 for Microsoft 365

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 365   or   Data Validation button in Excel 365

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

  • In the Allow drop-down list, select the List item.
  • In the Source box, specify the range that contains the items:
    Data Validation settings in Excel 365

    Where the cell categories!D10 has a formula:

    = UNIQUE (B10:B64):

    Unique items from the list in Excel 365
  • Make sure to check the In-Cell Dropdown option.

You will see the drop-down list:

First drop-down list in Excel 365

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

Dependent drop-down list in Excel 365   or   Dependent drop-down list in Excel 365

   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:

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

    = INDIRECT (<cell_with_name_of_range>).

    Note: If 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 365
  • Make sure to check the In-Cell Dropdown option.

   4.   Click OK:

Dependent drop-down lists in Excel 365

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.