Main Drop-down

Select the cell where you want the first (main) drop down list.
Go to Data → Data Validation. This will open the data validation dialogue box. Within the settings tab, select List.
In Source field, specify the range that contains the items that are to be shown in the first drop down list.

This will create the Drop Down 1.

Create from Selection

Select the entire data set where the Table headers match those from the Source field selection

Excel-Drop-Down-List-Conditional-Select-Entire-Dataset.png

Go to Formulas → Defined Names → Create from Selection (or you can use the keyboard shortcut Control + Shift + F3).

Excel-Drop-Down-List-Conditional-Create-from-Selection.png

In the ‘Create Named from Selection’ dialogue box, check the Top row option and uncheck all the others. Doing this creates 2 names ranges (‘Fruits’ and ‘Vegetables’). Fruits named range refers to all the fruits in the list and Vegetables named range refers to all the vegetables in the list.

Dependant Drop-down

Select the cell where you want the Dependent/Conditional Drop Down list

Go to Data → Data Validation. Within the setting tab, make sure List in selected.

In the Source field, enter the formula =INDIRECT(D3). Here, D3 is the cell that contains the main drop down.