How to Create a Drop Down List in Excel (NO BS Guide!)

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:

  1. Using Data from Cells.
  2. Entering Data Manually.
  3. 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.

How to Create a Drop Down List in Excel

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

  1. Select a cell where you want to create the drop down list.
  2. Go to Data –> Data Tools –> Data Validation.
3. In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
  1. 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??

Excel drop down list

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

It returns a list of items from the range A2:A6.

Here is the actual syntax of the OFFSET function: =OFFSET(reference, rows, cols, [height], [width])

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.

You May Also Like the Following Excel Tutorials:

2 thoughts on “How to Create a Drop Down List in Excel (NO BS Guide!)”

  1. Fine way of describing, and good post to take data regarding my presentation subject matter, which i am going to convey in school.|

  2. When I originally left a comment I appear to have clicked the -Notify me when new comments are added- checkbox and from now on every time a comment is added I get 4 emails with the exact same comment. Perhaps there is a way you can remove me from that service? Thanks a lot!|

Leave a Reply

Your email address will not be published. Required fields are marked *