Excel 下拉列表：创建、编辑、删除及更多高级操作

下拉列表类似于列表框，允许用户从选择列表中选择一个值。本教程将演示下拉列表的基本操作：在 Excel 中创建、编辑和删除下拉列表。此外，本教程还提供了下拉列表的高级操作，以增强其功能，解决更多 Excel 问题。

创建简单下拉列表

要使用下拉列表，首先需要学习如何创建。本节提供了6 种方法来帮助您在 Excel 中创建下拉列表。

从单元格区域创建下拉列表

在这里，我们将演示如何从 Excel 中的单元格区域创建下拉列表。请按以下步骤操作

1.选择一个单元格区域以放置下拉列表。

提示：您可以通过按住 "Ctrl" 键，同时逐个选择单元格，为多个不连续的单元格创建下拉列表。

2. 点击 "数据" > "数据验证" > "数据验证"。

3. 在 "数据验证" 对话框中，点击 "设置"选项卡，请按以下步骤配置。

备注：

现在下拉列表已创建。点击下拉列表单元格时，旁边会显示一个箭头，点击箭头展开列表，然后您可以从中选择一个项目。

从表格创建动态下拉列表

您可以将数据区域转换为 Excel 表格，然后基于表格区域创建动态下拉列表。

1.选择原始数据区域，然后按 "Ctrl" + "T" 键。

2. 在弹出的 "创建表格" 对话框中点击 "确定"。然后数据区域被转换为表格。

3.选择一个单元格区域以放置下拉列表，然后点击 "数据" > "数据验证" > "数据验证"。

4. 在 "数据验证" 对话框中，您需要：

然后，动态下拉列表被创建。当从表格区域添加或删除数据时，下拉列表中的值将自动更新。

使用公式创建动态下拉列表

除了从表格区域创建动态下拉列表外，您还可以使用公式在 Excel 中创建动态下拉列表。

1.选择要输出下拉列表的单元格。

2. 点击 "数据" > "数据验证" > "数据验证"。

3. 在 "数据验证" 对话框中，请按以下步骤配置。

=OFFSET($A$13,0,0,COUNTA($A$13:$A$24),1)

然后，动态下拉列表被创建。当从特定区域添加或删除数据时，下拉列表中的值将自动更新。

从命名区域创建下拉列表

您还可以从 Excel 中的命名区域创建下拉列表。

1. 首先，创建一个命名区域。选择您将基于其创建命名区域的单元格区域，然后在 "名称" 框中输入区域名称，并按 "Enter" 键。

2. 点击 "数据" > "数据验证" > "数据验证"。

3. 在 "数据验证" 对话框中，请按以下步骤配置。

现在使用命名区域数据的下拉列表已创建。

从另一个工作簿创建下拉列表

假设有一个名为“SourceData”的工作簿，您想在另一个工作簿中基于此“SourceData”工作簿中的数据创建下拉列表，请按以下步骤操作。

1. 打开“SourceData”工作簿。在此工作簿中，选择您将基于其创建下拉列表的数据，在 "名称" 框中输入一个区域名称，然后按 "Enter" 键。

这里我将区域命名为 City。

2. 打开您将插入下拉列表的工作表。点击 "公式" > "定义名称"。

3. 在 "新建名称" 对话框中，您需要基于在“SourceData”工作簿中创建的区域名称创建一个命名区域，请按以下步骤配置。

=SourceData.xlsx!City

备注：

4. 打开您将插入下拉列表的工作簿，选择下拉列表的单元格，然后点击 "数据" > "数据验证" > "数据验证"。

5. 在 "数据验证" 对话框中，请按以下步骤配置。

现在下拉列表已插入到选择的区域中。下拉值来自另一个工作簿。

轻松使用强大工具创建下拉列表

在这里，我强烈推荐 "Kutools for Excel" 的 "创建简单下拉列表" 功能。使用此功能，您可以轻松创建具有特定单元格值的下拉列表或创建具有 Excel 中预设自定义列表的下拉列表。

1.选择要插入下拉列表的单元格，然后点击 "Kutools" > "下拉列表" > "创建简单下拉列表"。

2. 在 "创建简单下拉列表" 对话框中，请按以下步骤配置。

