A Drop down list is helpful in giving users a pre-defined list of options to select from.

Drop down lists are used in many website or app programs. It can be used to fill forms, surveys or in creating interactive Excel dashboards.

In this tutorial, you’ll learn the steps to follow in creating a drop down list in Excel and what it can be used for. The steps will take only a few seconds to complete.

This Tutorial will cover the following topics:

#1. How to Create a Drop-down List in Excel

a. Making use of Data from the Cells

b. Imputing the Data Manually

c. Making use of Excel’s OFFSET Function

#2. How to Create a Dynamic Drop-down List in Excel Using the Offset Function

#3. How to Copy & Paste a Drop-down List in Excel

#4. The Important Risks to Avoid When Working with Drop-down List in Excel

#5. How to Select All The Cells With a Drop-down List in a Worksheet

#6. How to Create a Dependent / Conditional Drop-down List in Excel

#1. How To Create a Drop Down List In Excel

In this section, we will go through the steps to create a drop down list in Excel.

There are three ways we can do this. They are:

  1. Making use of data from the cells
  2. Imputing the data manually
  3. Making use of Excel’s OFFSET function.

#a. Making Use of Data from The Cells

You can create a drop down list in Excel using the data you have in your worksheet.

Let’s say you have a list of countries as shown below and you want to create a drop down list for the same items listed.

Here are the steps to be followed.

             #1. Select a cell location you want to insert the drop down list.

             #2. Click on ‘Data’ tab

    #3. Click on Data Tools -> Data Validation

            #4. In the Data Validation dialog box, click on ‘Settings’ and select ‘List’ from the drop-down menu

Immediately you select ‘List‘, the ‘Source field’ box will appear.

    #5. In the Source field, enter the formula:

          =$A$2:$A$9

         Or

you can use your mouse to select the cells after you click in the source field.

        #6. Confirm that the ‘In-cell dropdown’ box is checked.

It is usually checked by default. If it is not checked, the cell will not show a drop down.

       #7. Click Ok.

This will insert the list in the chosen cell location as shown above. In this case, cell C1 is chosen.

It is also possible to create drop down lists in multiple cells at the same time at one go.

To do this, you will select all the cells you want the drop-down to be located and follow the same steps above. Just make sure that you include the dollar sign to the cell references to make it absolute like this: ($B$1) and not relative like here: (B2 or $B2 or B$2).

 #b. Imputing The Data Manually

The values in the list can also be entered manually.

In the example above, the cell reference was entered in the Source Field by selecting the cell range of the listed items.

However, you may decide to add the items directly by imputing the items manually in the source field.

So if for example, we want to display a binary option of ‘True and False’ in the drop-down list, this is how we can do it.

         #1. Select the cell you want to insert the drop down list. In this case, we use cell C14.

  #2. Click on ‘Data’ tab -> Data Tools -> Data Validation

         #3. In the Data Validation dialog box, click on ‘Settings’ tab and select ‘List’ from the ‘Validation criteria’ drop-down menu.

Again, immediately the source field will appear.

          #4. In the Source field, enter the formula True, False.

          #5. Confirm that the ‘In-cell dropdown’ is checked.

            #6. Click Ok.

Now, we have a drop-down list in the selected cell with all the items listed in the source field. The items are separated by a comma which allows the items to be listed in different lines.

    #c. Making Use of Excel’s OFFSET Function

Another way to create a drop-down list in Excel is by using a formula in source field.

The OFFSET function in Excel allows you to create a drop-down list. It returns a list of items from a selected cell range. In this example, the selected range is A2:A9).

The OFFSET function uses the syntax below:

         =OFFSET(reference, rows, cols,[height], [width])

The OFFSET function takes five arguments, where ‘Reference’ is the starting point of the cell range of the list. The ‘Rows and Columns’ are specified as 0 and the Height specified is the number of elements in the list.

