Excel drop down list: create, edit, remove and more advanced operations

A drop-down list is similar to list box that allows users to pick one value from a selection list. This tutorial is going to demonstrate the basic operations for drop-down list: create, edit and remove drop down list in excel. Apart from that, this tutorial provides advanced operations for drop-down list to enhance its functionality to solve more Excel issues.

---

Create simple drop down list

For using a drop down list, you need to learn how to create it firstly. This section provides 6 ways to help you create a drop-down list in Excel.

Create drop down list from a range of cells

Here demonstrate the steps to create drop down list from a cell range in Excel. Please do as follows

1. Select a cell range for locating the drop down list.

Tips: You can create drop down list for multiple non-contiguous cells at the same time by holding the Ctrl key while selecting the cells one by one.

2. Click Data > Data Validation > Data Validation.

3. In the Data Validation dialog box, under the Settings tab, please configure as follows.

Notes:

Now the drop down list is created. When clicking the drop down list cell, an arrow will display next to it, click the arrow to expand the list, and then you can choose an item from it.

Create dynamic drop down list from table

You can convert your data range to an Excel table and then create a dynamic drop down list based on the table range.

1. Select the original data range, and then press the Ctrl + T keys.

2. Click OK in the popping up Create Table dialog box. Then the data range is converted to table.

3. Select a cell range for putting the drop-down list, and then click Data > Data Validation > Data Validation.

4. In the Data Validation dialog box, you need to:

Then dynamic drop down lists are created. When adding or removing data from the table range, values in drop-down list will be updated automatically.

Create dynamic drop down list with formulas

Apart from creating dynamic drop down list from table range, you can also use a formula to create a dynamic drop down list in Excel.

1. Select the cells where to output the drop down lists.

2. Click Data > Data Validation > Data Validation.

3. In the Data Validation dialog box, please configure as follows.

Then dynamic drop down lists are created. When adding or removing data from the specific range, values in drop-down lists will be updated automatically.

Create drop down list from named range

You can also create drop down list from a named range in Excel.

1. Firstly, create a named range. Select the cell range you will create named range based on, and then type in the range name into the Name box, and press Enter key.

2. Click Data > Data Validation > Data Validation.

3. In the Data Validation dialog box, please configure as follows.

Now the drop down list using data from a named range is created.

Create drop down list from another workbook

Supposing there is a workbook named “SourceData”, and you want to create drop down list in another workbook based on data in this “SourceData” workbook, please do as follows.

1. Open the “SourceData” workbook. In this workbook, select the data you will create drop down list based on, type a range name into the Name box, and then press the Enter key.

Here I name the range as City.

2. Open the worksheet you will insert drop down list. Click Formulas > Define Name.

3. In the New Name dialog box, you need to create a named range based on the range name you created in workbook “SourceData”, please configure as follows.

Notes:

4. Open the workbook you will insert drop down list, select the cells for the drop down list, and then click Data > Data Validation > Data Validation.

5. In the Data Validation dialog box, please configure as follows.

Now the drop down lists have been inserted in selected range. And the drop-down values are from another workbook.

Easily create drop down list with an amazing tool

Here highly recommend the Create simple drop-down list utility of Kutools for Excel. With this feature, you can easily create drop-down list with specific cell values or create drop-down list with custom lists preset in Excel.

1. Select the cells you want to insert drop-down list, and then click Kutools > Drop-down List > Create simple drop-down list.

2. In the Create simple drop down list dialog box, please configure as follows.

Note: If you want to create a drop-down list based on custom list preset in Excel, please select the Custom Lists option in the Source section, choose a custom list in the Custom Lists box, and then click the OK button.

Now the drop down lists have been inserted in selected range.

---

Edit drop down list

If you want to edit drop down list, methods in this section can do you a favor.

Edit a drop down list based on a cell range

For editing a drop down list based on a cell range, please do as follows.

1. Select the cells containing drop down list you want to edit, and then click Data > Data Validation > Data Validation.

2. In the Data Validation dialog box, change the cell references in the Source box and then click the OK button.

