Creating Multiple Dependent Drop Down Lists

3 Flares Filament.io 3 Flares ×

Drop down lists can be created in Excel to assist with data entry and to prevent potential typos and misspellings. The Data Validation tool in Excel can be used to easily create lists.

However if you have many entries, you may need to break the list into multiple dependent drop down lists. By creating a cascading set of lists, the entry in one is dependent upon the selection made in another.

Jump to the Video

Naming the Ranges

The first step in creating the multiple drop down lists is to name each range of cells containing the entries for each list.

Each range that contains the entries for a list will need to be named. This is vital as it will establish the relationship between the lists.

For this to work, the name given to a range will need to match the wording of the item from the previous list.

The image below shows the items being used for some of the lists. Each range will be named the same as the header for each list. For example, range C2:C5 is named Films, which precisely matches the wording of Films in the Category list.

 Lists on spreadsheet

To create a named range;

  1. Select the range of cells you want to name.
  2. Click in the Name Box and enter the name you wish to use.

Creating a range name in Excel

Creating the Dependent Drop Down Lists

Now that each range is named we can get on with creating the lists. The first list is created normally, the other dependent lists will use the INDIRECT function to reference the previous list entry.

Creating the Initial Drop Down List

  1. Select the cells that you want to apply the drop down list to.
  2. Click the Data tab on the Ribbon and then Data Validation.
  3. Select List from the Allow menu.
  4. In the Source field, enter = followed by the named range for the initial list entries. In this example, I would enter =Category.

Creating the categories drop down list

Creating the Dependent Lists

The INDIRECT function will be used for the cascading lists to reference the selection from the previous list. This function converts the text (selected item) to a reference (named range).

  1. Select the cells that you want to apply the drop down list to.
  2. Click the Data tab on the Ribbon and then Data Validation.
  3. Select List from the Allow menu.
  4. In the Source field, enter =INDIRECT(A2). In this formula A2 is the first cell of the previous list. In this example, the reference must be entered without $ so that it is not fixed.
  5. This needs repeating for each list.

And that is that. You now have multiple dependent drop down lists to make the entering of large sets of data easier.

Multiple dependent drop down lists

Watch the Video

4 thoughts on “Creating Multiple Dependent Drop Down Lists

  1. Hi

    This is a great function. However, when I practised I got a #REF error in the dependent cell until a value had been chosen in the first. I know that this is because it was referencing a blank but how can I leave the second cell blank until the first selection is made?

    • You could add a IFERROR function around the formula in the dependent cell. For example =IFERROR(the formula producing #REF!,””)

      The “” will show a blank until the first list is used.

Leave a Reply

Your email address will not be published. Required fields are marked *