In this topic we will see how to create a dependent drop down in excel i.e. a drop down that changes based on the selection of another drop down.
Suppose we want to create a drop down for Continent and a drop down for country that falls in the continent selected in the first drop down.
Step 1
Select C4 and go to Data Validation in the Data tab.
Step 2
Select the “List” in the Allow box and select the row 1 from F to J for the continent drop down and click OK.
Step 3
As you could see, the drop down for continent is created and now we will create the drop down for country based on the continent drop down.
Step 4
Now select the data range and press Ctrl+Shift+F3, a keyboard shortcut to create the name range from the selected data and keep only Top Row as checked and click OK as shown below .
Now the name range is created.
Step 5
Select C7, and go to data validation, choose “List” in the Allow box and insert the indirect formula as shown below and click OK.
Step 6
Now the drop down is created and is dependent on the continent selected in the continent drop down.
Hope this helped.