Dependent/Conditional Drop Down In Excel

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.

Capture

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.




Capture

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.

Capture

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 .

Capture

Now the name range is created.




Capture

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.

Capture

Step 6

Now the drop down is created and is dependent on the continent selected in the continent drop down.




Capture

Hope this helped.

Share The Knowledge

Random Posts

  • Split Date And Time In Excel

    We will see in this post how to segregate date part and time part from a datetime value in a […]

    Share The Knowledge
  • RGB And HSL In Excel

    We could use customized color pellet in excel based on our RGB and HSL values. Some companies work with only […]

    Share The Knowledge
  • Hide Content Of A Cell In Excel

    Let’s see how to hide the content of a cell in excel. Suppose we want to hide the content in […]

    Share The Knowledge
  • Set Size Of Excel Sheet To A4 and A3

    To make the excel printer friendly, we could manipulate the size of the excel sheet to fit our requirements. We […]

    Share The Knowledge

Leave a Reply