备注：如果您想基于 Excel 中预设的自定义列表创建下拉列表，请在 "来源" 部分选择 "自定义列表"选项，在 "自定义列表" 框中选择一个自定义列表，然后点击 "确定" 按钮。

现在下拉列表已插入到选择的区域中。

编辑下拉列表

如果您想编辑下拉列表，本节中的方法可以帮助您。

基于单元格区域编辑下拉列表

要基于单元格区域编辑下拉列表，请按以下步骤操作。

1.选择包含您要编辑的下拉列表的单元格，然后点击 "数据" > "数据验证" > "数据验证"。

2. 在 "数据验证" 对话框中，更改 "来源" 框中的单元格引用，然后点击 "确定" 按钮。

基于命名区域编辑下拉列表

假设您在命名区域中添加或删除值，并且下拉列表是基于此命名区域创建的。为了在下拉列表中显示更新后的值，请按以下步骤操作。

1. 点击 "公式" > "名称管理器"。

提示：您可以通过按 "Ctrl" + "F3" 键打开 "名称管理器" 窗口。

2. 在 "名称管理器" 窗口中，您需要按以下步骤配置：

2.1) 在 "名称" 框中选择您要更新的命名区域；

2.2) 在 "引用位置" 部分，点击按钮 选择下拉列表的更新区域；

2.3) 点击 "关闭" 按钮。

3. 然后弹出一个 "Microsoft Excel" 对话框，点击 "是" 按钮以保存更改。

然后基于此命名区域的下拉列表将更新。

删除下拉列表

本节讨论在 Excel 中删除下拉列表。

使用 Excel 内置功能删除下拉列表

Excel 提供了一个内置功能来帮助从工作表中删除下拉列表。请按以下步骤操作。

1.选择包含您要删除的下拉列表的单元格区域。

2. 点击 "数据" > "数据验证" > "数据验证"。

3. 在 "数据验证" 对话框中，点击 "清空" 按钮，然后点击 "确定" 保存更改。

现在下拉列表已从选择的区域中删除。

轻松使用强大工具删除下拉列表

"Kutools for Excel" 提供了一个方便的工具 - "清除数据验证限制"，可以帮助轻松删除一个或多个选择区域中的下拉列表。请按以下步骤操作。

1.选择包含您要删除的下拉列表的单元格区域。

2. 点击 "Kutools" > "限制输入" > "清除数据验证限制"。见截图：

3. 然后弹出一个 "Kutools for Excel" 对话框，询问您是否清除下拉列表，请点击 "确定" 按钮。

然后此选择区域中的下拉列表立即被删除。

为下拉列表添加颜色

在某些情况下，您可能需要制作一个颜色编码的下拉列表，以便一目了然地区分下拉列表单元格中的数据。本节提供了两种方法来帮助您详细解决此问题。

使用条件格式为下拉列表添加颜色

您可以为包含下拉列表的单元格创建条件规则，使其具有颜色编码。请按以下步骤操作。

1.选择包含您要使其颜色编码的下拉列表的单元格。

2. 点击 "开始" > "条件格式" > "管理规则"。

3. 在 "条件格式规则管理器" 对话框中，点击 "新建规则" 按钮。

4. 在 "新建格式规则" 对话框中，请按以下步骤配置。

4.1) 在 "选择规则类型" 框中，选择 "仅格式化包含"选项；

4.2) 在 "仅格式化包含" 部分，从第一个下拉列表中选择 "特定文本"，从第二个下拉列表中选择 "包含"，然后在第三个框中选择来源列表的第一个项目；

提示：这里我在第三个文本框中选择单元格 A16。A16 是我基于其创建下拉列表的来源列表的第一个项目。

4.3) 点击 "格式" 按钮。

4.4) 在 "设置单元格格式" 对话框中，转到 "填充"选项卡，为指定文本选择一个背景色，然后点击 "确定" 按钮。或者您可以根据需要为文本选择某种字体颜色。

4.5) 返回到 "新建格式规则" 对话框时点击 "确定" 按钮。

5. 返回到 "条件格式规则管理器" 对话框时，重复上述步骤3 和4 为其他下拉项目指定颜色。完成颜色指定后，点击 "确定" 保存更改。

从现在开始，当从下拉列表中选择一个项目时，单元格将根据所选文本以指定的背景色突出显示。

轻松使用强大工具为下拉列表添加颜色

