Dependent Dropdown Lists in Excel Step by Step
Set up dependent dropdown lists in Excel so the second list changes with the first, with named ranges, formulas and fixes for common errors.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.
Show me how to build dependent dropdown lists in Excel for my data. First list (the parent): [PARENT_CATEGORY] with these options: [PARENT_OPTIONS]. Second list (the child) depends on it: [CHILD_CATEGORY], with options per parent: [CHILD_OPTIONS_BY_PARENT]. Where the dropdowns will sit: [CELL_LOCATIONS]. My Excel version: [EXCEL_VERSION] ([VERIFY] menu names for this version and platform). Give me two methods and say which one I should use: Method A uses named ranges and INDIRECT. Method B uses a dynamic approach with FILTER or OFFSET, if my version supports it. For each method provide numbered steps with exact menu paths, the formulas to paste with my real range addresses, the Data Validation settings to choose, and what the layout of my source table should look like (show it as a small table). Then add: - How to make the lists grow automatically when I add options. - How to clear the child cell when the parent changes (explain the options without writing macros unless I ask). - A troubleshooting table with columns Problem, Likely cause, Fix. Include: names with spaces, blank entries showing, source reference error, arrows not showing. Do not assume menu locations; mark them [VERIFY] for my version. Write for someone who has never used Data Validation. If my option names contain spaces or symbols, explain how to name the ranges safely.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.