Set Up Dependent Dropdown Lists in Excel or Google Sheets Step by Step
Get click-by-click setup for dropdowns and dependent dropdowns in your app, with exact named ranges, INDIRECT or FILTER formulas, and rules for blank or invalid entries.
You getSetup summary, source list layout and step-by-step setup
Prompt
You are a spreadsheet trainer who sets up data validation in Excel and Google Sheets every day.
Help me build dropdown lists, including dependent dropdowns where the second list changes based on the first choice. First ask me these questions in one message and wait for my answers:
1. Excel (which version) or Google Sheets?
2. What goes in the main list, and what goes in each dependent list? I will paste the items.
3. Which sheet, columns and rows will hold the dropdowns?
4. Can people leave cells blank or type values not on the list?
Rules:
- Use the exact menu names for my app and version, one action per numbered step.
- Put source lists on a separate sheet and write every named range and formula out in full.
- Handle list names with spaces or symbols, and offer a FILTER-based method if INDIRECT does not work in my app.
- Cover blank and invalid entries: error alert settings, what happens when the first choice changes, and a formula to flag mismatched pairs.
Output format:
1. Setup summary (2-3 lines)
2. Source list layout (table)
3. Step-by-step setup
4. Formulas used (table: Formula, Where it goes, What it does)
5. Blank and invalid entry handling
6. Testing checklist (4-6 checks)
End with one line on any limits of my version and what to check if a dropdown shows up empty.Example output
Tips and common edits
Why this works
- Asking for app and version first avoids menu names and functions that do not exist in the user's setup.
- Requiring full formulas plus a space-handling fix prevents the most common INDIRECT failure with multi-word list names.
- Covering changed first choices and pasted data catches invalid pairs that basic validation quietly lets through.
Use when building a form, tracker or template that needs controlled choices. Have your list items ready to paste and know your app version. Works in ChatGPT, Claude or Gemini.
Common edits
- Ask for a three-level dropdown, such as Region, Country, City, instead of two.
- Request a version using Excel Tables so new list items appear in the dropdown automatically.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Set Up Data Validation Rules That Stop Bad Entries in a Shared Excel Sheet
You get a column-by-column validation plan with allowed values, custom formulas, input and error messages, plus a helper formula that flags rows already holding bad data.
Design a Clean Spreadsheet Layout for a Tracker or Budget That Scales
You get a complete sheet blueprint with tabs, column specs, data types, dropdown lists, summary formulas and setup steps that keep working as rows grow.
Build an Employee Shift Schedule and Hours Tracker with Overtime in Excel
You get a ready-to-build spreadsheet layout with formulas for overnight shifts, unpaid breaks, weekly hours per employee and highlighted overtime.
Build an Inventory Tracker Spreadsheet with Reorder Points and Low-Stock Alerts
You get exact columns, copy-ready reorder-point formulas, a status column and conditional formatting that highlights items to reorder now or this week.
Build an Invoice Template in Excel or Google Sheets with Automatic Totals
You get a cell-by-cell invoice layout with exact formulas for line totals, discount, tax, due date, and a duplicate-proof invoice numbering system.
Design a One-Page Manager Dashboard in Excel or Google Sheets
Get a buildable one-page dashboard plan with a layout grid, a spec for each tile (metric, chart, filter, source range, formula) and numbered build steps.