Edit a drop down list based on a named range

Supposing you add or delete values in the named range, and the drop down list is created based on this named range. For appearing the updated values in drop down lists, please do as follows.

1. Click Formulas > Name Manager.

Tips: You can open the Name Manager window by pressing the Ctrl + F3 keys.

2. In the Name Manager window, you need to configure as follows:

3. Then a Microsoft Excel dialog box pops up, click the Yes button to save the changes.

Then drop down lists based on this named range are updated.

---

Remove drop down list

This section is talking about removing drop down list in Excel.

Remove drop down list with Excel build-in

Excel provides a build-in feature to help removing drop down list from worksheet. Please do as follows.

1. Select the cell range containing the drop down list you want to remove.

2. Click Data > Data Validation > Data Validation.

3. In the Data Validation dialog box, click the Clear All button, and then click OK to save the changes.

Now drop down lists are removed from the selected range.

Easily remove drop down lists with an amazing tool

Kutools for Excel provides a handy tool - Clear Data Validation Restrictions to help easily remove drop down list from one or multiple selected ranges at once. Please do as follows.

1. Select the cell range containing the drop down list you want to remove.

2. Click Kutools > Prevent Typing > Clear Data Validation Restrictions. See screenshot:

3. Then a Kutools for Excel dialog box pops up to ask you if clear the drop down list, please click the OK button.

Then drop down lists in this selected range are removed immediately.

---

Add color to drop down list

In some cases, you may need to make a drop down list that is color-coded in order to distinguish the data in the drop down list cells at a glance. This section provides two methods to help you solve the problem in details.

Add color to drop down list with Conditional Formatting

You can create conditional rules to the cell containing the drop down list to make it color-coded. Please do as follows.

1. Select the cells containing the drop down list that you want to make it color-coded.

2. Click Home > Conditional Formatting > Manage Rules.

3. In the Conditional Formatting Rues Manager dialog box, click the New Rule button.

4. In the New Formatting Rule dialog box, please configure as follows.

5. When it returns to the Conditional Formatting Rules Manager dialog box, repeat the above step 3 and 4 to specify colors for other drop down items. After finish specifying colors, click the OK to save the changes.

From now on, when selecting item from the drop down list, the cell will be highlighted with specified background color based on the selected text.

Easily add color to drop down list with an amazing tool

Here introduce the Colored Drop-down List feature of Kutools for Excel to help you easily add color to drop down list in Excel.

1. Select the cells containing the drop down list that you want to add color.

2. Click Kutools > Drop-down List > Colored Drop-down List.

3. In the Colored Drop-down list dialog box, please do as follows.

Tips: If you want to highlight rows based on drop down list selection, please choose the Row of data range option in the Apply to section, and then select the rows you will highlight in the Highlight rows box.

Now the drop down lists are color-coded as the below screenshots shown.

Highlight cells based on drop-down list selection

Highlight rows based on drop-down list selection

---

Create dependent drop down list in Excel or google sheet

A dependent drop down list helps to display choices depending on the value seletced in the first drop down list. If you need to create a dependent (cascarding) drop down list in Excel worksheet or in google sheet, methods in this section can do you a favor.

Create a dependent drop down list in Excel worksheet

The below demo displays the dependent drop down list in Excel worksheet.

Please click How To Create Dependent Cascading Drop Down List In Excel? for a step-by-step guide tutorial.

Create a dependent drop down list in google sheet

If you want to create a dependent drop down list in google sheet, please see How To Create A Dependent Drop Down List In Google Sheet?

---

Create searchable drop down lists

For the drop down lists containing long list of items in a worksheet, it’s not easy for you to pick up a certain item from the list. If you remember the initial characters or several consecutive characters of an item, you can do the search feature in a drop down list to easily filter it. This section is going to demonstrate how to create a searchable drop down list in Excel.

Supposing the source data you want to create drop down list based on locates in column A of Sheet1 as the below screenshot shown. Please do as follows to create a searchable drop down list in Excel with these data.

1. Firstly, create a helper column beside the source data list with an array formula.

