How to Create a Drop Down List in Excel

Drop down lists in Excel allow you to create a list of options that users can select from a drop down menu. This is useful for restricting inputs, making data entry simpler and faster, and ensuring consistency.

Why Use Drop Down Lists

There are several benefits to using drop down lists in Excel:

Restrict Choices

Drop down lists only allow users to select from the options you provide. This prevents incorrect or inconsistent data from being entered.

Save Time

Entering data via a drop down is much faster than typing it out each time. Users simply click and select.

Ensure Consistency

Every user is choosing from the same list of options. This results in consistent inputs across your workbook.

Display Related Data

You can configure drop downs to display related data from elsewhere in the workbook based on the selected option.

Improve Data Validation

It’s easy to validate data entered via drop down. You know it matches one of the predefined choices.

How to Create a Basic Drop Down List

Creating a basic drop down list in Excelis easy and only takes a few steps:

1. Enter the list of options you want to choose from. Enter one option per cell. This is your drop down source data.
2. Select the source data cells. Go to the Data tab and click Data Validation.
3. In the Allow box, select List. In the Source box, enter the cell reference for your source data.
4. Click OK. A drop down arrow will appear in the cell when you click on it.
5. Users can now select any option from the drop down list you created.

This creates a basic drop down without any additional configuration. The list options are fixed and don’t change based on other data.

Link Drop Down List to Another Cell

You can link your drop down options to the value in another cell. This dynamically updates the list based on that cell’s value.

To do this:

1. Create separate source data lists on different sheets, one per category.
2. Name the sheets after the categories.
3. On your main sheet, select the cell to contain the drop down.
4. Go to Data Validation and select List.
5. Under Source, enter: =INDIRECT(A1)

Discover: How to get-rid of keloids

Where A1 contains the category name.

6. When you change the category in A1, the drop down updates to show the options on the relevant sheet.

This lets you maintain category-specific lists that users can easily access via the drop down.

Configure Dependent Drop Down Lists

Dependent drop downs filter options in one list based on selections in another.

To set this up:

1. Create your source data with categories in one column and sub-options in another.
2. On your sheet, create a drop down linked to the category column.
3. Next to it, create another drop down linked to the sub-option column.
4. Under Data Validation, set the sub-option drop down to only allow values Where: =$A$1=$A2

Where A1 is the category drop down cell and A2 is the category list cell.

5. Changing the category drop down will filter the sub-options to match.

Dependent drop downs are great for nested selections like regions, countries, cities.

Populating Cells Based on Drop Down Selection

You can configure your drop downs to populate related cells based on the selected option:

1. Create a lookup table with drop down options, output values, and any related data.
2. Set up your drop down list linked to the options.
3. Use VLOOKUP to return values from the lookup table based on the selection. =VLOOKUP(A2,LookupTable,2,FALSE)
4. The cell will display the looked up data for the chosen drop down option.

This lets you auto-populate things like prices, codes, addresses etc. based on the drop down.

Data Validation for Mandatory Selection

You can make selecting from the drop down mandatory with Data Validation:

1. With your drop down cell selected, go to Data > Data Validation.
2. Set Allow to List and enter your source data.
3. Check the Box for In-cell dropdown.
4. Set an Input Message saying selection is required.
5. Set an Error Alert if invalid data is entered.
6. Users must choose from the drop down list.

This prevents blank cells or invalid selections when a choice is required.

Drop down lists are a great way to simplify data entry and ensure data consistency in Excel. They take just minutes to set up but can save hours of work validating inputs or cleaning up messy data.