Tag: how to create the drop down list in excel

A Excel drop down list is one of the best way to give users multiple options to choose from per-defined list.

Pre-defined lists are not new, these are being used in many
websites for Form filling, many apps use them in user registration forms.

There are several ways through which you can create Drop
down list in excel and make your data more organized.

Let’s jump in and learn the best way through
which you can create drop down list in excel.

How to Create a Drop Down List in Excel

In this section, you will learn the exacts steps to create
an Excel drop-down list:

Using Data from Cells.

Entering Data Manually.

Using the OFFSET formula.

Using Data from Cells:

Creating a drop down list using data from cells, you must need
a list. Let’s assume that we have a list similar as given below.

Here
are the steps to create an Excel Drop Down List:

Select a cell where you
want to create the drop down list.

Go to Data –> Data Tools
–> Data Validation.

3. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.

As soon as you select your list, the source field appears as pop up.

4. In the source field, enter =$A$2:$A$6, or simply click in the Source field and select the cells and click OK. This will insert a drop down list in cell C2. 1. Make sure that the In-cell drop-down option is checked (which is checked by default). If this option in unchecked, the cell does not show a drop down, but you can manually enter the values in the list.

BONUS 1: If you want to create drop down lists in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are same (as $A$2) and not similar like (A2, or A$2, or $A2).

Entering Data Manually:

First we have tried to create drop down list by using cell as data source field. In this example we will directly add the data in source field as we don’t need any cell to pull the data.

This approach is good for small data set drop down list like, we want to give a drop down for “True or False” may be for “Yes or NO “. Let’s create a drop down list now.

Select a cell where you want to create the drop down list (cell C2 in this example).

Go to Data –> Data Tools –> Data Validation.

In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.

As soon as you select List, the source field appears.

· In the source field, enter True, False OR Yes, NO

Make sure that the In-cell drop-down option is checked.

Click OK.

This will create your drop down list in the cell that you have
selected.

The data that you have selected in the source field separated by comma will be shown in different lines in the drop down menu list.

Bonus 2:If you want to create drop down lists in multiple cells at one go, select all the cells where you want to create it and then follow the above steps.

Excel Drop Down List Using Excel Formulas:

In the above examples we have used data from cell and
entered manually but in this we will use the excel formulas to create a drop
down list.

I promise you that it won’t be too hard, so follow these
simple steps.

Suppose we have a small data set shown as below:

Here are the steps to create an Excel drop down list using the Excel OFFSET function:

Select
a cell where you want to create the drop down list (cell C2 in this example).

Go
to Data –> Data Tools –> Data Validation.

In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.

As soon as you select List, the source field appears.

In the Source field, enter the following formula: =OFFSET($A$2,0,0,5)

Make sure that the In-cell drop-down option is checked.

Click OK.

This
will create a drop-down list that lists all the fruit names (as shown below).

Bonus 3: If you want to create a drop-down list in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are same (as $A$2) and not like (A2, or A$2, or $A2).

Behind the scene-How OFFSET formula Works??

As you know that in above example, we used an Excel’s
predefined OFFSET function to create the drop down list.

It takes five elements, where we have specified the
reference as A2 (the starting point of the list). Rows/Cols are specified as 0 because
we don’t want to offset the reference cell. Height is specified as 5 as we have
taken five elements in our list.

Now, when we use this formula, it returns an array that has the list of the five fruits in A2:A6.

Note: You can check the array then just enter the formula in
a cell, select it and press F9, you would see that it returns an array of the
fruit names that you have taken in your list.

About Deepak Jha

I am a Marketer & passionate Excel practitioner. I am using MS excel from past 6 years in my daily routine work, I have tried many things to automate my excel work so i thought why not I help others to automate their work through excel so I started You Learn Excel blog. Just to make things easier for others.