In this tutorial, you will learn how to use conditional data validation in Google Sheets
How To Use Conditional Data Validation in Google Sheets
When working with data in Google Sheets, you may find yourself needing to create dependent drop-down lists. This means that the options in one drop-down list will be based on the selection in another drop-down list.
For example, you may have a list of countries in one column, and a list of cities in another column. If you select a country from the first drop-down list, you only want to see the cities from that country in the second drop-down list.
In this guide, we will show you how to create dependent drop-down lists in Google Sheets, using data validation and named ranges.
How To Create a Conditional Drop-down List in Google Sheets
Here’s how to create a conditional drop-down list in Google Sheets.
Select the first list of choices you want to convert into a drop-down list.
Select the textbox on the top-left corner of the spreadsheet. Type the name you want to give this named range.
Create a named range for the other ranges you want to convert into a drop-down list.
Select the location of the first-level drop-down menu. In the Data menu, click on the Data validation option.
Select the List from a range option for the criteria section. The range should be the headers of both drop-down list ranges. Click Save to apply the data validation settings.
Next, select an empty column. Use the INDIRECT function to output the named range specified in the cell you’ve added data validation to.
Next, select the cell that will hold the second-level drop-down list. Select the Data validation option under Data.
Next, set up the data validation such that the choices come from the range that holds the output of the INDIRECT function. Click Save to apply the data validation settings.
You should now have a dependent drop-down list in Google Sheets.
Choosing a different option in the first drop-down list will change the list of choices on the second drop-down list.
This guide should be everything you need to use conditional data validation in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.
More ways to do conditional formatting Google Sheets:
- Remove Conditional Formatting
- How To Do Conditional Formatting
- Copy Conditional Formatting
- Conditional Formatting Based On Another Cell
- Conditional Formatting for Duplicates
- Conditional Formatting with a Checkbox
- Conditional Formatting with Multiple Conditions
- Conditional Formatting Based On Another Sheet
- Apply Conditional Formatting Across An Entire Row
- Conditional Formatting to Compare Two Columns
- Color Scale Based On Another Cell
- Relative Reference
- Conditional Formulas
- Conditional Formatting Based On Another Cell Not Empty
- Conditional Formatting Based On Another Cell Date
- If Cell Contains Text in
- Highlight Cells
- Highlight Duplicate Cells
- Make Negative Numbers Red