在这里我们介绍 "Kutools for Excel" 的 "创建颜色下拉列表" 功能，以帮助您轻松在 Excel 中为下拉列表添加颜色。

1.选择包含您要添加颜色的下拉列表的单元格。

2. 点击 "Kutools" > "下拉列表" > "创建颜色下拉列表"。

3. 在 "创建颜色下拉列表" 对话框中，请按以下步骤操作。

提示：如果您想根据下拉列表选择突出显示行，请在 "应用于" 部分选择 "整行"选项，然后在 "高亮行区域" 框中选择您将突出显示的行。

现在下拉列表已根据以下截图显示为颜色编码。

根据下拉列表选择突出显示单元格

根据下拉列表选择突出显示行

在 Excel 或 Google 表格中创建依赖下拉列表

依赖下拉列表有助于根据第一个下拉列表中选择的值显示选项。如果您需要在 Excel 工作表或 Google 表格中创建依赖（级联）下拉列表，本节中的方法可以帮助您。

在 Excel 工作表中创建依赖下拉列表

以下演示显示了 Excel 工作表中的依赖下拉列表。

请点击 如何在 Excel 中创建依赖级联下拉列表？ 获取分步指南教程。

在 Google 表格中创建依赖下拉列表

如果您想在 Google 表格中创建依赖下拉列表，请参见 如何在 Google 表格中创建依赖下拉列表？

创建可搜索的下拉列表

对于包含长列表项目的下拉列表，在工作表中从列表中选择某个项目并不容易。如果您记得项目的初始字符或几个连续字符，可以在下拉列表中使用搜索功能轻松筛选。本节将演示如何在 Excel 中创建可搜索的下拉列表。

假设您要基于其创建下拉列表的源数据位于 Sheet1 的 A 列，如下图所示。请按以下步骤操作，以使用这些数据在 Excel 中创建可搜索的下拉列表。

1. 首先，在源数据列表旁边创建一个辅助列，并使用数组公式。

在这种情况下，我选择单元格 B2，将以下公式输入其中，然后按 "Ctrl" + "Shift" + "Enter" 键以获得第一个结果。

=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))),"")

选择第一个结果单元格，然后拖动其 "填充柄" 一直到列表末尾。

备注：在此数组公式中，$A$2:$A$50 是您将基于其创建下拉列表的源数据区域。请根据您的数据区域进行更改。

2. 点击 "公式" > "定义名称"。

3. 在 "编辑名称" 对话框中，请按以下步骤配置。

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$50)-COUNTIF(Sheet1!$B$2:$B$50,""),1)

现在您需要基于命名区域创建下拉列表。在这种情况下，我将在 Sheet2 中创建可搜索的下拉列表。

4. 打开 Sheet2，选择下拉列表的单元格区域，然后点击 "数据" > "数据验证" > "数据验证"。

5. 在 "数据验证" 对话框中，请按以下步骤操作。

5.1) 在 "允许" 框中选择 "列表"；

5.2) 点击 "来源" 框，然后按 "F3" 键；

5.3) 在弹出的 "粘贴名称" 对话框中，选择您在步骤3 中创建的命名区域，然后点击 "确定"；

提示：您可以直接在 "来源" 框中输入命名区域为 "=命名区域"。

5.4) 点击 "错误警告"选项卡，取消勾选 "输入无效数据后显示错误警告" 框，最后点击 "确定" 按钮。

6.右键点击工作表标签（Sheet2），从右键菜单中选择 "查看代码"。

7. 在打开的 "Microsoft Visual Basic for Applications" 窗口中，将以下 VBA代码复制到代码编辑器中。

VBA代码：在 Excel 中创建可搜索的下拉列表

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

8. 按 "Alt" + "Q" 键关闭 "Microsoft Visual Basic for Applications" 窗口。

现在可搜索的下拉列表已创建。如果您想选择一个项目，只需在下拉单元格中输入该项目的一个或几个连续字符，点击下拉箭头，然后根据输入内容列出的项目将在下拉列表中显示。见截图：

备注：此方法区分大小写。

创建下拉列表但显示不同值

假设您已创建一个下拉列表，从中选择一个项目时，您希望在单元格中显示其他内容。如以下演示所示，您已基于国家名称列表创建下拉列表，从下拉列表中选择国家名称时，您希望在下拉单元格中显示所选国家名称的缩写。本节提供 VBA 方法来帮助您解决此问题。

