We can use Excel’s data validation features to generate a conditional drop down list which allows us to select a data value from a specified list. The Excel conditional drop down list is a handy feature for making data entry forms.
Figure 1. Conditional Drop Down List in Excel
The list displays a set of items in the form of a drop down inside a cell, and a selection can be made from the items in the drop down. A conditional drop down list can be useful whenever we have a set of names, regions, or products that we regularly have to enter into a range of cells.
How to Create Conditional Drop Down Lists in Excel
A conditional drop down list in Excel makes it easier for us to input only data that meets our requirements.
Conditional drop downs are easy to generate and use.
Below are the steps to set-up a conditional list in Excel;
- After selecting the cell where we want the main (first) conditional drop down list to appear, click on the “Data” tab, and then, “Data Validation”. This should open a data validation menu box;
Figure 2. of Data Validation Button in Excel
- Inside the “Data Validation” menu box, under the “Settings” tab, clock on “List”;
Figure 3. Data Validation Settings in Excel
- Within the “Source” field, enter the specific range of cells containing those items that should be displayed in the main conditional drop down list;
Figure 4. of Data Validation Settings in Excel
- Clicking “OK” will create our conditional drop down 1;
Figure 5. Conditional Drop Down in Excel
- Select the whole set of data (A1:B6 in our worksheet example);
Figure 6. Data Set for Conditional Validation in Excel
- Click on the “Formulas”tab and then, go to “Defined Names” > “Create from Selection” (or we can always use the keyboard hotkey Ctrl+Shift+F3);
Figure 7. Create from Selection Button in Excel
- Inside the “Create Names from Selection” menu box, tick the “Top row” option and untick all the others. This will create 2 name ranges (“Vegetables” and “Fruits”).
The “Fruits” range refers to each fruit in our list while the “Vegetables” range refers to each vegetable on our list:
Figure 8. Conditional Validation List Options in Excel
- Click on “OK”.
- Select a cell where we want our Conditional Drop Down list ( cell E3 in our worksheet example).
- Click on the “Data” tab, and then go to “Data Validation”;
Figure 9. Data Validation Button in Excel
- Inside the “Data Validation” menu box, under the “settings tab”, make sure that the “List” option is selected;
Figure 10. Data Validation Settings in Excel
- Inside the Source field, we will input the following Excel formula based on drop down list =INDIRECT(D3). Where, D3 represents the cell which contains our first conditional drop down list;
Figure 11. Data Validation Settings in Excel
- Click on “OK”
Now, whenever we make any selection in conditional drop down list 1, those options listed inside conditional drop down list 2 will update automatically;
Figure 12. Conditional Drop Down Lists in Excel