Excel: How to create simple and dependent drop-down lists

Drop-down lists in Microsoft Excel (and Word and Access) allow you to create a list of valid choices that you or others can select for a given field. This is especially useful for fields that require specific information; fields that have long or complex data that’s hard to spell; or fields where you want to control the responses.

Creating dependent drop-down lists (when combined with an INDIRECT function) is another benefit. This allows you to select a product category from the main menu drop-down list box (such as Beverages), then display all the related products from the submenu (dependent) drop-down list box (such as Apple Juice, Coffee, etc.). This works very well for ordering and inventory purposes because it divides all the products into manageable categories. This is how most wholesale and retail companies handle their product lines. In fact, companies from hospitals and insurance carriers to banks and more use drop-down lists, check boxes, combo lists, and/or radio buttons to minimize typing and user errors.

How to create a simple drop-down list

We’ve created a sample drop-down list so you can practice the steps, or feel free to use your own data.


Practice Excel drop-down lists using the data in this workbook.

If your spreadsheet database is large or contains numerous fields, we recommend that you place the list box items in a table on a separate spreadsheet, but in the same workbook. However, if your list is relatively short, you can type the items for your list, separated by commas, in the Source field of the Data Validation dialog window.

1. Open a new workbook and add a second spreadsheet tab (click the ‘+’ sign at the bottom of the screen on the tab bar).

2. Rename Spreadsheet 1 as “wks” for worksheet, and Spreadsheet 2 as “lists.”

3. Enter the names of 10 doctors (or other applicable items) in column A from A1 through A10.

4. Sort the list to your preference. If you plan to sort by last name, enter the last name first, then the first name and middle initial on your original list.

Related Stories


Por favor ingrese su comentario!
Por favor ingrese su nombre aquí