PlayInfoBox

Home /Utilities /How to Create a Dependent Drop-Down List in Excel

How to Create a Dependent Drop-Down List in Excel

Tap a step to see how to create a dependent drop-down list in Excel.

  1. Define a named range for each category's list

    Turn the items belonging to each category β€” like "Fruit" or "Vegetables" β€” into a range and name that range exactly after the category.

  2. Create the first cell's category drop-down

    Use "Data Validation" to build a drop-down where you pick a top-level category, such as "Fruit" or "Vegetables."

  3. Link the second cell's drop-down with the INDIRECT function

    In the second cell's Data Validation source, enter a formula referencing the first cell, such as "=INDIRECT($A$1)."

  4. Test that the sub-list updates when the category changes

    Pick "Fruit" in the first drop-down and confirm the second only shows fruit items like apple and banana.

  5. Why it helps

    Like choosing a region and only seeing that region's cities next, narrowing the second choice based on the first cuts down on input mistakes and keeps data much more organized.

When a drop-down list shows options that don't even apply

If choosing a region still shows every city nationwide, or picking a category still shows unrelated products, the list gets too long to search comfortably. A dependent drop-down narrows the second list to only what fits the first choice, making entry far more accurate.

Keep the source lists on a separate sheet

For a cleaner workbook, it helps to put all the named category lists on their own hidden or separate sheet rather than mixing them into your main data sheet. That way the working sheet stays uncluttered, and you can update or add categories in one place without touching the sheet people actually enter data into.

Frequently Asked Questions

Will it cause an error if a category name has a space in it?

Yes. A defined name cannot contain a space, so a category like "Processed Foods" needs to be written with an underscore or similar, following the naming rules, before it can be used.

Can I chain three or more levels together?

Yes. Using the same approach, a third or fourth drop-down can reference the previous cell's selection with INDIRECT as well, letting you narrow the choices down through multiple levels.