How to Create a Drop Down List in Excel

Drop-down lists are a way to give the user the option to select from a pre-defined list. It's also called pull-down list. It can be used for users to fill out forms, or when creating interactive Excel dashboards.Drop Down List is very common in website and apps and also easy for users.

How to Create a Drop Down List in Excel

We will learn to create drop down list in three ways -

1. Using Data from Cells.
2. By Entering Data Manually.
3. Using the OFFSET formula.

1. Using Data from Cell.

Suppose you have a list of items as shown below. In which you want to create drop down list. Just follow some easy step to create Drop Down.

• Select the cell where you want to create drop down list
• Go to Data tab >>Data Tools >> Data Validation.

• In the Data Validation dialogue box, within the Settings tab, select the option to list in Validation criteria and select the source data.

By Entering Data Manually

In the above example, cell references are used in the source field but we can also add items directly by manually entering the source field.

• Select the cell where you want to create drop down list
• Go to Data tab >>Data Tools >> Data Validation.
• In the Data Validation dialogue box, within the Settings tab, select the option to list in Validation criteria
• Enter the data (Yes,No) in source field.

Using the OFFSET formula

We can also use formulas in the Source field to create drop down lists in Excel.

• In the Source field, enter the following Formula: =OFFSET($A$2,0,0,10)