1. 在源数据（国家名称列）的右侧，创建一个新列，其中包含您希望在下拉单元格中显示的国家名称缩写。

2.选择国家名称列表和缩写列表，输入一个名称到 "名称" 框中，然后按 "Enter" 键。

3.选择下拉列表的单元格（这里我选择 D2:D8），然后点击 "数据" > "数据验证" > "数据验证"。

4. 在 "数据验证" 对话框中，请按以下步骤配置。

5. 创建下拉列表后，右键点击工作表标签，然后从右键菜单中选择 "查看代码"。

6. 在打开的 "Microsoft Visual Basic for Applications" 窗口中，将以下 VBA代码复制到代码编辑器中。

VBA代码：在下拉列表中显示不同值

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

备注：

7. 按 "Alt" + "Q" 键关闭 "Microsoft Visual Basic for Applications" 窗口。

从现在开始，当从下拉列表中选择某个国家名称时，所选国家名称的相应缩写将显示在单元格中。

创建带复选框的下拉列表

许多 Excel 用户倾向于创建带有多个复选框的下拉列表，以便他们可以通过勾选复选框从列表中选择多个项目。

如以下演示所示，点击包含下拉列表的单元格时，会出现一个列表框。在列表框中，每个项目前都有一个复选框。您可以勾选复选框以在单元格中显示相应的项目。

如果您想在 Excel 中创建带复选框的下拉列表，请参见 如何在 Excel 中创建带有多个复选框的下拉列表？

为下拉列表添加自动完成

如果您有一个包含大量项目的数据验证下拉列表，您需要在列表中上下滚动以找到合适的项目，或者直接在列表框中输入整个单词。如果下拉列表可以在输入第一个字母时自动完成，一切将变得更容易。

要在 Excel 工作表中使下拉列表自动完成，请参见 如何在 Excel 下拉列表中输入时自动完成？

根据下拉列表选择筛选数据

本节将演示如何应用公式创建下拉列表筛选器，以根据下拉列表的选择提取数据。

1. 首先，您需要创建一个包含您将基于其提取数据的特定值的下拉列表。

提示：请按照上述步骤在 Excel 中创建下拉列表。

创建具有唯一项目列表的下拉列表

如果您的区域中有重复项，并且您不想创建包含重复项目的下拉列表，可以按以下步骤创建唯一项目列表。

1) 使用 "Ctrl" + "C" 键复制您将基于其创建下拉列表的单元格，然后将它们粘贴到新区域。

2)选择新区域中的单元格，点击 "数据" > "删除重复项"。

3) 在 "删除重复项" 对话框中，点击 "确定" 按钮。

4) 然后弹出一个 "Microsoft Excel" 对话框，告诉您删除了多少个重复项，点击 "确定"。

现在您得到了唯一项目列表，您可以基于此唯一列表创建下拉列表。

2. 然后您需要创建三个辅助列，如下所示。

2.1) 对于第一个辅助列（这里我选择 D 列作为第一个辅助列），将以下公式输入到第一个单元格（除列标题外），然后按 "Enter" 键。选择结果单元格，然后拖动 "填充柄" 一直到区域底部。

=ROWS($A$2:A2)

2.2) 对于第二个辅助列（E 列），在单元格 E2 中输入以下公式，然后按 "Enter" 键。选择 E2，然后拖动 "填充柄" 到区域底部。

备注：如果在下拉列表中未选择任何值，此处公式的结果将显示为空白。

=IF(A2=$H$2,D2,"")

2.3) 对于第三个辅助列（F 列），在 F2 中输入以下公式，然后按 "Enter" 键。选择 F2，然后拖动 "填充柄" 到区域底部。

备注：如果在下拉列表中未选择任何值，公式的结果将显示为空白。

=IFERROR(SMALL($E$2:$E$17,D2),"")

3. 基于原始数据区域创建一个范围，以使用以下公式输出提取的数据。

3.1)选择第一个输出单元格（这里我选择 J2），将以下公式输入其中，然后按 "Enter" 键。

=IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"")

3.2)选择结果单元格，然后拖动 "填充柄" 向右跨越两个单元格。

3.3) 保持选择范围 J2:l2，拖动填充柄一直到区域底部。

