Creating Dependent Drop Down List in Excel

Previously we have learned to create “Drop Down List” in excel. In this blog post, we will go little advanced and learn to create “Dependent Drop Down” in Excel. It means, we have to create 02 Drop Down Lists in excel and out of these two, the second Drop Down list will change based on the selection made in the first Drop Down List.

For this, we have listed few countries from three different continents. These three continents are “Asia”, “Africa” and “Europe”. So our first drop down list will be for selecting Continents and the second drop down list will be for selecting Counties from respective continent.

1st Drop Down List: It is for selecting Continent

2nd Drop Down List: It is for selecting Countries from respective Continent.

We have to use “Define Name” functionality in excel for this entire process. This “Define Name” command we can find in “Formulas” tab under “Defined Names” group.

Select all the countries listed under continent ‘Asia’ and click on command “Define Name”.

Once we click on command “Define Name”, one dialog box named as “New Name” will appear on our excel screen as shown in below image. In this dialog box, under ‘Name’ field we have to write the name as “Asia”.

Again, select all the countries listed under continent ‘Africa’ and click on command “Define Name”.

Once we click on command “Define Name”, one dialog box named as “New Name” will appear on our excel screen as shown in below image. In this dialog box, under ‘Name’ field we have to write the name as “Africa”.

Again, select all the countries listed under continent ‘Europe’ and click on command “Define Name”.

Once we click on command “Define Name”, one dialog box named as “New Name” will appear on our excel screen as shown in below image. In this dialog box, under ‘Name’ field we have to write the name as “Europe”.

So, with the above steps, we have provided names to 03 ranges, and these names along with their data we can see under command “Name Manager”. Below screenshot will help you to find this command under “Formulas” tab.

Now, we will proceed to create our 1st drop down list for 03 Continents “Asia”, “Africa” and “Europe” This 1st drop down list we will create in cell with cell address “C3”. For this, select the cell “C3” and click on the “Data Validation” command which we can find under “Data” tab.

Once we click on the ‘Data Validation’ command, a dialog box named as “Data Validation” will appear on our excel screen. In this dialog box, select “List” under “Allow” category and Enter the data “Asia, Africa, Europe” under “Source” category as shown in below image and click on “OK”.

We can see our 1st drop down list is now ready in cell “C3” for 03 Continents “Asia”, “Africa” and “Europe”.

Now, our main task is to create 2nd drop down list which will depend upon the 1st drop down list which we have created in cell “C3”. This 2nd drop down list we will create in cell with cell address “E3”. The 2nd drop down list will be dependent on the 1st drop down list which we have created in cell “C3” and hence it is called as “Dependent Drop Down List”. For this, select the cell “E3” and click on the “Data Validation” command which we can find under “Data” tab.

Once we click on the ‘Data Validation’ command, a dialog box named as “Data Validation” will appear on our excel screen. In this dialog box, select “List” under “Allow” category and enter the formula “=INDIRECT($C$3)” under “Source” category as shown in below image and click on “OK”.

We can see our 2nd drop down list is ready in cell “E3” for the countries which will depend on the Continent selected in cell “C3”.

In this way we can create “Dependent Drop Down List” in excel.