Create a Dependent Drop Down List in Excel

Excel gives you the tools you need to create custom drop down lists in your worksheets. You can make a simple list with two items, such as Yes and No, or a much more comprehensive list, such as the names of your entire staff. You can even take it one step further, creating a drop down list that displays items based on what the worksheet user selected in the previous cell.

The first thing you need to do is make the first list. Use the steps to make a named list, making sure your list entries are one-word items. The picture example shows a list of counties in the state of Arizona. Name this list. The options that appear in the second list will be dependent upon what the user chooses from this list.

click to enlarge

The second step is to create the first dependent list. Make the first dependent list for the first item in the first drop down list you created. This will be the drop down list available to users if they choose the first selection in your original drop down list. You must name this list exactly what your first item is in the original drop down.

For instance, in the picture example shown, the original list contained Arizona counties. The dependent lists will show cities for the county selected in the original list. The first county listed is Apache. Therefore, we named the first dependent list Apache. Create dependent lists for each of the items in your original list, naming each one the same as the corresponding item in the original drop down.

click to enlarge

The next step is to apply Data Validation. Enter headings into the first row, if you choose. Then select the cell or cells in which you want a drop down list showing the first list you created. In this example, cell A1 contains the heading “Arizona Counties.” Cells A2 through A10 contain the Data Validation drop down list “Counties.”

click to enlarge

click to enlarge

If you are using Excel 2003 or earlier, go to the Data menu and click on Validation. If you are using Excel 2007, go to the Data tab, select Data Validation and choose Data Validation from the drop down menu. The Data Validation dialog will appear. Choose List in the Allow box. Type =INDIRECT in the Source box. Directly after the INDIRECT function, enter the first cell with data in your original list. In the example spreadsheet, we entered =INDIRECT(A2), because A2 is the first cell with data that affects the dependent list.

click to enlarge

Click on one of the cells in your original list and choose an option from the drop down. Next, go to the corresponding cell in the dependent list. You should have a drop down available showing the items that are dependent on the first choice. Go back and try another. When you are satisfied with your dependent lists, save your worksheet and start showing it off.

click to enlarge

To learn more about making drop down lists in Excel, read Using Data Validation to Make a Dropdown List in Excel and Using a Data Validation Source List from a Separate Workbook.