备注：

现在已创建下拉列表筛选器，您可以根据下拉列表选择轻松从原始数据区域提取数据。

从下拉列表中选择多个项目

默认情况下，下拉列表允许用户每次在单元格中选择一个项目。重新选择下拉列表中的项目时，先前选择的项目将被覆盖。但是，如果要求您从下拉列表中选择多个项目并在下拉单元格中显示所有项目，如以下演示所示，您该如何操作？

要在 Excel 中从下拉列表中选择多个项目，请参见 如何在 Excel 中创建具有多个选择或值的下拉列表？本教程提供了两种详细的方法来帮助您解决此问题。

为下拉列表设置默认（预选）值

默认情况下，下拉列表单元格显示为空白，只有在点击单元格时才会出现下拉箭头。如何一目了然地找出工作表中哪些单元格包含下拉列表？

本节将演示如何在 Excel 中为下拉列表设置默认（预选）值。请按以下步骤操作。

在应用以下两种方法之前，您需要创建一个下拉列表并进行一些配置，如下所示。

1.选择下拉列表的单元格，点击 "数据" > "数据验证" > "数据验证"。

提示：如果您已创建下拉列表，请选择包含下拉列表的单元格，然后点击 "数据" > "数据验证" > "数据验证"。

2. 在 "数据验证" 对话框中，请按以下步骤配置。

2.1) 在 "允许" 框中选择 "列表"；

2.2) 在 "来源" 框中选择您将在下拉列表中显示的源数据。

提示：对于您已创建的下拉列表，请跳过这两个步骤。

2.3) 然后转到 "错误警告"选项卡，取消勾选 "输入无效数据后显示错误警告" 框；

2.4) 点击 "确定" 按钮。

创建下拉列表后，请应用以下方法之一为其设置默认值。

使用公式为下拉列表设置默认值

您可以应用以下公式为您创建的下拉列表设置默认值，如上述步骤所示。

1.选择下拉列表单元格，将以下公式输入其中，然后按 "Enter" 键以显示默认值。如果下拉列表单元格是连续的，您可以拖动结果单元格的 "填充柄" 将公式应用于其他单元格。

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

备注：

使用 VBA代码一次为工作表中的所有下拉列表设置默认值

假设您的工作表中有很多位于不同区域的下拉列表，要为所有下拉列表设置默认值，您需要重复应用公式。这很耗时。本节提供了一个有用的 VBA代码，供您一次为工作表中的所有下拉列表设置默认值。

1. 打开包含您要设置默认值的下拉列表的工作表，按 "Alt" + "F11" 键打开 "Microsoft Visual Basic for Applications" 窗口。

2. 在 "Microsoft Visual Basic for Applications" 窗口中，点击 "插入" > "模块"，然后将以下 VBA代码粘贴到代码窗口中。

VBA代码：一次为工作表中的所有下拉列表设置默认值

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

备注：在上述代码中，"- 从列表中选择 -" 是要在下拉列表单元格中显示的默认值。您也可以根据需要更改默认值。

3. 按 "F5" 键，然后弹出一个宏对话框，确保在 "宏名称" 框中选择 "DropDownListToDefault"，然后点击 "运行" 按钮以运行代码。

然后指定的默认值立即填充到下拉列表单元格中。

增加下拉列表字体大小

通常，下拉列表具有固定的字体大小，如果字体大小太小而无法阅读，您可以尝试以下 VBA 方法来放大它。

1. 打开包含您要放大字体大小的下拉列表的工作表，右键点击工作表标签，然后从右键菜单中选择 "查看代码"。

2. 在 "Microsoft Visual Basic for Applications" 窗口中，将以下 VBA代码复制到代码编辑器中。

VBA代码：放大工作表中下拉列表的字体大小

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

备注：在代码中，"xZoom =130" 表示您将当前工作表中所有下拉列表的字体大小放大到130。您可以根据需要更改它。

3. 按 "Alt" + "Q" 键关闭 "Microsoft Visual Basic for Applications" 窗口。

从现在开始，点击下拉单元格时，当前工作表的缩放级别将被放大，点击下拉箭头，您可以看到所有下拉项目的字体大小也被放大。

从下拉列表中选择项目后，您可以点击下拉单元格外的任意单元格以返回到原始缩放级别。