So for example, if you have a list of countries, here is how to create a drop down list in Excel like the one above, using the OFFSET function.

     #1. Select the cell you want to insert the drop down list. In this example, we will use cell C1.

     #2. Click on Data tab -> Data Tools -> Data Validation

     #3. In the Data Validation dialog box, click on ‘Settings’ tab and select ‘List’ from the ‘Validation criteria’ drop-down menu.

And again, the source field will appear immediately.

     #4. In the Source field, enter the formula:

               =OFFSET($A$2,0,0,8)

     #5. Confirm that the ‘In-cell dropdown’ box is checked.

     #6. Click Ok.

Now, this formula returns an array of list of the eight countries in cells A2:A9.

If you want to see the array of countries listed, you can enter the formula in a cell, select it and press F9. It will return an array of the countries.

#2. How To Create a Dynamic Drop down List in Excel

In this section, look at how a dynamic drop down list can be created. And this can be done using again, Excel’s OFFSET function.

By using the OFFSET formula, you can also extend this technique to create a more dynamic drop down list.

If you study the example above, you will notice that the OFFSET formula used in the above example does not update the list when any additional item is added to the list. The list will need to be updated manually each time a new item is added or removed from the list. 

But by making it dynamic, it will update any item added to the list automatically.

To create a drop down list that is dynamic, you will need to tweak the formula a bit. And here is how to do that.

     #1. Select the cell you want to insert the drop down list. In this example, we will use cell C1.

     #2. Click on ‘Data’ tab -> Data Tools -> Data Validation

     #3. In the Data Validation dialog box, click on ‘Settings’ tab and select ‘List’ from the ‘Validation criteria’ menu.

As usual, the source field will appear immediately.

     #4. In the Source field, enter the formula:

        =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,”<>”))

    

#5. Confirm that the ‘In-cell drop down’ is checked.

     #6. Click Ok.

What we have here is a drop down list of all the items in column A. If you add or remove any item from the list in column A, it will be automatically added or removed from the list as shown below:

Here, Saudi Arabia, China, Sweden and Kenya have been added to the list and updated in the drop down list as well.

And here, all four items including Australia have been removed.

In this formula, the ‘Height’ argument of 8 has been replaced with COUNTIF($A$2:$A$100,”<>”).

The COUNTIF formula will count all non-blank cells in the range A2:A100. This way, the formula will adjust itself to count all non-blank cells in that range.

Also note that:

  • There must be NO blank cells in between the filled cells. Otherwise it won’t work.

#3. How To Copy and Paste Drop Down Lists in Excel

It is also possible to copy and paste cells that have data validation to another cell location. When you copy it, it copies the data validation along with it.

For instance, if you have a drop-down list in cell D1 and you want to apply it to other cell locations, say D2:D6, you can just copy cell D1 and paste it in cells D2:D6.

This will copy the list and display it in D2:D6, including the drop down formatting.

However, if you want to copy only the list without the formatting, then these are the steps you should follow:

     #1. Copy the cell with the drop down (C1)

     #2. Select the cells you want to copy the drop down to.

     #3. Go to ‘Home’ tab -> Paste -> Paste Special

     #4. In Paste Special dialog box, select ‘Validation’ under Paste options

     #5. Click Ok.

#4. The Risks to Avoid When Working With Drop-down Lists in Excel

When working with drop down lists in Excel, you need to take extra care so as not to mess the list you have created.

If you mistakenly copy a cell that does not have a drop-down list and paste it over a cell that has a drop down list, you end up losing the drop down list.

More so, Excel does not give any alert or prompt to notify the user that the drop-down has been overwritten.

#5. How to Select All the Cells that have a Drop-down List in Excel

Sometimes, you may have a worksheet that contains cells with a drop down list. It is hard to tell which cells have a drop down list from just looking at it. So, it makes a lot of sense to mark cells that contain drop-down list with a background or border colour.

What does not make sense however, is having to check and identify these cells manually.

Thank goodness you do not need to go through all the hassle to do it manually since there is a quick and easy trick to do this.

To select all the cells with a drop-down list or any data validation rule:

     #1. From the ‘Home’ tab, click on ‘Find & Select’

     #2. In the drop down menu, select ‘Go To Special’

     #3. In Go To Special dialog box, select ‘Data Validation

     #4. Select ‘All’

