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.
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.
A new box will open, we will select options as mentioned below and then click ok.
Now after clicking ok we got the list of unique country name as below in the country name column.
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.
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.
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.
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.
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.
Now if we will select any country name this cell will show list of that particular country only.
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.
You can watch video as well on this which will help in more clarity on this.