dependent data validation list excel

How to create multiple level dropdown list in excel

How to create multiple level dropdown list in excel

Data validation in excel is one of the useful methods to put validation in cells of excel. In this article, we will learn how to create multiple level dropdown list. Multiple level dropdown list also known as dynamic data validation or multiple level data validations. It helps in putting validation in the second cell based on the selection of the first cell. By using this if we select a particular value in the first cell then the second cell will reflect values related to the first cell.

Let us start with an example of how to create multiple dependent drop down list in excel. We have a data set with two columns, the first Name of the country and the second Name of the city. We wanted to create a validation list in two cells with the condition name of the city that will show the list of the city only to the country selected in the first cell.

How to create multiple level dropdown list in excel
How to create multiple level dropdown list in excel

To create dependent validation in excel we will first take unique list of country name separately and create data validation for it. To create data validation in excel we will go to Data >> Data validation >> Data validation as mentioned in the below image.

Advance data validation
data validation list in excel

A new box will open, we will select options as mentioned below and then click ok.

excel data validation list
Data Validation and drop down list

Now after clicking ok we got the list of unique country name as below in the country name column.

data validation 4
excel dropdown list

Now we will create validation for Name of City, for which we will use indirect function. But before that we will arrange our data as below means names of city for each country in individual columns.

arranging data for data validation
arranging data for data validation

After arranging data we will define name for each column which contains name of cities. To define name we will got to Formulas tab >> Define Name >> Define Name . A new box will come like illustrated in the below image.

Define Name option in excel
Define Name option in excel

we will fill Name , Scope (selecting from dropdown), Comment, Refers to: (selection of list item) as illustrated in the below image and then click OK.

selection name range
selection name range

After clicking OK we will do it for other columns as well. After finishing for all we can check this as well. To check all Name Range we will got to Formulas >> Name Manager and then click ok. A new box will come as shown in the below image. It will reflect all define name, we can also edit, delete and create new names.

Excel Name Manager
Excel Name Manager

Now in the final activity we will go to cell below Name of City and will go to Data >> Data Validation >> Data Validation. In the new pop up box we will use excel function indirect =INDIRECT(Country Name) and then click ok.

use of indirect function in excel data validation list
use of indirect function in excel data validation list

Now if we will select any country name this cell will show list of that particular country only.

excel advance data validation
excel dynamic data validation

Was not it wonderful? It helps a lot when we are collecting data from different sources whose work is to feed the data in excel. As different people have different writing skill so this kind of validation helps in data consistency,

You can read more on dependent data validation in excel from the below article.

https://www.contextures.com/xldataval02.html

https://exceljet.net/dependent-dropdown-lists

https://www.excel-easy.com/examples/dependent-drop-down-lists.html

You can watch video as well on this which will help in more clarity on this.

Leave a Comment

Your email address will not be published. Required fields are marked *