• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Excel Tips / Creating Multiple Dependent Drop Down Lists

Creating Multiple Dependent Drop Down Lists

Drop down lists can be created in Excel to assist with data entry and to prevent potential typos and misspellings. In this tutorial, you learn how to create multiple dependent drop down lists in Excel.

If you have lists with 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.

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.

Sample data for the dependent drop down lists

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 first drop down list for the categories

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 in Excel

Watch the Video

Related Posts:

  • Import Multiple Excel Files with Multiple Sheets in Excel
    Import Multiple Excel Files with Multiple Sheets in Excel
  • Compare Two Lists in Excel to Highlight Matched Items
    Compare Two Lists in Excel to Highlight Matched Items
  • Lookup Multiple Values in Excel
    Lookup Multiple Values in Excel
  • Compare Lists with VLOOKUP
    Compare Lists with VLOOKUP

Reader Interactions

Comments

  1. Nicola Wilson says

    21 February 2016 at 3:59 pm

    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?

    Reply
    • computergaga says

      21 February 2016 at 7:59 pm

      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.

      Reply
  2. anuja says

    12 July 2016 at 7:16 am

    i need third thing to depend on 1st and 2nd selection

    Reply
    • computergaga says

      12 July 2016 at 7:55 pm

      This can all be set up using the same technique for as many lists as you need. 2nd list dependent upon first, 3rd dependent upon 2nd and therefore 1st as well.

      Reply
  3. Kadr Leyn says

    18 January 2021 at 5:20 pm

    Hi,thanks for tutorial.

    The template I created by adding dependent drop-down lists on the userform may be useful for users.
    There are 3 dependent drop-down lists and a textbox on the userform. In the first drop-down list, suppliers are listed, categories according to the selected supplier are listed, products according to the selected category are listed. Finally, the price of the selected product is shown in the textbox.

    Reply
    • Alan Murray says

      18 January 2021 at 5:39 pm

      Thank you for sharing, Kadr.

      Reply
  4. Ranadhir says

    27 June 2021 at 7:16 pm

    Hi,
    What you have shown (and is mostly shared in other examples on the net) are 1-to-many relations of the dropdowns. However, I have a need of many-to-1 relation in 2 dropdown cols.
    Ex –
    1. Skills dropdown of any of (1, 3, 7, 11, 12, 14) – will generate Track – 4A in the dependent dropdown
    2. Skills any of (2, 4, 5) will generate Track – 3A in the dep dropdown… and so on…

    Do you think this can be achieved ? Additionally, I also want that Track column to remain editable, even after the generated value shows in. I hope I could explain my ask…

    Reply
    • Alan Murray says

      26 July 2021 at 9:16 pm

      Hi you would repeat Track – 3A in each of the lists for skills 2, 4 and 5 as it is an option for each of them.

      Reply
  5. Ali Albassami says

    1 July 2021 at 9:54 pm

    In the range name, it won’t accept any space between words. Is there any way to overcome such, as spaces would be a must?

    Thank you.

    Reply
    • Alan Murray says

      26 July 2021 at 9:01 pm

      Absolutely. I have a video here, Ali – https://youtu.be/aidOWOsMdD8

      Reply
  6. Murdikayasa says

    30 August 2021 at 6:06 pm

    Hi Expert,
    i am looking a way to create dependent drop down list from the cell which consist of multiple selection from drop down list.
    Example:
    Cell A1: Consist of Fruit, Vegetables
    Cell B1: Consist the formula of INDIRECT(A1) on the Data Validation.
    When the multiple value was selected on Cell A1, the dependent drop down selection is not working.

    Please help.

    Reply
    • Alan Murray says

      18 September 2021 at 8:19 pm

      The example shown here cannot handle two selections. Sorry.

      Reply

Leave a Reply Cancel reply

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

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·