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

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

At a glanceDependent dropdown setup in Excel 365Category in B2:B500 drives Subcategory in C2:C500
  1. Build the Lists sheetEnter categories and subcategory columns
  2. Name the rangesCategories, plus Create from Selection for C1:E5
  3. Add Category dropdownB2:B500, List source =Categories
  4. Add Subcategory dropdownC2:C500, =INDIRECT(SUBSTITUTE($B2," ","_"))
  5. Handle blanks and invalid entriesStop alert, Ignore blank, red mismatch format
  6. Test the setupRun the 5-point testing checklist

Example for: an Excel 365 expense tracker, Category in column B and Subcategory in column C, rows 2-500 on the Expenses sheet, blanks allowed, no free typing.

1. Setup summary

You will store lists on a sheet called Lists, name each list, add a Category dropdown in B2:B500, then a Subcategory dropdown in C2:C500 that reads the Category with INDIRECT.

2. Source list layout (sheet: Lists)

Column AColumn CColumn DColumn E
CategoriesTravelOffice SuppliesSoftware
TravelAirfarePaperSubscriptions
Office SuppliesHotelInkLicenses
SoftwareMealsFurniturePlugins
TaxiPostageCloud Storage

3. Step-by-step setup

  1. Create a sheet named Lists and enter the table above.
  2. Select A2:A4, type Categories in the Name Box, press Enter.
  3. Select C1:E5.
  4. Go to Formulas > Create from Selection, tick Top row only, click OK. Excel creates Travel, Office_Supplies and Software.
  5. On Expenses, select B2:B500.
  6. Go to Data > Data Validation > Settings, set Allow to List, Source to =Categories, tick Ignore blank.
  7. Select C2:C500 and open Data > Data Validation.
  8. Set Allow to List and Source to =INDIRECT(SUBSTITUTE($B2," ","_")). Tick Ignore blank. Click OK; if Excel warns the source is an error, click Yes (B2 is empty for now).

4. Formulas used

FormulaWhere it goesWhat it does
=CategoriesValidation source, B2:B500Shows the 3 categories
=INDIRECT(SUBSTITUTE($B2," ","_"))Validation source, C2:C500Turns Office Supplies into Office_Supplies and loads that list
=AND($C2<>"",COUNTIF(INDIRECT(SUBSTITUTE($B2," ","_")),$C2)=0)Conditional format, C2:C500Highlights subcategories that no longer match the category

5. Blank and invalid entry handling

  • Error Alert tab: Style = Stop, Title = Invalid entry, Message = Pick from the list. This blocks typed values.
  • Blanks: Ignore blank lets rows stay empty until used.
  • Changed category: Validation does not clear C when B changes. The conditional format above fills mismatched cells light red so you can fix them.
  • Pasted data: Paste skips validation. Use Data > Data Validation > Circle Invalid Data after imports.

6. Testing checklist

  1. Pick Travel in B2; C2 shows only the 4 travel items.
  2. Pick Office Supplies; confirm C2 shows Paper through Postage.
  3. Type Lunch in C2; the Stop alert appears.
  4. Change B2 to Software while C2 says Hotel; C2 turns red.
  5. Leave a row blank; no error appears.

Note: Excel for the web supports this setup; if a dropdown is empty, check that the category text exactly matches the range name after spaces become underscores.

Written by AI from this prompt. Your results will vary.

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