In this case, I select cell B2, enter the below formula into it and then press the Ctrl + Shift + Enter keys to get the first result.

=IFERROR(INDEX($A$2:$A$50,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$50)>0,$A$2:$A$50,""),$A$2:$A$50,0),""),ROW(A1))),"")

Select the first result cell, and then drag its Fill Handle all the way down until it reaches the end of the list.

Note: In this array formula, $A$2:$A$50 is the source data range you will create drop down list based on. Please change it based on your data range.

2. Click Formulas > Define Name.

3. In the Edit Name dialog box, please configure as follows.

Now you need to create the drop down list based on the named range. In this case, I will create searchable drop down list in Sheet2.

4. Open the Sheet2, select the range of cells for the drop down list, and then click Data > Data Validation > Data Validation.

5. In the Data Validation dialog box, please do as follows.

6. Right click the sheet tab (Sheet2) and select View Code from the right-clicking menu.

7. In the opening Microsoft Visual Basic for Applications window, copy the below VBA code into the Code editor.

VBA code: create searchable drop down list in Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

8. Press the Alt + Q keys to close the Microsoft Visual Basic for Appliations window.

Now the searchable drop down lists are created. If you want to pick up an item, just enter one or several consecutive characters of this item into the drop down cell, click the drop down arrow, and then the item based on the entered content are listed in the drop down list. See screenshot:

Note: This method is case-sensitive.

---

Create drop down list but show different values

Supposing you have created a drop down list, when selecting item from it, you want something else display in the cell. As the below demo shown, you have created drop down list based on the country name list, when selecting country name from the drop down, you want to display the abbreviation of the selected country name in the drop down cell. This section provides VBA method to help you solve the problem.

1. On the right side of the source data (the country name column), create a new column containing the abbriviation of the country names that you want to display in the drop down cell.

2. Select both the country name list and the abbreviation list, type a name in the Name box and then press the Enter key.

3. Select the cells for the drop down list (here I select D2:D8), and then click Data > Data Validation > Data Validation.

4. In the Data Validation dialog box, please configure as follows.

5. After creating the drop down list, then right click the sheet tab and then select View Code from the right-clicking menu.

6. In the opening Microsoft Visual Basic for Applications window, copy the below VBA code into the Code editor.

VBA code: Show different values in drop down list

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20201027
    selectedNa = Target.Value
    If Target.Column = 4 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

Notes:

7. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

From now on, when selecting a certain country name from the drop down list, the corresponding abbreation of the selected country name will be displayed in the cell.

---

Create a drop down list with checkboxes

Many Excel users tend to create drop down list with multiple checkboxes so that they can select multiple items from the list by just ticking the checkboxes.

As the below demo shown, when clicking the cell containing drop down list, a listbox appears. In the listbox, there is a checkbox before each item. You can tick the checkboxes to display the corresponding items in the cell.

If you want to create a drop down list with checkboxes in Excel, please see How To Create Drop Down List With Multiple Checkboxes In Excel?.

---

Add autocomplete to drop down list

If you have a data validation drop down list with large items, you need to scroll up and down in the list for finding the proper one, or type the whole word into the list box directly. If drop down list can be auto complete when typing the first letter in it, everything will become easier.

For making drop down list autocomplete in a worksheet in Excel, please see How To Autocomplete When Typing In Excel Drop Down List?.

---

Filter data based on drop down list selection

This section will demonstrate how to apply formulas to create a drop down list filter in order to extract data based on the selection from the drop down list.

1. Firstly you need to create a drop down list with the specific values you will extract data based on.

Tips: Please follow the above steps to create a drop down list in Excel.

Create a drop down list with a unique list of items

If there are duplicates in your range, and you don’t want to create a drop down list with repetition of an item, you can create a unique list of items as follows.

1) Copy the cells you will create drop down list based on with Ctrl + C keys, and then paste them to a new range.

2) Select the cells in the new range, click Data > Remove Duplicates.

3) In the Remove Duplicates dialog box, click the OK button.

