How to Use the Data Validation Feature in Google Sheets to Create Dependent Dropdown Lists

The Need for Dependent Dropdowns

Data Validation dropdown menus are essential for keeping spreadsheets clean and preventing users from making typos. However, a single, massive dropdown menu is often overwhelming. If you manage a global sales spreadsheet, forcing a user to scroll through a single list of 195 countries just to select “France” is terrible design.

The solution is a Dependent Dropdown List (also known as a cascading or conditional dropdown). This is a two-step system where the options available in the second dropdown menu change dynamically based entirely on what the user selected in the first dropdown menu.

For example, if Dropdown 1 is set to “Fruit”, Dropdown 2 will only show Apples, Bananas, and Oranges. If the user changes Dropdown 1 to “Vegetables”, Dropdown 2 will instantly update to show Carrots, Broccoli, and Spinach.

While this requires advanced VBA scripting in Excel, you can build it in Google Sheets using a clever combination of the Data Validation tool and the INDIRECT function.

Step 1: Prepare the Reference Data

Before building the dropdowns, you must structure your reference data perfectly on a separate sheet.

1. Create a new tab at the bottom of your Google Sheet and name it “Data”.
2. In cell A1, type the word Fruit. In cell B1, type the word Vegetables. (These will be the options for your first dropdown).
3. Below “Fruit” (cells A2, A3, A4), type your options: Apple, Banana, Orange.
4. Below “Vegetables” (cells B2, B3, B4), type your options: Carrot, Broccoli, Spinach.

Step 2: Create Named Ranges

The secret to this trick is assigning specific names to the blocks of cells you just created.

1. Highlight the cells containing the fruit (A2:A4).
2. Click Data > Named ranges in the top menu.
3. A sidebar will appear on the right. Name this range Fruit. (Crucial: The Named Range must be spelled exactly the same as the header in cell A1). Click Done.
4. Highlight the cells containing the vegetables (B2:B4).
5. In the Named ranges sidebar, click Add a range and name it Vegetables. Click Done.

Step 3: Create the Primary Dropdown

Now, go back to your main sheet where you want the actual dropdown menus to appear.

1. Click on the cell where you want the first dropdown (e.g., cell A2).
2. Click Data > Data validation > Add rule.
3. Under Criteria, select Dropdown (from a range).
4. Click the grid icon and select the headers from your Data sheet (Data!A1:B1), which contains “Fruit” and “Vegetables”.
5. Click Done. You now have a working primary dropdown.

Step 4: Create the Dependent Dropdown using INDIRECT

Now you will create the second dropdown in cell B2. This dropdown will look at whatever word is currently sitting in cell A2, and use the INDIRECT function to instantly pull the Named Range that matches that word.

1. Click on cell B2.
2. Click Data > Data validation > Add rule.
3. Under Criteria, select Dropdown (from a range).
4. In the range box, you cannot just click cells. You must manually type this exact formula:
   =INDIRECT($A$2)
5. Click Done.

Testing the System

The system is now fully functional.

Go to your primary dropdown in A2 and select Fruit. Then click the dependent dropdown in B2. You will see Apple, Banana, and Orange.

Go back to A2 and change it to Vegetables. If you click the B2 dropdown again, the fruit will be gone, replaced entirely by Carrot, Broccoli, and Spinach.

By organizing your data into Named Ranges and calling them with the INDIRECT function, you can create highly complex, user-friendly forms directly within Google Sheets.