How to Create a Dependent Drop-Down List in Google Sheets

Creating a dependent drop-down list in Google Sheets is a powerful way to guide users through clean, logical data entry. It’s perfect for forms, invoices, schedules—anything where one choice affects the next.

In this quick guide, I’ll show you how to build one step-by-step.

What Is a Dependent Drop-Down?

A dependent drop-down list changes based on another selection. For example:

• First drop-down: Country
• Second drop-down: Cities (shows only cities in the selected country)

This keeps your data relevant, clean, and user-friendly.

Step 1: Set Up Your Data

Start by organizing your data in a clean structure. Use separate columns or a new sheet.

Example:

But for dependent drop-downs to work best, use grouped lists:

Better layout:

Column A        Column B        Column C         Column D
Countries       USA             Canada           Mexico
                New York        Toronto          Mexico City
                Los Angeles     Vancouver
                Chicago

Each country gets a column, with its cities listed underneath.

Step 2: Create the First Drop-Down (Primary List)

1. Click on the cell where you want the first drop-down (e.g., A1).
2. Go to Data > Data validation.
3. Under Criteria, choose List from a range.
4. Enter the range of your country headers (e.g., Sheet2!B1:D1).
5. Click Done.

Now users can pick a country.

Step 3: Name the Ranges for Each Group

To make the second list respond to the first, you’ll use named ranges:

1. Highlight the list of cities under each country (e.g., B2:B4 for USA).
2. Go to Data > Named ranges.
3. Name each range exactly as its corresponding country (no spaces). So:
   • USA → USA
   • Canada → Canada
   • Mexico → Mexico

Repeat this for each country group.

Step 4: Create the Dependent Drop-Down

Let’s say cell A1 holds the country, and you want the dependent list in B1.

1. Click cell B1.
2. Go to Data > Data validation.
3. Under Criteria, choose Custom formula is.
4. Enter the formula: =INDIRECT(A1)
5. Click Done.

Now, when someone selects a country in A1, only its cities will appear in B1.

Common Gotchas to Watch Out For

• Names must match exactly. If the country name has a space (e.g., “United States”), you can’t name a range “United States.” Use underscores (like United_States) and adjust your formula to =INDIRECT(SUBSTITUTE(A1," ","_")).
• No overlapping named ranges. Each name must be unique.
• Case-insensitive. INDIRECT works regardless of text case.

Why Use Dependent Drop-Downs?

• Cleaner data.
• Faster entry.
• Fewer errors.
• Better user experience.

They’re simple once you’ve done it once—and incredibly useful.

Got any questions or want to automate this with scripts? Drop a comment below—I’d be happy to help.