4) Then a Microsoft Excel pops up to tell you how many duplicates are removed, click OK.

Now you get the unique list of items, you can create drop down list based on this unique list now.

2. Then you need to create three helper columns as follows.

3. Create a range based on the original data range to output the extracted data with the below formulas.

Notes:

Now a drop down list filter is created, you can easily extract data from the original data range based on the drop down list selection.

---

Select multiple items from drop down list

By default, the drop down list allows users to select only one item per time in a cell. When reselecting item in a drop down list, the previously selected item will be overwritted. However, if you are asked to select multiple items from a drop down list and display all of them in the drop down cell as the below demo shown, how can you do?

For selecting multiple items from drop down list in Excel, please see How To Create Drop Down List With Multiple Selections Or Values In Excel?. This tutorial provides two methods in details to help you solve the problem.

---

Set default (preselected) value for drop down list

By default, a drop down list cell displays as blank, the drop down arrow only appears when you click on the cell. How to figure out which cells contain drop down lists in a worksheet at a glance?

This section will demonstrate how to set default (preselected) value for drop down list in Excel. Please do as follows.

Before applying the below two methods, you need to create drop down list and do some configurations as follows.

1. Select the cells for the drop down list, click Data > Data Validation > Data Validation.

Tips: If you have already created drop down list, please select the cells containing the drop down list, and then click Data > Data Validation > Data Validation.

2. In the Data Validation dialog box, please configure as follows.

After creating the drop down list, please apply one of the below methods to set default value for them.

Set default value for drop down list with formula

You can apply the below formula to set default value for the drop down list you created as the above steps shown.

1. Select the drop down list cell, enter the below formula into it and then press the Enter key to display the default value. If the drop down list cells are consecutive, you can drag the Fill Handle of the result cell to apply the formula to other cells.

=IF(C2="", "--Choose item from the list--")

Notes:

Set default value for all drop down lists in a worksheet at once with VBA code

Supposing there are lots of drop down lists locating in different ranges in your worksheet, for setting default value for all of them, you need to apply the formula repeatedly. That’s time-consuming. This section provides a useful VBA code for you to set default value for all drop down lists in a worksheet at once.

1. Open the worksheet containing the drop down lists you want to set default value, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then paste the below VBA code into the Code window.

VBA code: Set default value for all drop down lists in a worksheet at once

Sub SetDropDownListToDefaultValue()
'Updated by Extendoffice 20201026
Dim xWs As Worksheet
Dim xRg, xFRg As Range
Dim xET: xET = Null
Dim xStr As String
xStr = "- Choose from the list -"
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange.Cells
    On Error Resume Next
    For Each xFRg In xRg
    xET = Null
    xET = xFRg.Validation.Type
    If Not IsNull(xET) Then
        If xFRg.Validation.Type = 3 Then
            xFRg.Value = "'" & xStr
        End If
    End If
    Next
End Sub

Notes: In the above code, - Choose from the list - is the default value to display in the drop down list cell. You can also change the default value based on your need.

3. Press the F5 key, then a Macros dialog box pops up, make sure the DropDownListToDefault is selected in the Macro Name box, and then click the Run button to run the code.

Then the specified default value is populated into drop down list cells immediately.

---

Increase drop down list font size

Normally, drop down list has a fixed font size, if the font size is so small to read, you can try the below VBA method to enlarge it.

1. Open the worksheet containing the drop down lists you want to enlarge the font size, right click the sheet tab and then select View Code from the right-clicking menu.

2. In the Microsoft Visual Basic for Applications window, copy the below VBA code into the Code editor.

VBA code: Enlarge the font size of drop down lists in a worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20201027
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

Note: here xZoom = 130 in the code means that you will enlarge the font size of all drop down lists in current worksheet to 130. You can change it as you need.

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

From now on, when clicking on the drop down cell, the zoom level of current worksheet will be enlarged, click the drop down arrow, you can see the font size of all drop down items are also enlarged.

After selecting item from the drop down list, you can click on any cells outside the drop down cell to return to the original zoom level.