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. That meets most of our day to day requirements. However, when we create a dashboard or a template, we need to create a dependent drop down list or a conditional drop down list in Excel. That second drop down content should be changed based first dropdown content. It is very is to implement. So let us explore the way to create conditional drop down list in EXCEL.

Steps to Create conditional drop down list in Excel

To create a conditional or a dependent drop down list you need to use basic HLOOKUP function if your lists are organised vertically or vlookup function if your list is organised horizontally.

Let me you show you 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.

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. That is 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.
• =INDIRECT(HLOOKUP($A$2,Sheet1!$A$1:$C$1,1,FALSE))
• 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.