change language:

With data validation, drop-downs can be added to cells. Here it will be explained how the options of one selection list can be made dependent on the selected value in another selection list.In the example below, cells A1 and B1 contain drop-down lists. In the example below, cells A1 and B1 contain drop-down lists. The intention is that if boy is chosen in A1, then only boy's names (cells D1: D4) are visible in drop-down list B1 and only girl's names (cells E1: E4) when selecting girl.

dependent drop-down lists 1

To realize this, a drop-down list must first be added to cell A1 with data validation with options boy and girl:

dependent drop-down lists 2

The next step is to name the ranges D1:D4 and E1:E4. The names must be 'boy' and 'girl'. The easiest way to do this is to first select the range and then type the appropriate name in the name box:

dependent drop-down lists 3

The last step is to add the dependent list box to cell B1. The source for this list must be entered: = INDIRECT (A1)

dependent drop-down lists 4

After this, only girl names will appear in B1's drop-down list if girl is selected in A1 and boy's names if boy is selected in A1. B1's list box is therefore dependent on A1's list box.

Questions / suggestions

Hopefully this article helped you to create dependent drop-down lists in Excel. If you have any questions about this topic or suggestions for improvement, please post a comment below.