Creating conditional drop down list in Excel

In my earlier post, I have shown you how to create a simple drop down list in Excel. But meets most of our day to day requirements. However, when we create a dashboard or a template, we need to dependent drop down list or a conditional drop down list in Excel. That is based on the first drop down list, the second drop down content should change. It is possible in Excel VBA. But you can do this very easily using the excel spreadsheet formula too.

Steps to Create conditional drop down list in Excel

To create a conditional or a dependent drop down list you need to know the basic HLOOKUP function if your lists are organised vertically or vlookup function if your list is organised horizontally. Another thing you should know on how to create named list in excel.  So let us start on how to create a dependent or conditional drop down list with a simple example.
We have three different mobile manufacturers name in the top and list of model names down the list in sheet1 as shown in the sample table below.

Dependent Drop Down Lists
Creating Dependent Lists

First you define the lists with the name of the manufacturer. In this example, i have created the name ‘Samsung’ for the range A2: A10, ‘Nokia’ to the range B2:B10, ‘Sony’ to  C2:C10. Like this you can create any number of lists.  You create these named ranges by going to ‘Formulas->Define Name’.
Now we need to create the first drop down list in sheet2 where we want to create the dependent conditional drop down. The first drop down list is the simple one. Just go to Data->Data Validation->Data Validation.Under validation criteria, select list under ‘Allow’ and select the source as the headings of your lists. Thats Sheet1, range A1:C1. Your first drop down list is ready. Now the second drop down list is dependent list. To achieve this we use, INDIRECT function and HLOOKUP function.
Go to  Data->Data Validation->Data Validation. Select list and the source enter the formula as mentioned below and click Ok.
Your dependent drop down list shows now. As you change the first drop down, second drop down list changes.
How this works
The INDIRECT functions, tells the excel that, the source is a reference and not a value or text. HLOOKUP function returns the name of the group based on the first drop down. Similarly you can do it with VLOOKUP if your lists are organised in vertical lists.

1 comment… add one
  • kishore konjeti Feb 18, 2015, 8:50 am

    very useful.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.