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.

Mark Vincent

Tech enthusiast and content creator passionate about making technology simple for everyone. I share practical tips, guides, and reviews on the latest in computers, software, and gadgets. Let’s explore the digital world together!