How to Create a Dependent drop down list

As most of us already use drop down lists through data validation but sometimes it makes much more sense to create a dependent drop down lists e.g. you create a drop down list in cell A1 and as you choose any item from that list, the items of the other drop down list in cell B1 will change accordingly. So let’s see guys how we can create it in Excel:

Just have a look at the below screenshot, in this example we have a table with 4 columns with 4 States of India in each column and below each column we have respective Districts. Our motive is to create a drop down list in cell A3, which shows all the 4 States and then we will create a drop down list in cell B3 which will be dependent on the States’ drop down list and will show only the respective Districts.

Now let’s just follow the below steps to create a dependent drop down list:

Step 1 First we need to create assign names to the categories row as well as all the 4 States. To do this, first select the range D1:G1 and then click in the Name box and write a name as State and then hit enter.

Step 2 Now we need to select all the respective Districts below the State one by one and give it a respective State name range through the Name box. You can just repeat the same procedure discussed in the above step to assign a name. E.g. to assign a name to the Districts of Rajasthan we will select range from D2:D24 and write Rajasthan in Name box.

Step 3 Now we will create the First drop down list for States in cell A3. For this just activate cell A3 and then go to Data tab -> Data Validation

Now in the Data Validation dialog box, click Settings tab, choose List from the Allow drop down list, and input this formula =State into the Source box and then press ok. Now we have the drop down list with 4 states in cell A3.

Now to create a dependent drop down list in cell B3, just activate cell B3 and then again go to Data tab à Data Validation and now in the Data Validation dialog box, click Settings tab, choose List from the Allow drop down list, and input this formula =Indirect(A3) into the Source box and then press ok. Here we will get the list of respective Districts, depends on which State we choose in cell A3.