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.
Advertisement
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.
The Best Tech Newsletter Anywhere
Join 425,000 subscribers and get a daily digest of features, articles, news, and trivia.
By submitting your email, you agree to the Terms of Use and Privacy Policy.
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.
Advertisement
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.”
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.”
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.”
Advertisement
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)Advertisement
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.”
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.
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!
Learning how to create a dependent drop-down list in Google Sheets is useful to create several lists of options for users to select from.
It is no surprise that most of us are well versed in creating dropdowns using the data validation feature in Google Sheets.
If you are not familiar with the data validation feature, do not be shy to check out our tutorial on how to use it, and learn through real-life examples for better understanding! 😇
Let’s take an example:
Imagine you are a supplier for chairs. Your main income derives from selling office chairs and restaurant seatings.
Your catalog contains hundred types of chairs from many different brands. It is always a hassle when taking orders from companies in bulk as there would be errors in the data collected.
By using the dependent drop-down list in Google Sheets, we are able to minimize the errors and collect cleaner and more reliable data.
As shown above, using the dependent drop-down list we are able to let the users select which type of chairs and what brands to order.
Let us use a real-life example as a guide to learn how to create a multi-row-dependent drop-down list in Google Sheets.
You may make a copy of the spreadsheet using the link I have attached below.
A Real Example of Using Dependent Drop Down List Feature
In this example, you are an Italian catering company that supplies food for different events.
For customers like event management companies, they have several events lined up that would need you to cater to. This makes it easy to make errors in the order form collected from your customers.
Let us create a multi-row dependent dropdown list in Google Sheets containing all the dishes from the menu for customers to select from.
This enables the customers to dependently select the dishes according to the events and the number of courses they want in an event.
Once the dependent dropdown list is created, we will be able to send this list through email to customers for them to list down all the dishes they want for each event.
How to Use the Dependent Drop Down List Feature in Google Sheets
Creating a multi-row dependent dropdown list in Google Sheets requires:
- First Dropdown List
- Prep Data List
- Second Dependent Dropdown List
The tedious part that needs more attention is when we are preparing the Prep Data List. It requires us to utilize several functions in a single formula.
Before you start, it is preferred to create three different sheet tabs for the Dependent Dropdown List, Master List, and Prep Data List.
This will enable your Google Sheets to be cleaner and easily navigated.
Second, you will also need to name the different ranges of data. This enables formulas to be easily understood and read.
For example, data within the cells of B5:B9 are named as Appetizers.
To do this, select Data, then Named ranges.
In the pop-up, input the desired name for the range of cells and select the range of cells to be named.
Take note that names inputted can only be a single word. If you want to put multiple words, input an underscore in between the words instead of pressing the spacebar.
Remember to categorize the remaining courses as well!
(a) First Dropdown List
- Let us create the first dropdown list. Simply click on the cell that you want to create your dropdown at. In this example, it will be B5.
- Then, we will need to use the data validation feature to create a dropdown list. Press Data, then Data validation.
- In the pop-up box, we will select List from a range in the Criteria section and define the range as ‘Master List (Catering)’!B4:F4. This will enable us to create a dropdown list showing the five different courses for customers to select from.
- Be sure to tick Show dropdown list in cell and select Reject input. You can also create a validation help text if desired.
- Once you press Save, a dropdown list is created with the five different courses from your Master Listing.
(b) Prep Data List
The reason for preparing a separate data list is because the data validation feature does not allow us to enter formulas directly.
As the Second Dependent Dropdown List is reactive to the First Dropdown List, we will prepare a separate data list using several functions in Google Sheets to help us link them together.
Follow the steps slowly, and I will explain why we use each function for your better understanding at the end.
- First, create a separate sheet tab for the Prep Data List. Click on the cell you want to write your function in. In this example, it will be A3.
- Then, input this formula into the cell.
- Once you press Enter, the list of appetizers will appear.
Let me explain how this formula works. Here is a visual representation of the entire formula:
First, the TRANSPOSE function is to help swap an array of data from columns to rows.
Second, the INDEX and MATCH functions help us to look up data from the Dependent Drop Down tab to the Master List.
The first argument in the INDEX function is to locate all the data located in cell B5:B9 in the Master List.
The second argument in the INDEX function is to specify which rows to return. Since we wanted all the rows to return, we left it blank.
In the third argument, we used the MATCH function to match the course selected in the Dependent Drop-Down tab to the range of courses in the Master List tab. We input ‘0’ as the match_type as we want the exact match to return.
Hence, if in the Dependent Drop-Down tab we select Soup for the dropdown in B5, the formula in the Data Prep Tab will return the list of dishes for Soup.
(c) Second Dependent Dropdown List
Finally, we can create a data validation for creating the dependent dropdown list.
- First, select the cell you want to create the dependent dropdown list in. In this case, it is C5.
- Similar to the steps above, press Data, Data validation. Select List from a range as the criteria. For the range of cells, input the cells in Prep Data tab, ‘Data Prep (Catering)’!A3:E3.
- Once you press Save, a dropdown is created. The dropdown list will vary depending on the courses you choose in column B.
Once you fill in the formulas for all the other cells, your sheet will look something like this.
There you go! You have successfully created a dependent dropdown list!
If you are still unclear with any of the functions used within this tutorial, you can also check out our tutorials on how to use the TRANSPOSE, INDEX and MATCH functions for a better understanding!