How to Create an Excel Drop Down List (a beginner’s guide)

An Excel drop down list is a great way to give the user an option to select from a pre-defined list. It can be used while getting a user to fill a form, or while creating interactive Excel dashboards.

Drop down lists are quite common on websites/apps and are very intuitive for the user.

Watch Video – Creating an Excel Drop Down List

In this tutorial, you’ll learn how to create an Excel drop down list (and yes, it takes only a few seconds to do this).

How to Create an Excel Drop Down List

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.

#1 Creating an Excel Drop Down List Using Data from Cells

Let’s say you have a list of items as shown below:

How to Create an Excel Drop Down List - Data

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

  1. Select a cell where you want to create the drop down list (cell C2 in this example).
  2. Go to Data –> Data Tools –> Data Validation.How to Create an Excel Drop Down List - Data Validation
  3. 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.How to Create an Excel Drop Down List - Settings
  4. In the source field, enter =$A$2:$A$6, or simply click in the source field and select the cells using the mouse and click OK. This will insert a drop down list in cell C2.
    • Make sure that the In-cell dropdown option is checked (which is checked by default). If this option in unchecked, the cell does not show a drop down, however, you can manually enter the values in the list.How to Create an Excel Drop Down List - Demo

Note: If you want to create 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 absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).

#2 Creating an Excel Drop Down List by Entering Data Manually

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

For example, let’s say you want to show two options, Yes and No, in the drop down in a cell. Here is how you can directly enter it in the data validation source field:

  • Select a cell where you want to create the drop down list (cell C2 in this example).
  • Go to Data –> Data Tools –> Data Validation.How to Create an Excel Drop Down List - 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.How to Create an Excel Drop Down List - Settings
  • In the source field, enter Yes, No
    • Make sure that the In-cell dropdown option is checked.
  • Click OK.

This will create a drop-down list in the selected cell. All the items listed in the source field, separated by a comma, are listed in different lines in the drop down list.

How to Create an Excel Drop Down List - Manual Entry

Note: 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.

#3 Creating an Excel Drop Down List using Formulas

Apart from selecting from cells and entering data manually, you can also use a formula in the source field to create an Excel drop down list.

Any formula that returns a list of values can be used to create a drop down list in Excel.

For example, suppose you have the data set as shown below:

How to Create an Excel Drop Down List - Data

Here are the steps to create an Excel drop down list using the 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.How to Create an Excel Drop Down List - 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.How to Create an Excel Drop Down List - Settings
  • 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 will list all the fruit names.

How to Create an Excel Drop Down List - using FormulaNote: 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 absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).

How this formula Works??

In the above case, we used an OFFSET function to create the drop down list. It returns a list of items from the range A2:A6.

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

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

Now, when you use this formula, it returns an array that has the list of the five fruits in A2:A6. Note that if you enter the formula in a cell, select it and press F9, you would see that it returns an array of the fruit names.

How to Create a Drop Down List in Excel - offset F9

Creating a Dynamic Drop List in Excel (Using OFFSET)

The above technique of using a formula to create a drop down list can be extended to create a dynamic drop down list as well. If you use the OFFSET function, as shown above, even if you add more items to the list, the drop down would not update automatically. You will have to manually update it each time you change the list.

Here is a way to make it dynamic (and it’s nothing but a minor tweak in the formula):

  • 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,COUNTIF($A$2:$A$100,”<>”))
    • Make sure that the In-cell drop down option is checked.
  • Click OK.

In this formula, I have replaced the argument 5 with COUNTIF($A$2:$A$100,”<>”).

The COUNTIF function counts the non-blank cells in the range A2:A100. Hence, the OFFSET function adjusts itself to include all the non-blank cells.

How to Create a Drop Down List in Excel - dynamic offset

Note:

  • For this to work, there must NOT be any blank cells in between the cells that are filled.
  • 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 absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).

Copy Pasting Drop Down Lists in Excel

You can copy paste the cells with data validation to other cells, and it will copy the data validation as well.

For example, if you have a drop down list in cell C2, and you want to apply it to C3:C6 as well, simply copy the cell C2 and paste it in C3:C6. This will copy the drop down list and make it available in C3:C6 (along with the drop down, it will also copy the formatting).

If you only want to copy the drop down and not the formatting, here are the steps:

  • Copy the cell that has the drop down.
  • Select the cells where you want to copy the drop down.
  • Go to Home –> Paste –> Paste Special.How to Create an Excel Drop Down List - Paste Special
  • In the Paste Special dialogue box, select Validation in Paste options.How to Create an Excel Drop Down List - Paste Special Validation
  • Click OK.

This will only copy the drop down and not the formatting of the copied cell.

Caution while Working with Excel Drop Down List

You need to to be careful when you are working with drop down lists in Excel. When you copy a cell (that does not contain a drop down list) over a cell that contains a drop down list, the drop down list is lost.

The worst part of this is that Excel will not show any alert or prompt to let the user know that a drop down will be overwritten.

How to Select All Cells that have a Drop Down List in it

Sometimes, it is difficult to know which cells contain the drop down list in Excel. Hence, it makes sense to mark these cells by either giving it a distinct border or a background color.

Instead of manually checking all the cells for a drop down list, there is a quick way to select all the cells with a drop down list (or any data validation rule).

  • Go to Home –> Find & Select –> Go To Special.How to Create a Drop Down List in Excel - go to special
  • In the Go To Special dialogue box, select Data Validation
    • Data validation has two options: All and Same. All would select all the cells that have a data validation rule applied on it. Same would select only those cells that have the same data validation rule as that of the active cell.How to Create a Drop Down List in Excel - Goto DataVal
  • Click OK.

This would instantly select all the cells that have a drop down list in it.

Now you can simply give a border or a background color to the cells so that visually visible and you don’t accidently copy another cell on it.

Here is another technique by Jon Acampora you can use to always keep the drop down arrow icon visible. You can also see some ways to do this in this video by Mr. Excel.

Related Tutorials: