How to create dependent drop-down list in MS-Excel

How to create dependent drop-down list in MS-Excel?

Last Updated by Rushali Chanana 09-Aug-20

0 663

In this blog, I will explain how to create a dependent drop-down list in Microsoft-Excel. A dependent drop-down list is a list in which one particular field, depends on the other.

The advantages of dropdowns include saving screen space, and also preventing users from entering erroneous data since they have only predefined values to choose from. It is quick and more accurate. Through an example of the table given below, we will learn what is a dependent drop-down list and how to create one.

In the table below, a dependent drop-down list will be formed in columns B and C, so that when a country is selected, the cities for that particular country are shown. These lists can be made for many purposes and not just the one mentioned here like countries and their cities. A snapshot of the excel which we will be using as an example is given below.

You can also learn this by watching our YouTube video.

Dependent drop-down list in Excel

You can follow these steps to create a drop-down list:

  • First, select the row naming the countries i.e. F4-F7 in this case. Now, click on 'Formula' ribbon and there select 'Create from selection'; then select 'Top row' and click on OK.

Go to Formula -> Create from Selection -> Select Top Row.

How create dropdown list in Excel

  • In the same way, select the next table showing the names of cities of each country, click on the 'Formula' ribbon; select 'Create from selection'; click on 'Top row' and then OK. 

Go to Formula -> Create from Selection -> Select Top Row.

Step wise learn How to create dependent dropdown list

Now the next thing we want is that when we mention the name of a country in B4, we see the names of cities (mentioned in the other table) of that country in C4. So for that, we have to create a dropdown.

To create this dropdown, click on the ‘Data’ ribbon. Select ‘Data validation’. A table will appear on the screen namely 'Data Validation'. There, in 'Allow' - select 'List'

Go to Data -> Data Validation -> Allow-select List

Next, in the same table, go to the 'Source' option and then select the row mentioning countries i.e. from F3 to F7 and finally click on OK.

Data Validation in Excel

You will see that a dropdown icon has appeared near the selected cell i.e. B4 in this case.

Learn Excel Formulas

Next, we want to see the names of the cities in C4. To accomplish that, we have to start with the same process, click on 'Data' ribbon. Go to 'Data validation'. In the table that appears, select 'List' in the allow option.

The only difference here is that in the source option, you have to apply a formula which is as follows: =INDIRECT(select the source i.e. B4 cell in this case) and click on OK.

Dependent drop-down list

After this step, you will a dropdown icon near C4 too, when you select the country in B4. Here you can choose the city.

Dependent drop-down list in Excel

You can see now that creating a dependent drop-down list may have seemed difficult at first sight, but it is quite facile and simple. We hope that this write-up has made the process simpler for you.

Please leave your comments below and don't forget to subscribe to our website for more such solutions.



Share:


Leave a Reply


Comments
    Waiting for your comments