Arrow left
Back to guides
Google Sheets Tips

Dependent Drop-down List in Google Sheets: Explained

In this article, you will learn how to create a dependent drop-down menu in Google Sheets. 

What is a dynamic drop-down menu in Google Sheets?

A dynamic drop-down list means its choices are determined by another drop-down list it refers to. For example, assume you have three categories, Flower, Insect, and Animal. Each category has three subcategories: Rose, Lily, and Tulip for Flower; Ant, Bee, and Dragonfly for Insect; and Lion, Tiger, and Rabbit for Animal. You have two drop-down lists, one for the category and the other for the subcategory. The values in the pull-down menu for the subcategory change depending on the choice in the drop-down menu when the second drop-down menu is called dependent or dynamic drop-down list.

How do I create a dependent list in Google Sheets?

  1. Prepare a list of choices for the drop-down menus.
  2. Create the first drop-down list.
  3. Generate Named Ranges for ranges containing values for the second pull-down menu. The ranges' names must be those of the values in the first pull-down list.
  4. Insert the INDIRECT formula referring to the first drop-down list, whose output the second pull-down list refers to.
  5. Create the second drop-down list.

Learn the steps above by looking at the specific example below. Assume we will create the dependent list described in the upper section. Category items will be in the first drop-down list, and subcategories will change depending on the first choice.

  1. Categories: Flower / Insect / Animal
  2. (When Flower is chosen) Subcategories 1: Rose, Lily, and Tulip
  3. (When Insect is selected) Subcategories 2: Ant, Bee, and Dragonfly
  4. (When Animal is picked up) Subcategories 3: Lion, Tiger, and Rabbit

Step 1: You need to prepare a list of choices summarizing the information above  (or you can type them directly in the “Data Validation” window).

Step 1: Prepare a list of choices

Step 2: Select E2:G2 as a range for the first drop-down menu. Check the first pull-down list is created correctly. (If you don’t know how to create a drop-down menu, check this article - (i) List from a range under the “How to do data validation in Google Sheets” section)

Step 2: Create the first drop-down list based on the values in the list created in Step 1

Step 3: Create Named Ranges by category, which is utilized in the next step. Select E3:E5, F3:F5, and G3:G5 for Flower subcategories, Insect ones, and Animal ones, respectively. Ensure you name the ranges after the categories (Flower, Insect, and Animal).

Step 3: Make three Named Ranges

Step 4: Insert an INDIRECT function referring to the cell which contains the first drop-down list. If it works properly, combined with the named ranges, the INDIRECT formula should show one of the ranges containing subcategory names for the category selected in the first drop-down list. In this case, as “Flower” is chosen in the first pull-down menu, the INDIRECT presents its subcategory names.

Step 4: Create a list of subcategories by the INDIRECT function for the second pull-down list

Step 5: Create the second drop-down list by referring to the range the INDIRECT formula shows. Now you have the dependent pull-down menu. Ensure it works correctly by switching your choice in the first drop-down menu. If you change its choice, the content of the second pull-down list should change accordingly. (Note a value chosen from the previous first list in C3 remains unchanged when the first drop-down menu changes. You need to change the subcategory manually by choosing one item from the updated Subcategory list)

Step 5: Insert the second drop-down list (dependent list) by referring to values spread by the INDIRECT function

How do I link a drop-down list to another worksheet in Google Sheets?

  1. Copy the existing drop-down menu.
  2. Paste it into another worksheet in the same file.
  3. The same data validation rule is applied to the copied list.

Learn how to do this step-by-step in the video below 👇

Automate financial reporting with LiveFlow

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how

Need help?

Our team is here to help you any time between 9am and 10pm EST.
Check Icon
Email us at: help@liveflow.io

Liked this article? Then you'll love the ones below