The Google Sheets logo.

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.

Advertisement

For the purpose of showing all of the steps involved, we’ll keep everything in the same spreadsheet.

Lists of items in Google Sheets

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.

Cells for drop-down lists

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.”

Select 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.”

First named range for list items

Keep the sidebar open, select the second set of list items, and click “Add a Range.”

Advertisement

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.”

Second named range for list items

Once you have your named ranges, you can close the side panel and create the first drop-down list.

Named ranges in Google Sheets

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.

Select 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 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.”

Complete the Data Validation settings

You should then see your first drop-down list in the cell you selected.

First drop-down list

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.

Advertisement

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.”

INDIRECT function with results

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.”

Complete the Data Validation settings

Advertisement

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.

Select an item from the drop-down list

To confirm it all works, select your next list item and confirm the choices in the dependent drop-down list.

Select an item from the 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!

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »