By using a drop-down list, you can make data entry more efficient and error-free. You can also create a dependent drop-down list in Google Sheets so that the first list choice determines the options for the second.
Dependent drop-down lists are useful for many situations. You may list products with specific attributes, car makes that have particular models, or websites with certain sections. By creating a drop-down list where the choice controls what appears in the second drop-down list, you can speed up data entry.
Let’s look at how to create a dependent drop-down list in Google Sheets.
RELATED: The Beginner’s Guide to Google Sheets
Set Up the List Items
To get started, enter the list headers and items for each list on a sheet. You can do this in the same sheet where you plan to insert the drop-down lists or another if you want the items out of view.
For this tutorial, we have Entrees and Desserts for our event. If you pick Entree in the drop-down list, you’ll see your choices in the second list. If you pick Dessert in the drop-down list, you’ll see those choices instead.
For the purpose of showing all of the steps involved, we’ll keep everything in the same spreadsheet.
You can also label or decide where you plan to insert the drop-down lists. Here, we’ll be adding those to cells A2 and B2 below the headers.
Name the Ranges
Next, you’ll name the ranges that contain the list items. This is necessary for the dependent drop-down list as you’ll see later.
Select the first list of items without the header, go to Data in the menu, and pick “Named Ranges.”
Enter the name for the range which should be the same as the first list item for the first drop-down list. In our case, we enter “Entree.” Then, click “Done.”
Keep the sidebar open, select the second set of list items, and click “Add a Range.”
Enter the name for the second set of items and here, this would be the second list item you can choose in the drop-down list. For our example, we enter “Dessert” and click “Done.”
Once you have your named ranges, you can close the side panel and create the first drop-down list.
Create the First Drop-Down List
Select the cell where you want the first drop-down list. For our example, this is cell A2 where you pick either Entree or Dessert. Then, go to Data > Data Validation in the menu.
In the box that appears, move to Criteria. Select “List From a Range” in the drop-down box and then enter the cell range containing the list headers. For our example, this is D3:E3 containing “Entree” and “Dessert.”
Check the box for Show Dropdown List in Cell. Choose what to show for invalid data, optionally include Show Validation Help Text, and click “Save.”
You should then see your first drop-down list in the cell you selected.
Insert the Function
Before you create the dependent drop-down list, you need to insert the INDIRECT function. The results are what you’ll use as the cell range for that second list. Use the cell location for your first drop-down list.
Go to an empty cell in the sheet and enter the following replacing the cell reference with your own:
=INDIRECT(A2)
When you choose an item from the drop-down list, you’ll see the INDIRECT function display the list items. So when we select “Entree” those list items appear and the same happens when we select “Dessert.”
Note: When nothing is selected, you’ll see an error for the formula. Simply choose a list item to see the Google Sheets function do its job.
Create the Dependent Drop-Down List
Now it’s time to create the dependent drop-down list. Go to the cell where you want the list and click Data > Data Validation from the menu as you did to create the first list.
RELATED: How to Restrict Data in Google Sheets with Data Validation
In the box that appears, move to Criteria. Select “List From a Range” in the drop-down box and then enter the cell range containing the list items that display from the INDIRECT function.
Check the box for Show Dropdown List in Cell, complete the invalid data and appearance settings per your preference, and click “Save.”
You can then give your lists a test! Select the first list item in the first list and you should see the correct items appear as choices in the second list.
To confirm it all works, select your next list item and confirm the choices in the dependent drop-down list.
You may have many more list items than our example, so when you’re satisfied that the lists work correctly, put them to work!
