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.
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.
Note: if you have not choose anything in cell A3 then after
hitting OK in the above step you will get an error like this :
This is just an informative error so just press OK to
continue.
Feel free to ask for help in comments if you are facing any problem while trying this.
If you like my post, then you can just put in your email ID at the top right corner of the page and you will get a FREE weekly newsletter with amazing Excel tips and notification of my latest post on this blog.