How to create conditional statements for drop-down lists in Google Sheets

Google Sheets is more than just a spreadsheet app — it’s a powerful tool for automating workflows and improving data entry accuracy. One of its most useful features is the ability to create drop-down lists. But what if you want the options in one list to change based on the selection of another? That’s where conditional drop-down lists come in.

In this guide, we’ll walk you through how to create conditional statements for drop-down lists in Google Sheets, step-by-step.

🎯 What Are Conditional Drop-Down Lists?

A conditional drop-down list dynamically changes its available options based on a value selected from another drop-down. This is also called dependent drop-downs. It’s perfect for forms, surveys, order sheets, or anything requiring categorized inputs.

✅ Step-by-Step: Creating Conditional Drop-Down Lists

Let’s say you want two drop-downs:

• Category: Electronics, Furniture
• Item: Changes based on selected category (e.g., if “Electronics” is selected, options are “Laptop”, “Phone”, etc.)

Step 1: Set Up Your Data Source

Create a new sheet (name it something like “Lists”) and list your categories and subcategories like this:

Note: Make sure the first row contains headers exactly matching the main category names.

Step 2: Name the Ranges

• Select the list under “Electronics” and go to Data > Named ranges.
• Name it exactly as the category name: Electronics.
• Repeat for each category.

This allows Sheets to use the INDIRECT function to match values dynamically.

Step 3: Create the First Drop-Down (Main Category)

1. Go to the main sheet where users will enter data.
2. Select the cell for the Category drop-down.
3. Click Data > Data validation.
4. Under “Criteria”, select List of items or List from a range that includes your category names.
5. Click Done.

Step 4: Create the Dependent Drop-Down (Subcategory)

1. Select the cell for the Item drop-down.
2. Click Data > Data validation.
3. Under “Criteria”, choose Custom formula is.
4. Use this formula: =INDIRECT(A2) (Assuming A2 is the category selection cell.)
5. Click Done.

Now, the options in the “Item” drop-down will change depending on the “Category” selected!

🛠️ Tips for Better Conditional Drop-Downs

• Avoid spaces and special characters in category names for easier range naming.
• Use data validation warnings to alert users when they select invalid combinations.
• Freeze your header row to keep category titles visible.
• Hide the data source sheet to keep your working sheet clean.

📌 Final Thoughts

Conditional drop-down lists in Google Sheets help streamline data entry, reduce errors, and enhance usability. With a bit of setup using named ranges and the INDIRECT function, you can create dynamic, responsive lists perfect for many use cases.