Note that:

  • There are two options for ‘Data Validation’: All and Same.

Select ‘All’ if you want to select all the cells that a data validation rule has been applied on it and select ‘Same’ to select only the cells with the same data validation as the active cell.

You can simply format the cells to be easily visible by adding a background colour or giving it a border line. This way, you won’t mistakenly copy another cell on it.

#6. How To Create a Conditional/Dependent Drop down List in Excel

If you need to create two or more drop down lists where the items displayed in the second drop down will be dependent on what the user has selected in the first drop down, it is also possible in Excel.

Here is an example of a conditional/dependent drop down list:

From the example above, the items listed in ‘Drop-down 2’ are dependent on what is selected in ‘Drop-down 1’.

To create this, these are the steps to follow:

     #1. Select the cell location for the first(main) drop-down list. Cell D2 in this case.

     #2. Click on ‘Data’ tab -> Data Tools -> Data Validation

     #3. In the Data Validation dialog box, click on ‘Settings’ tab and select ‘List’ from the ‘Validation criteria’ drop-down menu.

Immediately, the source field box will appear.

     #4. In the Source field, specify the cell range for the first drop down list:

         =$A$1:$B$1

     #5. Click Ok.

This creates the first drop down, Drop-down 1.

     #6. Select the entire dataset. In this example, I will select (A1:B6)

     #7. Go to Formulas, select ‘Defined Names -> Create from Selection (You can use keyboard shortcut: Control + Shift + F3)

     #8. In ‘Create Names From Selection’ dialog box, check ‘Top row’ option and uncheck all others.

This will create the two named ranges (‘Food’ and ‘Drink’). The Food name range refers to all the food in the list while Drink name range refers to all the drinks in the list.

     #9. Click Ok

     #10. Then select the cell location for the Conditional/Dependent drop-down list. In this case, it is cell E2.

     #11. Click on ‘Data’ tab -> Data Tools -> Data Validation

     #12. In the Data Validation dialog box, click on ‘Settings’ tab and select ‘List’ from the ‘Validation criteria’ drop-down menu.

The source field will appear.

     #13. In the Source field, enter the formula:

        =INDIRECT(D2)

D2 here is the cell that contains the first and main drop-down.

     #14. Click Ok.

And that’s it.

Now you will see that the options listed in Drop-down List 2 changes automatically as you make a selection in Drop Down 1.

If you select ‘Food’, the list in Drop Down 2 displays a list of food and when you select ‘Drink’, Drop Down 2 list displays a list of drinks.

If you are asking: How does this work?

The formula =INDIRECT(D2) is being referred by the conditional drop-down list in cell E2. What this means is that the INDIRECT function allows the drop-down list in E2 refer to the named range ‘Food’ when Food is selected, and thereby lists all the items in that category.

Important Notes to Consider with Conditional Drop Down Lists in Excel

Note 1:

  • If you have made a selection, and then you go back to make changes in the main drop down, the change will not be effected in the dependent drop-down. You will therefore have a wrong entry.

For example, if you select United Kingdom as country in drop-down 1 and then select London as city in drop-down 2, and then you go back to change the country to United Arab Emirate (UAE), the city will not change. It will still remain as London. Hence a wrong information because London is not a city in UAE.

Note 2:

  • The formula will change if the main category, that is, Drop Down 1 has more than one word. For example, instead of ‘Food’, you have ‘Main Food’. The formula will change from =INDIRECT(D2) to =INDIRECT(SUBSTITUTE(D2,” “,”_”)).

This is because spacing in named ranges are not allowed in Excel.

So when more than one word is used to create a named range, Excel will automatically insert an underscore in between the words. So if you have ‘Main Food’, Excel will automatically name it as ‘Main_Food’ in a named range.

The SUBSTITUTE function used within the INDIRECT function converts the spaces to underscores.

Did you find this tutorial useful? Kindly share in the comments section!